Last Entry

[DanG]

I have two columns:
"Date" and "Appts Next Week". All the dates are filled in (the dates are the Friday of every week for the year).
The data entry person finds the current date and puts in the appts as requested. I want to capture the # of appts that were last entered on a summary report.
So if 5/25/2007 next weeks appointments were 5 then I would want to capture 5 (not the previouse dates though).

Hope this makes sense.
I'm thinking VLookup but not sure how to construct it.

Thank you

 

[shades]

Howdy. If I understand correctly I think you want SUMPRODUCT, since it is conditional (two conditions: equal to less than current date, and equal to and greater than current dat - 7 ).
________
BUY IOLITE

 

[DanG]

Pretty much right.
An example
date1= 5/1/2007 data1= 49
date2 = 5/25/2007 data2 = 40
date3 = 6/3/2007 data3 = (nothing entered)

I want to pull the data related to date2 (which would be 40).
So I do understand how sumproduct works...
=sumproduct((date a1:a52<=now())*(?))
?= not sure how to just get the "40" I'm looking for as this would sum all data entries.


Thank you for the help!

 

[shades]

Sorry, I misunderstood what you are looking for.

Try this: Assume that your data is in Column B and your dates in column A. This formula will find the last entered value in column B.

=INDEX(B:B,MATCH(9.99999999999E+307,B:B),1)
________
Oxygen Vaporizer

 

[Brianwarnock]

Took me a while to figure this out, it's an abuse of the MATCH function, bloody cunning.

Brian

 

[shades]

Yeah, I learned this from Aladin - brilliant Excel formulist...

Actually, if this occurs more than once in a workbook, then it is wise to name the number as BigNum

=9.99999999999E+307

Then the above formula becomes

=INDEX(B:B,MATCH(BigNum,B:B),1)
________
Herbalaire

 

[DanG]

Sorry it took so long to get back.
I'll give it a try and then do my homework as to how it works.

Thank you very much!

 

[DanG]

That's just too cool!

I was playing around to get the cell reference...
=CELL("address",INDEX(B:B,MATCH(9.99999999999E+307,B:B),1))

and it returns the cell reference with no problem.

This one has me stumped a bit. I understand the index and match, I just don't get the 9.999... part. Can you explain?

Thanks!

 

[shades]

The MATCH formula is looking for the row number of last item. 9.99999999999999E+307 is the largest number that Excel can handle. When you search for this value using an approximate lookup (i.e., when MATCH's 3rd, optional argument is 1 or omitted or TRUE) then the last value in the lookup array is returned. MATCH will then return an approximate match in this case (i.e. less than or equal to that large number).
________
Montana Dispensaries

 

[DanG]

great trick!

thank you very much for the detailed explanation.
Who the heck thinks of this stuff?

Thanks!