Using concatenate to acquire value from a cell

[deweyirl]

Hi all,

I have a master overview sheet which shows the important inputs that went into a model. A number of simulations were done with some input values changing from simulation to simulation.

Each simulation has a worksheet which outlines the overall results and the inputs that are within the simulation.

What I would like to know, is that are there are a number of inputs that are going to be changed over a number of simulations. In the attached example, there are details for run3 inputted in the run3 worksheet but not Overview sheet.

I could copy the column c, input it into column d, use the find and replace function and replace run2 with run3 within the column.

Cell C2 is returning the Energy use in Run 2 and has an equation of ='Run2'!$A$3. Is there a method of changing the number after the run to the number in row 1, so the formula automatically updated if the run number on the top row changes.

I tried to use the concatenate function but I am getting an error and it doesn't work.

I hope I have explained myself well.

Regards,
Stephen

[6StringJazzer]

If you build a cell reference from a string, you have to use the INDIRECT function. This formula can be use across the row, and if the sheet does not exist yet it will show a #REF error.

Please Login or Register  to view this content.

[edioquino]

Try this place it in starting D2 in Overview. Hope it helps

=INDIRECT("'Run"&D$1&"'!$A$3")
=INDIRECT("'Run"&D$1&"'!$B$3")

=INDIRECT("'Run"&D$1&"'!$B$8")
=INDIRECT("'Run"&D$1&"'!$B$9")
=INDIRECT("'Run"&D$1&"'!$B$10")
=INDIRECT("'Run"&D$1&"'!$B$11")
=INDIRECT("'Run"&D$1&"'!$B$12")
=INDIRECT("'Run"&D$1&"'!$B$13")
=INDIRECT("'Run"&D$1&"'!$B$18")
=INDIRECT("'Run"&D$1&"'!$B$19")

[Richard Buttrey]

If you add an initial column A on the Overview sheet and enter the row numbers in A5:A12, i.e. 8,9,10....18,19) then the indirect in C5 copied down is

Please Login or Register  to view this content.