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

When do you think most people will be driving electric cars?

Discuss in The Lounge

Poll History