Forum Discussion
Help with formula in Exc
The "Type" column from master sheet must be match with the other sheet's name,
Then, use mid and cell function to extract your sheet names, and use filter to return dynamically the data from master sheet, and will update whenever you input more data on the master sheet.
Put this formula to cell A2 of the other sheets. =FILTER(Master!A2:H15,Master!B2:B15=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"No data")
I would only note that the formula CELL doesn't work on Excel online or certain other platforms so if compatibility is needed I would suggest just inserting a row at the top and then in Cell A1 put the sheet name and then adapt the above formula to just look at cell $A$1 for the sheetname
Then on the Master you can automatically generate those sheetnames for your data validation using
=VSTACK('Lead:Service Call Form'!A1)
see attached
Your formula didn't return any data on any of the other sheets
I copied your formula into my spreadsheet and it says That function isn't valid - it is highlighting the word FILTER.
Yes sounds like a problem with the Excel version. I'm guessing you are using something older than Excel 2019.
Other alternatives include pivot tables and power pivot and some much more complicated formulas that I would avoid. Attached I included an example of a pivot table. There are many ways to configure and format the pivot table but I'll leave that up to your discretion.
Also I want to point out that I formatted the original table as a Table (but you could also just turn Filters On) and now you can easily use the drop down triangles to Filter the data based on the Type column and you don't need all the other tabs.
