Forum Discussion
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 being different sales categories and the first sheet is the master sheet ("Prospects Master").
Each sheet will be populated with data, but when a tick is entered in columns J ("First Contact") or K ("Intro /Deck") I want all the data in that row to auto-populate into the master sheet. So the master sheet will be a list of clients/projects that are active. I would also like for the row to be automatically removed from the master sheet if column N ("Non Contract") is ticked on the associated sheet.
Is this possible?
I'm using Excel Office 365 version 16.47.1 on a Mac.
Many thanks!
- 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.
4 Replies
- 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.
