Help with Indirect

Pretty Vacant

New Member
Joined
May 18, 2015
Messages
31
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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

Board Regular
Joined
Nov 12, 2015
Messages
111
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.

2yzitc6.jpg
[/IMG]

thanks a lot
warm regards,

Adrian
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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..
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top