Forum Discussion
Solved
Need macro to auto-populate master sheet with data from other tabs
Hi there! I'm here looking for some help with an excel problem. Attached is the workbook I need to edit. All the sheets are identical (with the exception of one hidden sheet) with the sheet names...
- I think this is the formula you need:
=FILTER(Data!A3:Q10000,((Data!L3:L10000<>"")+(Data!M3:M10000<>"")),"")
The + in the filter criteria will move records to the prospects sheet if 'L' OR 'M' is not blank. With FILTER, I like to omit the header row and start with the first row of data. I've set it down to 10,000. You may change that as needed.
It's do-able with formulas, vba, or, power query (w/some clean up of the header rows). The way the workbook is setup it's going to take some leg work to analyze the data. Everyone has their own beliefs on workbook design. My thoughts are: if the workbook is comprised of many sheets with the same arrangement and like data, the data must not be split into many sheets. It's best if consolidated into 1 sheet where you can play to the strengths of Excel to analyze the data (Pivots, filtering, slicers, etc).
Thanks Patrick2788
I've edited the report so that all the data is on one sheet. What formulas should I use that would achieve what I need?
I tried with FILTER but got a value error. I haven't used this formula much so I 'm not sure if I'm doing it correctly.
Cheers
- I think this is the formula you need:
=FILTER(Data!A3:Q10000,((Data!L3:L10000<>"")+(Data!M3:M10000<>"")),"")
The + in the filter criteria will move records to the prospects sheet if 'L' OR 'M' is not blank. With FILTER, I like to omit the header row and start with the first row of data. I've set it down to 10,000. You may change that as needed.- Thanks, I've got it working now.