I am trying to read data into a front sheet from slave sheets depending on the User's selection. I want to avoid the use of =INDIRECT which, as I understand it, is volatile and therefore processor-hungry. I would also like to avoid using VBA if possible
I have attached an example. Any help is much appreciated.
Maybe in B5 coiped across and down
Formula:
Please Login or Register to view this content.
But if this is a trivial example and your production workbook has dozens of sheets then that would be an inelegant solution.
INDIRECT is processor hungry but it depends how much data you have. If this example data set is truly representative then I can't see it being a problem.
A neater solution would be to change your data if its spread out across many sheets. Data is always best kept on a single sheet.
In his case I'd have a new Data layout with columns for
Current Tab Name - values would be A,B,C or their equivalent
Whatever the current A:G values represent - values A,B,C,D...etc
Whatever the current B4:I4 values represnet - values 1,2,3...8
Value - i.e. the values currently at the intersections of the above
Last edited by Richard Buttrey; 02-27-2020 at 01:38 PM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
BRISBANEBOB, only indirect allow to use dynamic sheet name. Your example is not so good or the solution is simple
OR
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by BMV; 02-27-2020 at 01:40 PM.
Are you only likely to have those 3 subsidiary sheets, or might there be more?
You could do it with a multiple IF formula, i.e. IF($B$2="A", get the data from sheet A, IF($B$2="B", get the data from sheet B, get the data from sheet C))
As the tables all follow the same format, you could simplify your INDEX formula by using ROWS() and COLUMNS() functions instead of MATCH.
Hope this helps.
Pete
There will only be 4 or 5 sheets but some will have 500 rows.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks