What a mess I've gotten myself into.
Here's my headache...
I have a good sized workbook that tracks my clients on a daily basis, then a monthly summary. ...each of the past 4 years are on individual sheets all formatted very similarly.
Example:
Sheet 1 = 2008
Sheet 2 = 2009
Sheet 3 = 2010
Sheet 4 = 2011
Each sheet has accounts listed in a row across the top of the sheet.
Just below each account name, is a YTD (year to date), and below that is a sum of all days in each month, and below that is the sales of every single day.
Not all sheets are identical, as each year new account are added. No account names are removed even if we do not do business with them in the entire year.
I have another worksheet where I want to create a dynamic chart (line graph).
I would like to create a drop-down list where the selection list is a lookup on the row where all accounts are listed on a sheet. (that's already done)
But, here's where it starts to melt my brain...
Based on the selection from the drop-down, I would like the chart to lookup that account name on all sheets (2008, 2009, 2010, and 2011), then pull the values within the accounts respective column for each month and chart that data.
So...
I select account "red stapler" from the drop-down.
Then, I lookup "red stapler" through the cell ranges identified on each sheet. ('2008'!B4:ZZ4, '2009'!B4:ZZ4, '2010'!B4:ZZ4, and '2-11'!B4:ZZ4).
Then, if "red stapler" is found in '2008'!AB4, '2009'!AC4, '2010'!AF4 and '2011'!BB4, I want it to pull data from rows 7-18 within that respective column ('2008'!AB7:AB18, '2009'!AC7:AC18, '2010'!AF7:AF18, and '2011'!BB7:BB18) and chart each value on a montly basis. (axis labels are (x-axis) JAN-DEC (7-18), and (y-axis) $ values, with each line labeled respective to the year (2008-2011).
I can map the chart out manually for each account, so the values can all be identified within the chart without trouble, it's the dynamic selection that's a royal pain in the ***.
Any help is greatly appreciated.
Here's my headache...
I have a good sized workbook that tracks my clients on a daily basis, then a monthly summary. ...each of the past 4 years are on individual sheets all formatted very similarly.
Example:
Sheet 1 = 2008
Sheet 2 = 2009
Sheet 3 = 2010
Sheet 4 = 2011
Each sheet has accounts listed in a row across the top of the sheet.
Just below each account name, is a YTD (year to date), and below that is a sum of all days in each month, and below that is the sales of every single day.
Not all sheets are identical, as each year new account are added. No account names are removed even if we do not do business with them in the entire year.
I have another worksheet where I want to create a dynamic chart (line graph).
I would like to create a drop-down list where the selection list is a lookup on the row where all accounts are listed on a sheet. (that's already done)
But, here's where it starts to melt my brain...
Based on the selection from the drop-down, I would like the chart to lookup that account name on all sheets (2008, 2009, 2010, and 2011), then pull the values within the accounts respective column for each month and chart that data.
So...
I select account "red stapler" from the drop-down.
Then, I lookup "red stapler" through the cell ranges identified on each sheet. ('2008'!B4:ZZ4, '2009'!B4:ZZ4, '2010'!B4:ZZ4, and '2-11'!B4:ZZ4).
Then, if "red stapler" is found in '2008'!AB4, '2009'!AC4, '2010'!AF4 and '2011'!BB4, I want it to pull data from rows 7-18 within that respective column ('2008'!AB7:AB18, '2009'!AC7:AC18, '2010'!AF7:AF18, and '2011'!BB7:BB18) and chart each value on a montly basis. (axis labels are (x-axis) JAN-DEC (7-18), and (y-axis) $ values, with each line labeled respective to the year (2008-2011).
I can map the chart out manually for each account, so the values can all be identified within the chart without trouble, it's the dynamic selection that's a royal pain in the ***.
Any help is greatly appreciated.
