0

In my database, I have created separate worksheets for different sources of data, to make the database more structured for the users. In the summary page I created, I would like to dynamically refer to these worksheets, to easily visualize the data.

The "sheet selection" looks as follows.

enter image description here

Site should determine from which sheet the data comes. Afdeling and Data are columns in these sheets, and its values are used to find the data range (row start and row stop).

In this excellent question and answer, it is explained how to dynamically refer to a specific data range by Function, without the use of Indirect() (Why I don't want indirect). However, it does not answer how to refer to a sheet.

Is it possible to select the worksheet in an excel file by function, without using Indirect()?

Robin Kramer-ten Have
  • 818
  • 2
  • 13
  • 34
  • Questions that only point to indirect: https://superuser.com/questions/79766/excel-vlookup-how-to-specify-dynamic-worksheet-name , https://stackoverflow.com/questions/14850828/dynamic-sheet-name-in-query-in-google-spreashsheet , https://stackoverflow.com/questions/7128546/reference-excel-worksheets-dynamically – Robin Kramer-ten Have Aug 01 '17 at 09:26
  • It's not completely clear if you want only Excel Formula or VBA, but the other way would be to include all ranges in the formula `IF(condition, Shee1!A1, Sheet2!B2)` – Slai Aug 01 '17 at 09:38
  • I am aiming for a Formula interpretation, but if VBA is the only viable option it is also welcome. Thank you for the suggestion of the If-statements. There will be around 14.000 cells in which I need to specify the sheet, and there will be five sheets in total. Won't that amount of if-statements cause some speed issues? – Robin Kramer-ten Have Aug 01 '17 at 09:50
  • Non-volatile Excel Formulas should be fastest as they are recalculated only when anything changes in the source ranges. VBA in the [Worksheet Change Event](http://www.excel-easy.com/vba/events.html#worksheet-change-event) might be a bit faster than INDIRECT depending on how it's implemented. You should probably test how slow INDIRECT is before optimizing. – Slai Aug 01 '17 at 20:56

0 Answers0