Auto-populate worksheets from a source worksheet based on data input

[MattChisholm]

Hi MS Tech Community,

I have a dilemma that I have been trying to search a solution to for quite a while. I manage a massive inventory of cannabis excise tax stamps. I am constantly issuing thousands of stamps for multiple work orders across multiple provinces everyday. My tool for tracking all these transactions is Excel.

My question is in regards to auto populating multiple tabs across a workbook based on data inputted into a source sheet. I know how to use the formula based option however that is more work than I really want to spend time doing. Can I assign specific columns to recognize the data and correlate that information to the correct tab and column in the dependent work sheets?

Example.

Date	Province	Line	W/O Number	Issued	Used	Damaged	Unaccountable	Returned
1-Jul	British Columbia	Pre-Rolls	1502641	902	888	14	0	0
1-Jul	Nova Scotia	Pre-Rolls	1502642	297	288	9	0	0
1-Jul	Alberta	Pre-Rolls	1502630	5,419	1,680	6	-6	3,727
1-Jul	Newfoundland	Pre-Rolls	1502638	2325	1200	17	0	1108
1-Jul	Manitoba	Pre-Rolls	1502639	3,531	1,200	3	+1	2,329
1-Jul	Quebec	Pre-Rolls	1502640	1,678	1,200	8	-2	468

I would like to be able to have column B (Province Column) recognize 1 of 10 tabs. If I type in Newfoundland it will recognize to populate the information into the Tab that's titled NFLD. Then have the information from column D (W/O #) auto-populate the W/O cell in the dependent NFLD tab. The same of Column E (issued) and I (returned). Essentially the top row of the dependent province work sheet will have the complete inventory amount for that province and as I populate the source sheet, I would like the dependent province sheets to auto-populate and keep track of the inventory automatically.

Is this something that is possible? Any tips or tricks would be greatly appreciated.

I have attached a test copy of the work book.

Much Thanks

Matt

[#2]

@MattChisholm ,

You have many options. I recommend PowerQuery (PQ). Setup is a bit tedious but straight forward. One setup and we add new transactions to Rec Excise Tracking we can click the Data > Refresh All and every tab will update nicely. 

NOTES

1. PQ will convert Rec Excise Tracking to a table so I recommend making it a table and naming it tblSource before PQ does so the name means something. 
   
   
2. Use PQ to create each result query. You will need 1 for each tab. Just duplicate the one I created, rename it, then double click the Filtered Rows step and choose which provinces to include for each query.
3. Load the query to the appropriate tab then insert a few columns:
   1. Row - enter formula =ROW()-ROW([#Headers])
   2. Start - enter formula =IF([@Row]=1,B1,INDEX([End],[@Row]-1))
   3. End - enter formaul=[@Start]+[@[Add/Sub]]

[#3]

@Craig Hatmaker 

Thank you very much for the response. I was ok at Excel 2007 but have lost  a lot of the info I retained in the last dozen years or so. Am I launching PQ correctly by clicking the Data > From Table /Range button?

I'm not familiar with Power Query.

Maybe I'm not understanding correctly but once I have this totally set up will I be able to save it as a template and then use a new workbook every month?

Thanks for the info.

Matt

[#4]

@MattChisholm 

Yes to all questions. But to clarify, all you have to do is update the one worksheet with the transactions then press refresh all to update all other worksheets.

[#5]

@Craig Hatmaker 

Hello Again Craig,

So I'm still struggling to get this workbook set up exactly as I want it. I thought I had everything figured out and I've created my template, but when I load in Septembers numbers and then hit refresh it automatically deletes the formulas for the ROW, START and END column and does not auto tabulate any of that information and leaves those columns entirely blank.

I also noticed that the custom column you created (Add/Sub) had the wrong formula assigned to it. It should actually just be "[Issued]-[Returned]" instead of including the issued, used and damaged as well. The formula could also be  [Used]+[Damaged]+[Unaccountable] but [Issued]-[Returned] is what I have it set as currently.

The END column formula should also be =[@Start]-[@[Add/Sub]] to get the accurate amount removed from inventory.

But regardless of all that like I said before whenever I add the info, in this case all of Septembers numbers and then hit refresh any of the formulas that were present get deleted and then the columns appear blank.

  

Am I suppose to input the formulas every time? That would defeat the purpose of trying to automate the withdrawn amounts as I track it per work order.

Thanks for the help thus far...I've added both the blank template that I was hoping to be able to use at the beginning of every month and the populated version with Septembers numbers in it. I did not hit Refresh on it so you can see what it is that I am referring.

Matt Chisholm

[#6]

@MattChisholm 

I looked at your new workbook and then I put your new data into the workbook I gave you and updated it. This is what I got. As you can see, the formulas work. So what went wrong?

In your new workbook you added formulas to tblSource. Do not change tblSource. We want the new formulas in the dependent worksheets only. When they are in tblSource and we use PowerQuery to read it, PQ changes all formulas to values. We need them to stay formulas. When we read tblSource with PQ and then place the filtered results in dependent worksheets, Excel merges the query fields with the table which includes formulas and all works well. 

Hope that helps