Hi,
My first tab shows my annual budget plan, for about 130 budget lines (rows). One annual total per budget line.
In my second tab, I enter invoice data (all payables) throughout the year, i.e. I'm constantly adding new rows and entering newly-received invoices. As I do this, one of the cells I fill in is a code that is the same name as one of the 130 budget lines back in Tab 1.
Back in Tab 1, I have columns to show the total dollars for each month of the fiscal year.
I want the cells in these monthly columns in Tab 1 to automatically update as I enter data into the invoice-data tab (Tab 2). They would recognize the budget line code and pull to that budget line, AND they would recognize the invoice date that I've entered and pull to the correct MONTH column.
Now, I've tried to do this by creating a pivot table of the invoice data on Tab 2, with the budget line names as the rows, and the invoice dates as the columns, and the Sum of (invoice) Amounts as the values. So far so good, in that I can see the data I want pulled into Tab 1 (how much has been spent per month per budget line). Then I did vlookups from Tab 1 to the pivot table, and this seemed to populate Tab 1 correctly.
HOWEVER, I can't maintain control of the pivot table as I enter new data into Tab 2. Every time I refresh, it re-arranges the years/quarters/months so that my vlookups on Tab 1 have incorrect data pulls, and I have to either fix the pivot structure, or change the col_index_num part of my vlookup formulas.
Can I do this operation without having to use the pivot table step?
OR, can I somehow lock down the pivot table structure so that it never changes, even as I enter new data that populates it?
OR, can this be done in a way entirely different than what I've attempted?
Thank you!
My first tab shows my annual budget plan, for about 130 budget lines (rows). One annual total per budget line.
In my second tab, I enter invoice data (all payables) throughout the year, i.e. I'm constantly adding new rows and entering newly-received invoices. As I do this, one of the cells I fill in is a code that is the same name as one of the 130 budget lines back in Tab 1.
Back in Tab 1, I have columns to show the total dollars for each month of the fiscal year.
I want the cells in these monthly columns in Tab 1 to automatically update as I enter data into the invoice-data tab (Tab 2). They would recognize the budget line code and pull to that budget line, AND they would recognize the invoice date that I've entered and pull to the correct MONTH column.
Now, I've tried to do this by creating a pivot table of the invoice data on Tab 2, with the budget line names as the rows, and the invoice dates as the columns, and the Sum of (invoice) Amounts as the values. So far so good, in that I can see the data I want pulled into Tab 1 (how much has been spent per month per budget line). Then I did vlookups from Tab 1 to the pivot table, and this seemed to populate Tab 1 correctly.
HOWEVER, I can't maintain control of the pivot table as I enter new data into Tab 2. Every time I refresh, it re-arranges the years/quarters/months so that my vlookups on Tab 1 have incorrect data pulls, and I have to either fix the pivot structure, or change the col_index_num part of my vlookup formulas.
Can I do this operation without having to use the pivot table step?
OR, can I somehow lock down the pivot table structure so that it never changes, even as I enter new data that populates it?
OR, can this be done in a way entirely different than what I've attempted?
Thank you!
