Forum Discussion
How to relate a variable cell's value to a particular worksheet?
I have a MS Excel problem which I could not solve, if I have 12 months worksheets (1-12) & another separate working worksheet where I assign a variable cell to allow user to input a character between 1-12, and this number will link to the particular month worksheet to retrieve some data. Any idea how to solve that? Thank you.
=INDIRECT("Sheet"&D1&"!A1")
In D1 enter sheet number and value of Sheet1 cell A1 or Sheet2 cell A1.... is retrieved. In my attached example 17 is in cell A1 of Sheet1.
6 Replies
=INDIRECT("Sheet"&D1&"!A1")
In D1 enter sheet number and value of Sheet1 cell A1 or Sheet2 cell A1.... is retrieved. In my attached example 17 is in cell A1 of Sheet1.
Thanks for the quick respond & very much appreciated. I have attached the example in my attachment jpeg file where in diagram 1, user will input value in month, day & store #, in order to retrieve total qty value from that particular month worksheet (diagram 2). In this case, I know how to apply functions like index & match but how to integrate this INDIRECT function into index & match functions? Please advice. Thank you very much.
=INDEX(INDIRECT("'"&C2&"'!C2:F5"),MATCH(C4,INDIRECT("'"&C2&"'!A2:A5"),0),MATCH(C3,INDIRECT("'"&C2&"'!C1:F1"),0))
Is this what you are looking for?
Hi ikedalim,
You would need INDIRECT function to refer it to sheet, please share sample workbook if need further support
Regards, Faraz Shaikh | Microsoft MVP, MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.
Thanks for the quick respond & very much appreciated. I have attached the example in my attachment jpeg file where in diagram 1, user will input value in month, day & store #, in order to retrieve total qty value from that particular month worksheet (diagram 2). In this case, I know how to apply functions like index & match but how to integrate this INDIRECT function into index & match functions? Please advice. Thank you very much.
