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
- Thank you very much this helps a lot and works well to find the cell reference address as you have done. I wouldn't have thought to use sumproduct to count the columns to the Next Due Date cell. One question, I originally used just a simple match function on the first column as I know the "Date" data is always going to be in that column, but when putting that ( ie your M1 cell ) into the "Next Due Date" formula it wouldn't work until I entered your whole formula ie =MATCH( "Date", INDEX( RawData,, 1), 0) + INDEX( ROW( RawData), 1) -1 ie it seemed to need the same array specified in the second ( Next Due Date ) formula. Why when my simple match formula returned the same number for "Date" column?
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
