Copying formulas across worksheets

[energyreport]

I have a large worksheet that has multiple worksheets as tabs. Each of these tabs has information in a table. These tabs are named things like AAD, CTV, BTY, etc.

One worksheet is set up to summarise the contents of these tabs. I do not wish to sum, or do anything mathematically to them (so not suitable to use Consolidate).

I simply want to create a row of formulas in the summary sheet that just points to the relevant cell in the respective tab.

For example, say there are tabs AAA, BBB, CCC , so in my summary worksheet I have a column that goes down in rows AAA, BBB, CCC. I want a formula that when it is on row AAA, looks up a range of cells in tab AAA. When I copy this formula down to row BBB, then I want it to look up the same range of cells, but this time in tab BBB.



Is this possible?



Thanks

[JBeaucaire]

I believe you want to use INDIRECT().

If A1 had the text string "AAA" in it, and in B1 you wanted cell D25 from the sheet listed in A1, then this formula would accomplish that:

=INDIRECT("'" & A1 & "'!D25")

Now you could copy that down the B column and each cell would take the text strings from the adjacent cell in the A column and thus attempt to grab a value from a different sheet...indirectly.

[teylyn]

=INDIRECT("'" & A1 & "'!D25")

slight correction, probably a typo as far as I know JB's work. And these quotes are flipping hard to see.

it should be
=INDIRECT(<double quote><single quote><double quote> & etc...