You'll be able to get the ref of the cell you're adding it to and calculate the relative cells to it.

Is it always the same i.e. column to right + etc?

No -- that's the problem hence index and match to find the column then find the last value in that column ( which is a date )

And the column starts halfway down the sheet

Though if you mean the destination workbook and sheet -- I can specify which cell to put the formula in so that I can look it up from access.

That was my original plan
1 copy the formula to the sheet I need the info on.
2 formula finds the column and last value on it
3 I know exactly where the value is now so get it to access some method.

  There maybe some way the formula ( index and match which works no problem when it's anywhere in the sheet ) or a formula or code can be done directly from access.

I have a Loop macro to open each file in turn and paste into a cell -- only thing is I can't do it with that index and match formula. It's like excel is trying to resolve the formula even though I want to paste it as is.  ( ie it's inside quotation marks so you'd think it should see it as a string )

I changed the cell references to absolute ie with $ signs in front and that works now when doing the job manually. Also I used a recorded macro ( while doing it manually ) which also works but only because both sheets are open and I get a load of warnings about circular references etc.

I sorted the problem by taking the mountain to Mohammed ie copying the range involved on the data sheet (that's another problem I had to solve see below) to the "formula sheet" workbook with the formula already in a cell far enough to the right to be well clear of getting pasted over.

  I had trouble finding ( counting )  the number of columns cos the last column had a heading cell which was merged with 3 more columns to the right of it so it wouldn't see the last column and return a count which was one less than it should be.

  I solved that by creating temporarily a second sheet ( sheet 2 ) in the data workbook and pasting the values on sheet 1 with number formats only to sheet 2 then pasting that to the "formula sheet in the formula workbook.

  Now nested within the the loop macro I can suck out all the last dates along with any other relevant information about that record entry from every workbook in my trusted folder automatically. They all get saved along with a foreign key in a new record set ready to import to access.