INDEX formula not calculated properly

Hi there,

I have quite a complicated spreadsheet that everyone here gave fabulous input to help get it up and running but there seems to be a couple of issues happening:

1. The Column K INDEX formula doesn't seem to be calculating properly. For example there is a highlighted row (272) where column K should be '24' but is the wrong amount. Could anyone help me here?

The idea of this column is that each person that stays here stays for a number of nights that will come out of their approved nights (Column G) that pulls from the 'Current Approvals' sheet depending on the approval number.



2. Every time I open the spreadsheet it seems to want to update links from an old spreadsheet - could i rectify this so that it stops referring to an old spreadsheet?

Thanks for your help - I am hoping the title is relevant. Please let me know if this is not clear.

Thanks

Heidz

1. the formula is doing it "right". MATCH finds the first instance of your name in B272 (Clint Kingston) inside Current Approvals worksheet and finds it in row 36. I suppose you actually want row 37 with Clint Kingston as well but dated 22-Jun. seems like there is no unique ID you can use that won't create such a problem? not sure if combining name with date will solve it. you know your data better. if it does, you have to use an array formula such as this in cell K3 and copy down:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

2. go to DATA tab -> Edit Links. there is a link connected to G drive. some links such as Named Range or Data Validation are not so easily deleted. like this particular one. you have to know where it exist in. otherwise, try to copy 1 sheet at a time to a new spreadsheet to see which has the old link attached to it

2. Every time I open the spreadsheet it seems to want to update links from an old spreadsheet - could i rectify this so that it stops referring to an old spreadsheet?

I don't see evidence of it referring to another spreadsheet. It appears to be working properly. Of note: there are two Clint Kingstons with different approved nights. The formula would have to match the second one returning 28. Then 28-4=24.

What indicators are there that the second Clint Kingston is the desired match?

Or can use the approval number. This can be committed with just Enter.

Formula:

Please Login or Register  to view this content.

I tried both benishiryo's and my version. They both return 24 at my end.

Withdrawn by FR.

You are welcome. Thank you for the feedback and marking your thread Solved.

Desensitised version of spreadsheet example attached

Originally Posted by heidzhaydz

Desensitised version of spreadsheet example attached

Thank you very much for that heidzhaydz. It will help future researchers.