change row number within a formula based on a cell value

[welchww]

Is it possible to adjust a row number within a formula based on a cell value. A simple example (A1+A2) Where the 1 in the formula would change based on a cell referance. Something like this (A(c10)+A2)

 

[DaveTurton]

Data in column A

row control (number) in C10


=INDIRECT(ADDRESS(C10, 1))+A2

 

[welchww]

I need the column to remain as "A" but for the row number to change based on a value in a specific cell. The solution doesn't seem to reference column "A" for the first part of teh equation but maybe I am not reading it right?

 

[DaveTurton]

Address(row,column)


column 1 is column A

 

[welchww]

Great, Now how do I reference a column that is in a different sheet?

 

[SirJB7]

Hi, welchww!

In the last parameter -the 5th one- of ADDRESS you could indicate the worksheet name.

Regards!

 

[welchww]

I tried that but I get a #ref! error. Do I have to put something in parameters 3 and 4. This is what it looks like now. =INDIRECT(ADDRESS(3,2,,,"Data!")) If I don't put quotes around the sheet name it won't let me out of the cell. Thanks

 

[SirJB7]

Hi, welchww!

Remove the exclamation mark from the worksheet name.

Regards!

 

[welchww]

Wow, that's awsome, thanks!!

 

[SirJB7]

Hi, welchww!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!