Forum Discussion
How do I automatically find the address of a cell that contains specified text?
Hi I'm trying to find a way to have access database find an address on a specified spreadsheet ( there is one for each record in the database but they are all slightly different to each other ) that contains a cell with a unique bit of text specified by the search.
The text I'm looking for is "Date" and "Next Due Date." These are column headings but are not the first row on the spreadsheet. I also have to find the last recorded date on each column but I have already managed find the formula to do that.
Fortunately the word "Date" will always be in the first column ( ie column A ) so I've found the formula to find that no matter which row it starts at but the text "Next Due Date" can be in any column to the right of column F but will always be in the same row as the "Date" text.
Is there a way to index across and find that column address so that access can read the last date in both columns off the specified spreadsheet of the record in the database?
I can't modify the spreadsheets there are literally thousands of them and the rows where the information will be may change. The only thing that's common to all is that the word "Date" is in column A and is unique to that column and that "Next Due Date" will be on the same found row of "Date" and will be unique to it's column too.
Both bits of text are unique to each spreadsheet.
I'm using excel 2007 (and access 2007)
Can anyone help me with this puzzle ?
11 Replies
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)
