Alternative to INDIRECT

[MarkfromDorsetUK]

## my first post! ##

I need an alternative solution to using the INDIRECT function to pick up a specific sheet name from a tab and use it within a formula. Additionally, I want to use a named range containing the sheet names in a formula, currently SUMPRODUCTS, to sum across the selected sheet names. I have the selected sheet names in a named range currently.

Ideally I'm looking for a formula based solution as my XLS has 500 lines and 300 columns and each cell currently uses INDIRECT and SUMPRODUCTS.

The problem is fully described in sample XLS I've created - which Mr Excel doesn't seem to allow me to attach - understandably! I've uploaded a screen short showing current formula in D2 using INDIRECT and picking the sheet name up from B11 to look at the value in V8 on the sheet named in B11.

 

[KRice]

Welcome to the MrExcel Board! You may be able to use an alternative approach, but what issue are you trying to address? Is the worksheet sluggish because so many volatile functions are being used? How many other worksheets need to be accessed? Are the ranges to access on those other worksheets always single cells (as shown in your screenshot). I’m not sure how to advise at the moment without more detail. You might be able to use INDEX or CHOOSE functions to effectively replace INDIRECT, or perhaps consider a UDF, but more information is needed. If you follow the link in my signature block to the XL2BB add-in, you can download, install, and then use it to post small working examples of worksheets. Alternatively, especially if XL2BB doesn’t quite convey everything you are attempting to do, you can upload the workbook to a file-sharing site (e.g. Dropbox, Box, Google Drive, etc). and share the link so that others can access the file.

 

[MarkfromDorsetUK]

Thanks KRice. The issue is a very slow running XLS due to the volatile INDIRECT Formulae. XLS has around 45 sheets. INDIRECT is used to summarise financial results pulling data from around 38 sheets representing 38 company's. Each of two sheets where the data is summarised has 500 lines and 300 columns representing months over 5 years (COL) and profit and loss, cash flow and balance sheet lines (ROWS). A Sample XLS I've created is saved in a dropbox folder here. Please make a copy of you modify it and save into the same directory. Thanks in advance for any help!
Alternative to INDIRECT