Forum Discussion
Auto-populate worksheets from a source worksheet based on data input
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
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.
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
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