Forum Discussion
How do I automatically find the address of a cell that contains specified text?
Thanks for reply -- sorry I may not have been clear. I'll try to explain exactly what I need to do.
I have two date columns but they can be in various places on the sheet. Each sheet can be from different pieces of equipment so the tests and number of tests ie columns of results recorded on these sheets will vary as will the header information above the main results tables, so the number of rows will vary too. ( the The only thing that is constant is the "Date" ( done date or date the last maintenance was done ) is always in column A but can be on any row.
I need to return the last date on that column to a database that manages the schedules etc.
That bit is the easy bit. ie the stuff in the A column.
The difficult part is of course the "Next Due Date" since it can be any number of columns over to the right of the Date column but will be on the same row as the "Date" header that is the part you have so kindly helped of with and works very well.
I was using =Lookup(2,1/(H:H<>""),H:H) to find the last date in that column by just manually typing in the Column address into the formula (H:H) which happens to be H on that one sheet. ( which works perfectly but only because I know which column )
I thought I could nest your formula inside to find the column address but it gives dates like 00/09/1900 or N/A when I try to do that.
Is there a way to find that column and return the last date on it. ie the Next Due Date.
We've ( you've ) found the cell address of the column header ( of a table half way down sheet ) so I thought it would be easy to resolve that to just that column and put that result into the Lookup formula above.
Obviously it's not that simple ( for me )
Thanks
I think I understand your challenge. Though, could you refer to the bullet point in my previous reply (Oct 30 2024 11:57 AM)?
Hi I've Attached a modified version of your sample spreadsheet which is similar to what I'd receive from engineers.
Dates I'm trying to return are highlighted top right below your formulas. Thank you for your patience with me.
Ivor.
Hi Ivorniceone
Your file clarify things. Given your data have headers:
in H7 (adjust the 2 AZ)
=INDEX(A:AZ, LOOKUP(2, 1/(A:A <> ""), ROW( A:A ) ), MATCH( "next due date", INDEX(A:AZ, LOOKUP(2, 1/(A:A = "date"), ROW( A:A ) ),), 0 ) )- Thank you very much this is the answer I've been trying to get to. Now all that's left is to have access go and get that from excel.
I'll have a go at that and report back if I come up with a way to do it.
Thanks again Lorenzo you've been a great help.