Automated filling of subsequent sheets based on condition
- Thread starter Pasadu
- Start date
p45cal
Well-Known Member
The attached has tables on each sheet. All except the MASTER SHEET update their table when that sheet is activated, which is done by a 2-line macro.
This macro isn't esssential because each table can be updated by right-clicking on it and choosing Refresh.
Make sure that when you update the MASTER SHEET that the table fully encompasses all your data (this should happen automatically).
This macro isn't esssential because each table can be updated by right-clicking on it and choosing Refresh.
Make sure that when you update the MASTER SHEET that the table fully encompasses all your data (this should happen automatically).
Thank You sir, I really appreciate it. But please, I want the data to have unique values. Meaning Each row should be distinct. If the order number is AMD100, it should be recorded once on the row, it should not be repeated like 2 times or 3 times.
The repeatition is done on the master data, but the other sheets should have just unique extractions, the order numbers should appear ONCE, not repeated overthere. Thank You once again.
The repeatition is done on the master data, but the other sheets should have just unique extractions, the order numbers should appear ONCE, not repeated overthere. Thank You once again.
Thank you, it worked perfectly as I desired. Nonetheless, can you guide me in SUMMARY as to how you did it, so I can do same when i come across other workbooks?
Irrespective, thank you for the work, it is Perfect.
p45cal
Well-Known Member
I've been silly with that last offering; I removed duplicates right at the end of each refresh using a macro where I should've/could've done it as the last step in the query.
So the same caveats as in my msg#2 above. The macro's still in there but not absolutely required.
How it works:
Power Query. There are 8 queries; 1 'landing' query which grabs the raw data from the Master Sheet, then 7 others (each of which uses the 'landing' query as a source), one for each results sheet which only (a) filter, (b) return a subset of columns and (c) remove duplicates.
Like pivot tables, if the source data changes they need refreshing - the macro does that as each results sheet is activated (moved to from another sheet).
To explore what's happening in Power Query, right-click any of the results tables and choose Table, then choose Edit query…
On the right of the new window you'll see Applied Steps which you can click on in sequence to see what's happening. On the left, you can expand a Queries pane to explore the 8 queries.
Table1 is the landing query, the others are the individual results queries/tables whose names should tell you what they're about.
So the same caveats as in my msg#2 above. The macro's still in there but not absolutely required.
How it works:
Power Query. There are 8 queries; 1 'landing' query which grabs the raw data from the Master Sheet, then 7 others (each of which uses the 'landing' query as a source), one for each results sheet which only (a) filter, (b) return a subset of columns and (c) remove duplicates.
Like pivot tables, if the source data changes they need refreshing - the macro does that as each results sheet is activated (moved to from another sheet).
To explore what's happening in Power Query, right-click any of the results tables and choose Table, then choose Edit query…
On the right of the new window you'll see Applied Steps which you can click on in sequence to see what's happening. On the left, you can expand a Queries pane to explore the 8 queries.
Table1 is the landing query, the others are the individual results queries/tables whose names should tell you what they're about.
Thank you sir for your time and effort. I will have a look at the table later on a PC. Please how can I contribute to this page if I want to support it with money?
Hi Pasadu,
If there's a specific forum member you'd wish to donate to, several have links in their signature, or the Excel Ninjas (volunteer forum mods) have links here: https://chandoo.org/forum/threads/donate-support-our-ninjas.18150/
If you wish to support Chandoo directly, his contact info is here: https://chandoo.org/wp/contact/
Hope this helps.
If there's a specific forum member you'd wish to donate to, several have links in their signature, or the Excel Ninjas (volunteer forum mods) have links here: https://chandoo.org/forum/threads/donate-support-our-ninjas.18150/
If you wish to support Chandoo directly, his contact info is here: https://chandoo.org/wp/contact/
Hope this helps.
Acknowledged Sir, I understand it and will show my appreciation
Sir, could you please have a look at this data for me where there should be automatic feeling of other sheets based on conditions on the Master Sheet? I have attached the file please. Thank You.