indirect function?
 

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

Perhaps you mean

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

--

HTH

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


"Russ" wrote in message
...
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

On Thu, 28 Jul 2005 16:06:46 +0100, "Bob Phillips"
wrote:

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

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.

--
Regards
Roger Govier
"Russ" wrote in message
...

On Thu, 28 Jul 2005 16:06:46 +0100, "Bob Phillips"
wrote:

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

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.

On Fri, 29 Jul 2005 10:49:07 +0100, "Roger Govier"
wrote:

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,

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" wrote in message
...
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.

On Fri, 29 Jul 2005 10:49:07 +0100, "Roger Govier"
wrote:

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.

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

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

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