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 ?

​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 ?  Read More

​