SOLVED

dynamic cell address

Occasional Contributor

pc desktop, win11, office, excel

 

using 2 sheets in a workbook.

 

On sheet 2:

consider

=sheet1!D43

this gets the data from cell D43 on sheet1

Is there a way that the 43 can be a variable?

like:  =sheet1!D"A1"    where A1 contains a number.  This example doesn't work,

but is there a correct syntax that would work?

 

thanks for the help

4 Replies
best response confirmed by justa-user (Occasional Contributor)
Solution

@justa-user 

Try this formula:

=INDIRECT("Sheet1!D" & A1)

@justa-user 

First an answer to your question.  The formula you require is

= INDIRECT("Sheet1!D"&A1)

I would, however, never consider using such a formula.  I would consign Excel to the trash can if I were forced to use such a notation, made worse by the presence of a volatile function.  I would apply defined names both to the the column range and the index cell that reflects their business significance rather than their location of the sheet.  The formula would be of the form

= INDEX(ColumnD, recordNum)

For example
= INDEX(balance, period)

That said, I hope INDIRECT works for you.

 

works fine.... thanks for the help
works fine I agree that it is a bit complicated.