I am working on a spread sheet for a system assessment. I have many challenges in this project including having to deal with ever changing sheets being present within the document. Each system is a tab that tracks system settings and performance settings and 1 tab is used for a high level overview. I am working to pull averaged results across all ‘systems’ tabs to the ‘overview tab as well as low, high, minimum, maximum, etc . . .
In this case I have a cell(‘Overview’!J26) on the ‘Overview’ tab that searches all the ‘System’ tabs to populate the lowest failure rate present across all systems (‘Sensor 1’!J87, ‘Sensor 2’!J87, ‘Sensor 3’!J87, etc . . .). I also do this for the highest Failure %. What I would like to do is to then use the Lowest Failure % that is populated in the ‘Overview’ tab to then search the ‘System’ tabs for the sheet where that value is found to then return the value in the system name cell (‘Sensor 1’!D9, ‘Sensor 2’!D9, etc . . .) and be populate in the Overview tab directly under the Low Failure % (‘Overview’!J27).
This seems very simple in my head but the function of this is proving to be complex. I am open to VBA solutions as well at formula solutions that I can update via vba to accommodate the ever changing sheet quantities.
If could only do something like:
=INDEX(‘Sensor 1:Sensor 5’!D9,MATCH(‘Overview’!J26,’Sensor 1:Sensor 5′!J87,0))
where if the match sheet value J87 could select the proper D9 sheet value to then populate in the ‘Overview’ sheet
VBA .Find and explore vlookup, index, and match formulas
Jared Rogers is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Bad data architecture can lead to problems downstream. Separating data across multiple sheets is an example of less than ideal data architecture. In cases like that, you won’t be able to change the data architecture and will have to resort to workarounds, which may can make the whole thing even more complicated. So, here is a workaround that can deal with the drawbacks of spreading data across different sheets.
Conceptually: You could use a helper sheet. Insert a new sheet.
On the helper sheet, type the names of all the Sensor sheets into a column.
In the next column, use a reference to that sheet’s cell J87 with the failure rate. In the next column, reference that sheet’s cell D9. Add as many specific data points from each sheet as you need, each in their separate column.
Now you have a nice little table of values and you can see where each value originates.
On your overview sheet, you can now use an Index/Match function or an Xlookup across this helper table as the input to return the sheet name and/or the D9 value of the sheet with the matched value.
Once you have set up the helper sheet, you can hide it, since it is not required to be visible in the user interface.
You could also apply VBA that runs when the workbook is opened. The VBA routine could loop through all sheets, identify if a sheet is a system sheet somehow (maybe any sheet that starts with a specific word). For each system sheet, the VBA could then write a reference to that sheet’s J87, D9, etc cells into a table on the hidden helper sheet.
The resulting list of sheets on the helper sheet can then grow or shrink automatically as system sheets are added/removed in the workbook. Your Index/Match lookup formulas on the overview sheet would not be affected by how many rows are in the helper sheet.
2