Help with Indirect

[Pretty Vacant]

Morning,

I have a spreadsheet with a number of worksheets all with data about specific projects. I have created a summary sheet which pulls data from all the worksheets into one place. In order to do this I used the Indirect function. As all the worksheets are set up exactly the same, I created 'helper' grid with worksheet names in column A and the column letter in row 4 so Indirect would know where to look. I used the formula: =IFERROR(INDIRECT(""&$A5&""&"!"&B$4&3),0) and this works great.

My question is this; is it possible to use the indirect as above but without having to create the helper grid and then hiding it?

Many Thanks

P

 

[Fishboy]

Morning,

I have a spreadsheet with a number of worksheets all with data about specific projects. I have created a summary sheet which pulls data from all the worksheets into one place. In order to do this I used the Indirect function. As all the worksheets are set up exactly the same, I created 'helper' grid with worksheet names in column A and the column letter in row 4 so Indirect would know where to look. I used the formula: =IFERROR(INDIRECT(""&$A5&""&"!"&B$4&3),0) and this works great.

My question is this; is it possible to use the indirect as above but without having to create the helper grid and then hiding it?

Many Thanks

P

Hi P,

Using your example, lets say for instance A5 contains Sheet2, B4 contains A, your indirect formula is basically saying "show me the contents of Sheet2!A3". Taking that into consideration could you not just replace your indirect formula with =Sheet2!A3 instead?

 

[radian89]

Hi P,

Using your example, lets say for instance A5 contains Sheet2, B4 contains A, your indirect formula is basically saying "show me the contents of Sheet2!A3". Taking that into consideration could you not just replace your indirect formula with =Sheet2!A3 instead?

Hi Fishboy (we meet again) & Pretty Vacant,
I don't think so, i've try recreating the scenario, with sheet 2 contain text, and sheet 3 contain number, and with using P's formula it get the right result, while directly refer to those sheet, it will result showing only from 1 sheet.

but, if each sheet have same template & location, why don't you try something like

=SUM(Sheet2:Sheet4!D2)

it will sum D2, from sheet 2,3,and 4, at the same time.

[/IMG]

thanks a lot
warm regards,

Adrian

 

[Jonmo1]

No I don't see any way to use Indirect without the grid of sheetnames and column letter.

You could possibly get rid of the column of sheetnames IF your sheet names have some sort of numerical pattern.
Like literally Sheet1 Sheet2 Sheet3 etc..