formula for find and replace?

hello,

I am fairly new to excel's many under used formulas and functions. my work book currently has 15 sheets with in it, I have 1 sheet which I use to report errors from the other sheets, this reporting sheet works with formulas such as ='sheet1'!a1 repeated in multiple cells for which i need to return a value in approx 18 cells, what I’m trying to do is run a find and replace to have it change the formulas I currently have based on two separate inputs in adjacent cells. What I would like to do is enter a value in d1 say 2 and it change the formula to ='sheet1'!a2. I would also like to do the same this with the first half of the formula, I enter a value in c1 say 2 and my formula change to ='sheet2'!a2 when both c1 and d1 have an input in them. can this be done? if so what would be the easiest way forward?

PS: i am currently useing the find an replace to change the formulas for me, it works but it is time consuming when i have to change between sheets.


Thank you in advance.

i see this thread has been veiwed almost 60 times, does anyone have any suggestions or any alterinitive i can try?

thank you