What is the best way to replace a table that is referenced by multiple formulas ?

[aimster1525]

Hello,

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 ?

Thank you

[#2]

@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.

[#3]

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.



C

[#4]

@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?

[#5]

@aimster1525 

IMHO, 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.

[#6]

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?

thank you 

cheers