Hello, would you please help me. Is there a way to use sumif formula to roll up individual worksheets to the summary. Please see summary tab cell G8. This formula SUM('063-4001:063-4003'!G8 would not work if the end users insert more rows or make changes to the individual tabs.
Thanks
Lannie
Hi Lannie,
Try a VLOOKUP on multiple sheets, if the structure may vary.
Hi Catalin,
This is a sample file. On the actual file, i have about 10 units that need to be roll up to the summary tab. So i'm not sure how to do it. Would you please provide a sample formula on the attached file.
Hi Lannie,
As described in Vlookup Multiple sheets link sent in the previous message, it is possible to create a formula to lookup a value in multiple sheets.
It's just not the right way to do it, use power query to transform your data in the correct format and display the data into a proper report format using pivot tables, see attached file. Just change the file path in Settings sheet, refresh Data sheet then refresh Pivot table Report.
(to refresh, right click the table or pivot and click Refresh)
Hi Catalin,
Thanks so much for the sample report! However, I encounter error when trying to refresh. I changed the file path in Setting sheet, and it still doesn't let me refresh.
Look like the error is in the "ProcessSheet fx" and also in the query Data / Added (custom step). Would you please give me advise how to fix the ProcessSheet fx. Please see attached.
Thanks.
Lannie
Hello Catalin,
I've tried and able to refresh the headers query, except the "data" query. I noted it gets stuck in "Added Custom" step (see attached). I don't understand this step. Would you please guide me to fix this step. Thanks again!
Hi Lannie,
In Power Query Editor, what settings you have for Data Source Settings? (go to Home Tab>Data Source Settings)
There should be 2 objects listed: ThisWorkbook and the source workbook. Edit Permissions for both and set Privacy level to "None".
