Some examples - of varying complexity - of the INDIRECT function ...
http://www.openofficetips.com/blog/archives/2005/03/advanced_functi.html
On 10/25/05, Anthony Chilco <[EMAIL PROTECTED]> wrote:
>
> Hi Jim,
> Indirect works in OOo in the same way as it does in Excel. What doesn't
> is the import function if there are sheet references in the strings used
> to build the address. It's an easy fix, though. Find "sheet1!" and
> replace all with "sheet1.". Leave the double quotes out of the search
> strings. Tomorrow I may be able to send you an example of some complex
> indirect addressing.
> tc
>
> Jim Martin wrote:
>
> > Hello All:
> >
> > I am trying to migrate away from ms excel and I was happy that the
> > calc in oo2.0 will open my largest spreadsheet. Unfortunately it gives
> > an error message regarding a formula that works fine in excel and in
> > gnumeric. The formula is something like this:
> >
> > =AVERAGE(INDIRECT("c"&A2):INDIRECT("c"&b2))
> >
> > where cell a2 and b2 contain integer values that represent the top and
> > bottom of a range that I wish to average in column b. I rely on this
> > type of forulation heavily in most of my spreadsheets. Calc gives me
> > Err:508 which indicates a missing bracket. As you can see, there is no
> > bracket error.
> > Further, Calc help gives an example of how to use indirect that is
> > unlike anthing I have seen in other spreadsheets: =SUM(INDIRECT("a1:"
> > & ADDRESS(1;3)))
> > I assume that this syntax would work with average too. Even so, it
> > seems very different to the way I am used to using indirect. I can't
> > see how I could apply this to my common usage of specifying row and
> > colum values to other fucntions via indirect. For instance, I would
> > not know in advacne that I needed to start averaging at cell a1 (as in
> > the example) nor would I know how many rows or columns I would need to
> > average. I relay on match and row functions to give me those values.
> >
> > So, looks like I won't be an OpenOffice user unless one or you can
> > tell me how to make my style of equation work in Calc. Anybody?
> >
> > Thanks in advance,
> >
> > Jim
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
--
My OpenOffice Calc Website
http://www.openofficetips.com
