Forum Discussion
How to extract 5 cells from 400 worskeets into one master list?
They are all the same invoice on all 400 tabs, but I would like to make a master list with the same 5 cells from each invoice.
I have the current formula in a list ='Basic Invoice'!$I$10 and it brings the info into the master list, but I need the Basic Invoice to update to the next worksheet hoping to use auto fill or something else?
Amanday1234 Without seeing sample, really hard to advise. In general, for Microsoft-365 user, can use TOCOL()/TOROW() functions to consolidate data from multiple sheets to one sheet. You can use TOCOL() like.
=TOCOL(Sheet2!D5,Sheet3!D5,Sheet4!D5,Sheet5!D5)For multiple sheet reference as a time (Array Approach), use following formula. See the attached file.
=TOCOL(Sheet2:Sheet5!D5)Thanks for the info. I was able to finally get it to do what I needed using the define name, index, then indirect formulas.
- If you have access to Excel 365, I would still consider switching to Harun's proposed solution. It is likely to be far more efficient. VSTACK will also achieve a similar result and might be better suited to your data depending upon its layout. I would also use a defined name to reference the 3D range and favour absolute references over relative.
