Return value from specified row based on last value of every row below it
I have seen similar questions answered, but none that would quite work for me.
I am working with two worksheets. The first one, called Daily Readings, contains readings for hundreds of different instruments. The readings are taken sporadically, but as they are taken, I enter them in this simple matrix worksheet with dates in row three and the name of the instruments in column A.
The second sheet, called Summary, is a list of the instruments, when the last reading was, its value, when the next reading will be, etc. The rows are set up just like the Daily Readings sheet. I use (LOOKUP(9.999999E+307,'Daily Readings'!200:200) to return the last value of each row, thus giving me the most recent value.
My question is, how do I use the value from the LOOKUP function to refer to row three of the column it came from? Row three contains the date, thus providing me with the value and the date the reading was taken.
I don't know anything about VBA so please try to avoid it if possible.
I hope this is clear. Please let me know if I can clarify any better.
Last edited by zacharius; 02-02-2012 at 05:28 PM.
Hi
Not really sure I follow your layout correctly. Can you attach a sample workbook for review.
rylo
I agree with rylo, an example sheet would help clarify things and show us the limitations, however:
If the readings are unique on each row, you can use MATCH/INDEX.
You could substitute your cell reference rather than re-doing the lookup.=INDEX('Daily Readings'!$3:$3,1,MATCH(LOOKUP(9.999999E+307,'Daily Readings'!200:200),'Daily Readings'!200:200,0))
If they are not unique, we will definitely need to see it to help.
To upload a sheet, press the Go Advanced button below and then Manage Attachments.
Cheers, Rob.
Apologies - duplicate post.
Just had another thought. You can find the last time a reading was taken using SUMPRODUCT:
The above finds the maximum Date value in Row 3 where there is an entry in Row 200.=SUMPRODUCT(MAX(('Daily Readings'!A200:IV200>0)*('Daily Readings'!A$3:IV$3)))
As an aside, you could use the date above to get the reading using INDEX, rather than using your current LOOKUP function. This would speed up the spreadsheet (if speed is an issue). To calculate the column, subtract the spreadsheet start date from the answer above. The row is =ROW('Daily Readings'!A200).
Cheers, Rob.
Thank you for the effort. I will post an example tomorrow when I'm back at work. The workbook is actually much more complicated than what I tried to explain so I will have to pull out the important parts for you.
The readings are distances and have the potential to be duplicated, especially if there was no change since the previous reading.
I think you're onto something there. I will check it out in the morning. Thanks!
Re: Return value from specified row based on last value of every row below it
Rob, you've done it. I just had to modify it slightly to include any non-blank value and extend it to column ZZ:
Thanks a lot! I have been trying to figure this out for longer than I care to admit.=SUMPRODUCT(MAX(('Daily Readings'!A200:ZZ200<>"")*('Daily Readings'!A$3:ZZ$3)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks