Like INDIRECT - but not

Samizell

New Member
Joined
Dec 1, 2016
Messages
7
Hi Everyone,

My current formula is INDIRECT("'" & $A8 & "'!b6"); A8 is referencing a worksheet name and B6 is the direct cell in that worksheet. Indirect works beautifully, until I have a ton of them and it's bogging my spreadsheet down. I need to find another way to return the specific cell (B6) from each worksheet within the workbook...without using VBA or an Array.

Any help would be appreciated!
Thanks,
Shelly
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Welcome to the Board!

...without using INDIRECT, VBA, or an Array?
I think you have pretty much exhausted/ruled out all options I can think of in order to do that!

The only thing I can think of is to not use INDIRECT and directly link to each one (which means manually replacing all your formulas with a direct link).
However, if the issue is the number of different sheets it is referencing, I don't know if that will make the performance difference you are hoping for.
How many different spreadsheets is it referencing?
 
Upvote 0
Thank you for responding.

I was requested not to use VBA since no one else here knows how to use or update it. But I might just go that route. Right now there are 26 tabs/worksheets but it's a live document and more are continuously added, so the INDIRECT worked perfect for capturing the new tabs automatically. Manually updating the formulas to capture the added tabs isn't an option because the users only have basic Excel experience. I love how INDIRECT works but any change to the file requires an almost 2 minute calculation. Any other recommendations/suggestions? Would using VBA vs the INDIRECT formula also slow it down?
 
Upvote 0
Also - another thought was to try to use INDEX MATCH MATCH, since I have a named range, but for the life of me I can't figure out how to get the named range to work. My named range is 'sheetlist' and then I need to reference cell b6, then cell c6, etc.
 
Upvote 0
Hi Everyone,

I've come to the conclusion I need to do this in VBA, and would like to request help writing it. I have a named range "sheetlist", which is capturing each worksheet name. Then my end result is to return the contents of cell B6 from each worksheet, into a list along with the worksheet name; and then another list with the contents for cell C6 and the worksheet name, etc for cells D6, E6, and F6.

Thank you in advance,
Shelly
 
Upvote 0
Hi Everyone,

My current formula is INDIRECT("'" & $A8 & "'!b6"); A8 is referencing a worksheet name and B6 is the direct cell in that worksheet. Indirect works beautifully, until I have a ton of them and it's bogging my spreadsheet down. I need to find another way to return the specific cell (B6) from each worksheet within the workbook...without using VBA or an Array.

Any help would be appreciated!
Thanks,
Shelly

Create a range with the relevant sheet names (hope they are not too many) and name this range SheetList. Now you can invoke something like:

=CHOOSE(MATCH(A8,SheetList,0),Sheet1!B6,Sheet2!B6,Sheet3!B6)
 
Upvote 0
Thank you Aladin,

I appreciate the response but I need to stay away from manually selecting the cell from each worksheet. There are quite a few tabs and the end users continue to add new tabs...and won't know to update this formula. That was one reason INDIRECT worked beautifully. INDIRECT just takes too long to calculate. I was hoping for an automated work-around.

Shelly
 
Upvote 0
Thank you Aladin,

I appreciate the response but I need to stay away from manually selecting the cell from each worksheet. There are quite a few tabs and the end users continue to add new tabs...and won't know to update this formula. That was one reason INDIRECT worked beautifully. INDIRECT just takes too long to calculate. I was hoping for an automated work-around.

Shelly

As I said, if you don't have too many sheets CHOOSE would be admissible. (CHOOSE would admit 255 arguments (sheets).)
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top