Articles

Excel cell addresses

-
August 25, 2008 at 05:47:49
Specs: XP, -

I am trying to find a way to use a number from a cell to help specify a cell address.

Example: I have a cell with a number in it, let's call this number x. Now I want to get another number from a cell with the address <Sheet2!Ax>, is this possible?

I've tried different methods with INDIRECT, but it didn't help.

Example 2: Say I want the SUM from cell A1 to cell Ax, where x is a number in another cell that can change independently. Can you write =SUM(A1:A"Sheet2C3") or something similar?


See More: Excel cell addresses

Report •


#1
August 25, 2008 at 07:03:07

You were on the right path.

=SUM(INDIRECT("A1:A"&C1))

INDIRECT returns the reference specified by a text string.

"A1:A"&C1 forms a text string such as "A1:A4" which INDIRECT turns into a reference for the SUM function to use.


Report •

#2
August 27, 2008 at 04:07:59

Great, it works perfectly.

Will this work between sheets also, if I want to write =SUM(INDIRECT("Sheet2!A1:Sheet2!A"&C1)) ?

I don't seem to find the right notation..


Report •

#3
August 27, 2008 at 04:23:42

Never mind, it should be
=SUM(Sheet2!A1:INDIRECT("Sheet2!A"&C1))

:)


Report •

Related Solutions


Ask Question