Jun 15 2021 05:39 PM
Jun 15 2021 05:39 PM
I have a dashboard with multiple formulas and a couple of pivot tables that reference a table on a worksheet in the same workbook. I need to replace the entire table weekly because the data changes. What is the best way to do it without breaking my formulas ?
Jun 15 2021 10:31 PM
@aimster1525 With "table" I assume you mean a structured Excel table. One that you can give a name and use structured reference with. Something like =SUM(tbl4pivot[Value]).
Make sure that the pivot table source refers to the table name (e.g. tbl4pivot), not a range like Sheet1!A1:P300.
Now, when you do your weekly update, create (anywhere in the workbook) a new structured table for it in the same way as the previous week's data. Delete the previous week's table and rename the new one to tbl4pivot.
Now refresh your pivot table and it should pick-up the data from the newly created table. Of course, save your workbook before updating, so that you can always go back in case the end result is not what you would expect it to be.
Jun 16 2021 09:04 AM
Jun 16 2021 09:15 AM
@aimster1525 Can't really tell without seeing the file. I'm not particularly good at imagining how a formula would work, just from seeing formulae like the ones you describe. Sorry!
Can you upload it, without disclosing any confidential information?
Jun 16 2021 09:54 AM
IMHO, first one could work if modify as
If take data from PivotTable that's cube formulas assuming you added data to data model creating PivotTable.
Jun 20 2021 03:23 PM
Hi @aimster1525 ,
The best way is to re-build your solution where it does not involve using formulas.
Question: what does your formula do? Not asking what the function does in terms of excel functions but your formulas are getting information from the dataset and where is that information going to? show us that elaborated on your formula is getting info from the dataset and being used where? In that context. So we can figure out how we can create a solution without using formulas. So we need to know the lay of your spreadsheet land since we already know that the information is coming from the dataset, where is the destination? your formula is streaming information from the dataset to where? a table? a calculation? to feed what. a form?