Hi all,
I'll preface this by saying that I have done a full search and can not get any close to my desired outcome. I'm a great lover of the forum and google, hence not having to post here before! But I'm really stumped and this (probably easy for you lot) formula is holding up my entire project...
Simply put, I have a user-friendly dashboard as a front page, the raw data would look a little like this for the aspect that we are concerned with:
<tbody>
</tbody>
Client One's references must be completed before Client Two's, though the reference numbers themselves are irrelevant (i.e. non consecutive order). I have completion times on a separate tab already, which looks a little like this:
<tbody>
</tbody>
As we can see above, 202 (client 2) was finished before 103 (client one). So I just need a formula that will highlight this on the dashboard (i.e. conditional formatting just to fill the reference number cell). Whenever I get stuck I always write out the formula I need in words I can understand before converting it into excel speak. So in this case I've got:
(IF(SHEET1!REFERENCE#1 IS FOUND IN SHEET2) THEN(FINDCOMPLETIONTIME))...(IF(REFERENCE#1TIME > REFERENCE#2TIME... TRIGGER CONDITIONAL FORMATTING)). This may have confused you more than it has helped...
I had index/matched across parts of the data but given that there are a LOT of reference numbers (not perfectly aligned into columns or rows either I'm afraid) it got very sloppy very quickly. Does anyone have any suggestions, have I made any sense at all?
I'll preface this by saying that I have done a full search and can not get any close to my desired outcome. I'm a great lover of the forum and google, hence not having to post here before! But I'm really stumped and this (probably easy for you lot) formula is holding up my entire project...
Simply put, I have a user-friendly dashboard as a front page, the raw data would look a little like this for the aspect that we are concerned with:
| (COL A) | Client One (COL B) | Client Two (COL C) |
| Reference (A2) | 101 (B2)... | 202 |
| 103 | 293 | |
| 162 | 230 |
<tbody>
</tbody>
Client One's references must be completed before Client Two's, though the reference numbers themselves are irrelevant (i.e. non consecutive order). I have completion times on a separate tab already, which looks a little like this:
| Reference (COL A) | Completion (COL B) |
| 101 | 03:15 |
| 202 | 03:17 |
| 103 | 03:19 |
<tbody>
</tbody>
As we can see above, 202 (client 2) was finished before 103 (client one). So I just need a formula that will highlight this on the dashboard (i.e. conditional formatting just to fill the reference number cell). Whenever I get stuck I always write out the formula I need in words I can understand before converting it into excel speak. So in this case I've got:
(IF(SHEET1!REFERENCE#1 IS FOUND IN SHEET2) THEN(FINDCOMPLETIONTIME))...(IF(REFERENCE#1TIME > REFERENCE#2TIME... TRIGGER CONDITIONAL FORMATTING)). This may have confused you more than it has helped...
I had index/matched across parts of the data but given that there are a LOT of reference numbers (not perfectly aligned into columns or rows either I'm afraid) it got very sloppy very quickly. Does anyone have any suggestions, have I made any sense at all?
