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?

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.

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..

Never mind, it should be

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

Ask Your Question

Weekly Poll

Do you think Google's project to use balloons to provide Internet to people who can't currently get it will succeed?

Discuss in The Lounge

Poll History