Forum Discussion
How do I automatically find the address of a cell that contains specified text?
Hi Ivorniceone
With Excel 2007? Not sure at all as I run the latest version... See if the attached sample helps, assuming I understood the challenge:
To make things clear and for readability of the formulas, the greyed area of the sheet has been named RawData
I now have an issue with the last date in each of the date columns. I had previously used a lookup manually entering the column letter H:H but when I try to substitute that with the results of your formula ie $H$7 it doesn't work.
I may need to extract just the H from that new address found.
Sorry but I'm lost. So, could you please share a representative workbook with 1 sheet showing:
- A sample of the data (as you get/receive them)
- In a separate cell manually write the your exact expected result (with/from the above data)
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- 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
if with sql, it is easy to get result by headers of a table.
e.g.
select max(`next date`) from sheet1;
