Excel question - having a formula to fill in the last entry in a range

I want a way to complete a dashboard with the month current month’s data, without having to change the cell each month. For example, I have a the following horizontal range:



Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13
0.85	1.00	0.97  0.99	

and I want a cell on a different page to show the current month’s data and the YTD total. Is there any way I can look for the last column greater than zero? If I was doing this in Access I might be able to use the “Last” function.

StG

Assuming you always want the current month, you can use a combination of the Today function and HLOOKUP to get the desired effect.

Assuming the data you’ve given is on the first two rows of Sheet1, and the dates are all the first of the month, this would work:

=hlookup(date(year(today()), month(today()), 1), Sheet1!A:B, 2, false)

I’m on my iPad in a pub, so might not have the brackets quite right.

That’s a great idea! I’ll give it a try. It would be the previous month (the month-end results are logged), but I might be able to make it work.

StG

Scougs - Thanks so much for the help - I was able to get it to work wonderfully.

StG