How to use OFFSET when the reference is a formula linked to another tab?

Hi All,

I have a formula that works perfectly...
=MIN(IF('Main Log'!$D$4:$D$2000=$B4,IF('Main Log'!$O$4:$O$2000,"",'Main Log'!$E$4:$E$2000)))

What this does is checks my 'Main log' to look for the instances where:
- Appointment type matches what I've specified in B4 (in Column D)
-The subsequent report has no completion date (in Column O)
This cell now shows me the start date of this (from Column E) for the oldest case of this type with no completion date.

Let's say I have this in J4 of my monitoring sheet. Now I want to show the name of this oldest case in J5, which can be found in Column G on my 'Main Log' (2 cells to the right of my start date).

I need a formula for J5 that finds the information 2 cells to the right of what J4 has found.

I tried a basic =OFFSET(J4,0,2) but this just uses the J4 cell as a starting point, rather than starting at the 'Main Log' location that J4's formula is returning.

Please help someone!

Thanks in advance, Steve

Cross-posted at: https://www.mrexcel.com/board/thread...query.1213960/

So now you have to retreive data from column G with 3 conditions met :
in given row
'Main Log'!$D$4:$D$2000= $B4
'Main Log'!$O$4:$O$2000 = ""
'Main Log'!$E$4:$E$2000 = just found minimum

so it shall be something like:

Formula:

Please Login or Register  to view this content.

Hope it helps, if not - see the yellow box about sample attachment above

Welcome (back) to the forum.

Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

1) if you have cross-posted, and

2) the URL of the cross posts.

That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

(Note: since you are new here, on THIS occasion, I have done it for you.)

So the answer above is almost correct. I only misunderstood where NHS contract column is located.
The version to be used in sample file (change semicolons to commas as needed or see the attachment

Formula:

Please Login or Register  to view this content.