Project Turnover Alaysis, Chart, VBA, Macro

[OlliTbb]

Hi all!

I'm trying to make my Turnover Analysis a bit more time efficient using macros and VBA. I allready got so far that by clicking on a button a macro imports turnover data from a different excel file.
Data:
A9-A100: Days (Format: YYYY-MM-DD), starting A9
B8-AA8: Customers Names
AB9-AB100: Totals
A101: Totals

Now I'm at a point where I don't get any further...

1. Box01 and Box02:
I need to have a Box, lets name it "Box01" with a drop down menu that includes all names from row "B". Since the imported data and customers names may differ over time Box01 schould update the new names automatically, too.
I also need a second box "Box02". With Box02 I want to choose between the timeframes "All", "24 Months", "12 Months", "YTD" (Year-to-date), and "1 Month".

2. Graph
This might be more complicated... I now want to select a Customer in Box01, lets call him "CustAAA", and a the timeframe "1 Month" in Box02. Then I press "Button02". This now should do the following: create a column chart with the turnovers of "CustAAA" of the last 12 months. Last date in column A is 2009-12-15. It therefore should include all dates beginning 2008-12-15 (if this day is not in the list due to a holiday or weekend it should take the next availaby date e.g. 2008-12-17). The macro can not just jump to row 100 because the list will get longer each day. It also can not jump to the last number in the column, because this is the "Total".
By selecting "CustBBB" in Box01 and "YTD" in Box02 the macro shall make a chart with the turnovers of "CustBBB" including all data from the recent year. Today selecting YTD should range from 2009-01-01 (this is a holiday so take next day available) until 2009-12-15. Selecting YTD on January 10th next year therefore should range from 2010-01-01 (holiday -> take next day available) until 2010-01-09.
Ideally the headline of the chart would include the selected customers name ;-)

If anyone would be able to solve only part of my problem please post the code that would help me, too!

Thanks a lot for your effort!!!!

[teylyn]

Tach Olli,

what you want to do can be done without macros, actually there are several possible approaches.

One viable approach might be a Pivot Table/Chart, which automatically gives you drop downs to select only parts of your data.

Another approach might be to create some kind of dashboard, using dynamic ranges and a few helper tables hidden away somewhere else in your workbook.

I need to have a Box, lets name it "Box01" with a drop down menu that includes all names from row "B". Since the imported data and customers names may differ over time Box01 schould update the new names automatically, too.

This can be achieved with data validation - List and using a dynamic range name to adjust the named range to expand when new data items are added.
To set up a dynamic range, click Insert - Name - Define, enter a name, z.B. CustomerList and put a formula like this in the Refers to box

=OFFSET(Sheet1!$B$8,0,0,1,COUNTA(Sheet1!$8:$8)-1)

(version for semicolon instead of commas in formulas)
=OFFSET(Sheet1!$B$8;0;0;1;COUNTA(Sheet1!$8:$8)-1)

If A8 is empty, delete the "-1" at the end of the formula. Also, if your Excel settings are to use the semicolon instead of the comma in formulas, you need to use the second version.

Now go to the cell where you want the dropdown and click Data - Validation. Select List and enter this in the source box

=CustomerList

I also need a second box "Box02". With Box02 I want to choose between the timeframes "All", "24 Months", "12 Months", "YTD" (Year-to-date), and "1 Month".

For this, you need to create a table somewhere in your workbook, maybe on a different sheet, list all the above values, and assign this list a range name. This one doesn't have to be dynamic, so you can just use the ususal approach. Again, use the list name for the data validation where you want to select the value.

....

Blut geleckt?

Now.... to help you further, it would really help to see your spreadsheet. So, maybe you could prepare a version with sufficient dummy data to illustrate what you want to do, and upload it here. You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

I'm more than happy to guide you through the process and give suggestions. We could also continue this thread in German, if that would make you feel more comfortable. I know it's sometimes hard to come up with the English terms.

cheers

[OlliTbb]

Moin moin!

Many thanks for your help and effort!

I think we should continue this thread in English, just in case someone else wants to help or is interested in this topic as well...

I might upload a dummy file later. I don't dare to upload any files from work

[teylyn]

Yup, no prob. Please make sure that any file you upload contains only dummy data.

Frankfurt in December .... I couldn't stomach it. December in NZ is way better