Forum Discussion
What is the best way to replace a table that is referenced by multiple formulas ?
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.
- Hello,
Thank you for the quick reply. I have a follow up question. For now, I have a few formulas that I'm not using the Pivot Table for. Should I reference the pivot table or use the column title ?
Current formula: COUNTIFS(DATA!AE2:AE513,"<="&TODAY()+30,DATA!D2:513,"MCA")
Will one of these work:
COUNTIFS(DATA!COLUMNTITLE,"<="&TODAY()+30,DATA!COLUMNTITLE,"MCA")
COUNTIFS(PIVOTTABLENAME!PIVOTFIELD,"="&TODAY()+30,PIVOTTABLENAME!PIVOTFIELD,"MCA")
Is the GETPIVOTDATA used here instead?
Thank you for your help.
CIMHO, first one could work if modify as
=COUNTIFS(tbl4pivot[COLUMNNAME],"<="&TODAY()+30,tbl4pivot[ANOTHERCOLUMNNAME],"MCA")
If take data from PivotTable that's cube formulas assuming you added data to data model creating PivotTable.
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?
