dynamic Chart

[ylafont]

I have bee trying to create a dynamic chart form data on another sheet. Since data is entered daily. I only need the last 5 days of to be able to display my requirements.

I have been able to Vlookup and match to find the required day and fill in the data for the chart with the formula.

=VLOOKUP($A4,'Daily Stats'!$A$1:$AA$13,MATCH(C$33,'Daily Stats'!$A$1:$AA$1,0),0)

My issues is that i cannot find a solution that will automatically fill in the dates for only the last 5 days. (being performed manually now)
I have been able to use lookup to locate the last entry

=LOOKUP(2,1/('Daily Stats'!1:1<>""),'Daily Stats'!1:1)

but when i combine that with offset

=OFFSET(LOOKUP(2,1/('Daily Stats'!1:1<>""),'Daily Stats'!1:1),1,0,1,1)

i get an error,

is there a better option?

The goal here is the have the last 5 days entered in the "Daily stats" sheet, automatically picked up by the "Charts" sheet.

thank you in advance.

 

[henrik2h]

Could you perhaps use the Large function for the dates?

Large(datecolumn,1)
Large(datecolumn,2) etc

 

[ylafont]

Could you perhaps use the Large function for the dates?

Large(datecolumn,1)
Large(datecolumn,2) etc

=LARGE('Daily Stats'!1:1,1) = retrieves the same same results as lookup function
=LOOKUP(2,1/('Daily Stats'!1:1<>""),'Daily Stats'!1:1)

However when combined with the offset function like

=OFFSET(LARGE('Daily Stats'!1:1,1),0,-5,1,1)

the following excel displays a formula error,

anyway to get around this?

thank you for the assistance!

 

[henrik2h]

I thought your goal was to find the last date entered (and the second, third, fourth and fifth)? If using Large,1 you will get the largest date (ie the latest input), Large,2 will get you the second largest etc. I may have misunderstood. This formula will update when you enter new data (new date) in the data sheet.

 

[jasonb75]

The error is caused because OFFEST is expecting a range for the first argument, but you're trying to pass an array to it instead.

In C3 and drag right. =LARGE('Daily Stats'!1:1,COLUMNS(C3:$G3))
In C4, drag right and down =INDEX('Daily Stats'!$2:$6,MATCH($A2,'Daily Stats'!$A$2:$A$6,0),MATCH(C$3,'Daily Stats'!$1:$1.0))

 

[ylafont]

I thought your goal was to find the last date entered (and the second, third, fourth and fifth)? If using Large,1 you will get the largest date (ie the latest input), Large,2 will get you the second largest etc. I may have misunderstood. This formula will update when you enter new data (new date) in the data sheet.

Ah - i use it as a formula for the other cells, that should work if it is placed in each cell usinf Large,2 - Large,3 ..... and so on.


jasonb75 --
l need to play with that index formula to see how it work.

Thank you both! much appreciated.