# Help with Indirect

#### Pretty Vacant

##### New Member
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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?

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,

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..

Replies
1
Views
301
Replies
3
Views
168
Replies
3
Views
682
Replies
3
Views
348
Replies
3
Views
924

### Forum statistics

1,221,053
Messages
6,157,640
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back