Showing two values stacked but also showing their individual lines all on the same graph

[Robbie8]

Hi
I'm not sure if this is possible, but I'll ask anyway

I have 2 separate charts made from formulas where the name (in this case Name 3) is actually the same person - One on the "Linux Assigned" tab line chart and One on the "Unix assigned" tab line chart. What I would like to be able to do is show all 3 on the same chart.

i) The line graph for the Linux assignment
ii) The line graph for the Unix assignment
iii) the line graph for the combined Unix and Linux assignment.

This is further complicated because I have had to have formulas created as I want my graphs to auto update with the 110 days from today so it keeps rolling forward. As such I have a calc tab at the end, formulas and entries in Name Manager which drive this process.

Is there a simple way to show the 2 individual and then stacked version on the chart on the same one?.

If so your advice would be appreciated?

Thanks

Rob

[dflak]

At first I thought I would have to define a lot of named dynamic ranges to make this work. Instead I found that I could make it work with Excel Tables and VLOOKUPs. I used tables for several reasons: they are easy to reference and they know how big (how many rows and columns) they are.

Take a look at the Linux Unix Chart Tab.

Cell B1 contains today's date. Cell B2 is the employee of interest. This must match the table column name and I do not have data validation on it.

Cells B5:B115 contain the dates. Column C Contains the Linux resources for the date, Column D contains the Unix resources and Column E is the combined.

About the only trick in the VLOOKUPs is COLUMN(INDIRECT("Table_Linux_Resources[" &$B$2 & "]")) - this finds the column in which the employee of interest is found. I suppose I could have used MATCH instead.

To learn more about tables, see this article: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

[Robbie8]

Hi dflak
Thats exactly what I wanted - thank you thats perfect.

Rob