indirect function?

[Russ]

I'm using Office 2000. I would like to use a cell in my spreadsheet
to indicate a row number for other cell's formulas. That is, I would
enter a row number (not a full cell address) in a certain cell, and
other cells would access that cell and insert that row number in their
own formula. I've thought about combining the indirect function, and
the address function, but I don't know how to accomplish this. For
example, the cells that need the info would have a formula something
like this:

=sum(indirect(A1):indirect(A2))

Problem is, I want each column's formula to indicate it's own column,
not the column in cell A1 or A2.

Any way to do this?

Russ

 

[Bob Phillips]

Perhaps you mean

=SUM(INDIRECT("A"&B1&":A"&B2))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Russ]

Perhaps you mean

=SUM(INDIRECT("A"&B1&":A"&B2))

I tried your solution but Excel didn't like it. I re-arranged the
quotes and ampersands but could not get it to go. Got any explanation
for this one?

Russ

 

[Roger Govier]

Bob's solution works fine.
What are you entering in B1 and B2? What error are you getting?
With 13 in B1 and 15 in B2 and the values 10,20,30 in cells A13, A14 and A15
I get the correct result of 60.

 

[Russ]

After following your explanation I see that Bob's solution does work
fine. I was and still am unsure of the reasons for the quotes and
ampersands and why they go where they do, but I plugged in my columns
and rows and got it going just fine. All it needs is some way to
indicate a relative address for each column so I can copy the formula
across columns easily. Thanks for the help.

 

[Bob Phillips]

Russ,

INDIRECT is looking for a string (that it can INDIRECT to). So the formula
builds up the string bit by bit

"A" - to signify the start column, A
&B1 - concatenate with that A, the row number in cell B1
":A" - : for a range separator, A for then end column, A again
&B1 - concatenate with that :A, the row number in cell B2

giving us a range string, such as A2:A23. which INDIRECT works upon

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Russ]

On Fri, 29 Jul 2005 23:08:12 +0100, "Bob Phillips"

Thanks Bob, I never would've figured this out. I didn't find anything
like this in Excel's help files, but maybe it's buried deeper
somewhere.

Russ