We are using a programme that quantifies materials from a drawing. Once the quantification is complete we are able to export the quantities into an excel document. The template for the excel document is embedded into the programme we are exporting from and we are unable to access it for any modifications. The template is set up with raw data and also a pre-designed pivot table. The pivot table tab name is the same for every time you export the document. The column headings and row names are also the same every time you export the document. The file is then named in accordance with the house design. That is the thing that changes every time as well as the quantities within the pivot table. However the document requires more calculations after the pivot table is created. I have created a new tab in the excel document and labeled it cover sheet. I have set it up to GETPIVOTDATA from a set column labeled Primary Count. I have saved the tab 'coversheet' into it's own file as I wish to copy this cover sheet with all the formulas and 'GETPIVOTDATA' into the workbooks we are creating. The issue I am facing is the formula for the GETPIVOTDATA is retaining the original file name. an example of the formula in the cover sheet original document reads like this: =GETPIVOTDATA("Count",'Resources Pivot Table'!$A$1,"Resource","cc 0999_item: 6230B : Roof Tiles - Supply")
When I copy the coversheet into a new workbook that also has the pivot table the formula shows the original filename. It reads like this: =GETPIVOTDATA("PrimaryQuantity",'[Riley 49_Liam model takeoff v3_Cover sheet source file.xlsx]Resources Pivot Table'!$A$1,"Resource","cc 0999_item: 6230B : Roof Tiles - Supply")
If I remove the original filename from the formula I get an error message in the formula. I need to have the coversheet with the filename exposed and add a formula within it to state the formula to read the current workbook. I have tried many things and can't seem to get it to work. Is there a formula I can add into the GETPIVOTDATA formula that will do what I am after.