Hello,
I have a Pivot table tab called "Pivot" in which Col A and B are pivot table values coming from tab "Raw Data 1". Column C and D (in red) are not part of the pivot table and are manually added columns which are pulling the data from the tab "Raw Data 2" after vlookup with the pivot table columns (A and B). I have a chart which is built off of Col C and D (in red).
I have 2 questions:
1) How will my vlookup formulas in Col C and D drag down dynamically to match the pivot table values in Col A and B when there are more data added to raw data? Do i need to drag it all the way down to an arbitrary row which far enough or is there a formula which will keep adding the vlookups to match the last row of the pivot table?
2) How can i select a range for this graph which will automatically detect the first and last row for Col C and D as more data gets added to the raw data periodically. How will the chart range automatically adjust itself to include the new additions or subtractions in the raw data. I want my graph to always have a range starting from the first row to the last row of col C and D which will be until the pivot table range in Col A and B. This way i don't need to set a fixed range for the graph which might include 0's or blanks which i want to avoid.
Bookmarks