How to extract 5 cells from 400 worskeets into one master list?

New Contributor

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?

 

 

3 Replies

@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)

 

 

Harun24HR_0-1670131898218.png

 

@Harun24HR 

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.