Change Formula Worksheet Reference Based On Cell

[Mike Langlois]

Hi all,

Is it possible to amend a formula based on a certain cells value?

As an example I have a formula:

Cell value: Sheet1

=sum(Sheet1!A:A)

If I change the value in a particular cell, I would like to change the Sheet1! to whatever is put into that particular cell, thus changing the sheet the formula references? So it becomes:

Cell value: Sheet2

=sum(Sheet2!A:A)

Hoping it is possible?

 

[NARAYANK991]

Hi ,

Try this :

=SUM(INDIRECT("'" & B1 & "'" & "!" & "$A:$A"))

B1 will contain Sheet1 or any other worksheet tab name.

The above formula should not be in column A of any worksheet.

Narayan

 

[Mike Langlois]

Hi Narayan,

Thanks for your reply. That does work for Sum formula, however I also have an occasion using Index Match that would need to amend. I have attached an example that hopefully will explain what I am trying to achieve.

When the value in H1 is changed, I want the value of that cell to become the bolded section of the formula:

=INDEX(Jan!A:A,MATCH(Front!A4,Jan!D:D,0))

If that is possible?

 

[deciog]

Mike Langlois

Look if it helps, put Validation List

Decio

 

[deciog]

From Brazil

 

[Chihiro]

Same principle applies.
=INDEX(INDIRECT("'"&H1&"'"&"!"&"$A:$A"),MATCH(A4,INDIRECT("'"&H1&"'"&"!"&"$D:$D"),0))

 

[Mike Langlois]

Perfect thank you very much, both worked exactly as needed