Unknown #REF!

[Billy Spivy]

Hello,

I copied and pasted across the sheet 'Time Clock Log' and in cell DY3 there is an error but all the other similar cells seem fine.

Can someone please try to figure out what is wrong?

Thank you,
Billy

[63falcondude]

Here is your formula:
=INDEX('Employee Info'!$B$5:$B$29,INT((COLUMNS($C:DY)-1)/5)+1)

=INT((COLUMNS($C:DY)-1)/5)+1 returns 26. There are only 25 rows in B5:B29.

You cannot return the 26th row when the reference is only 25 rows.

[Billy Spivy]

Oh okay so how can it be revised to allow for the extra columns please?

[Batnoob]

=INDEX('Employee Info'!$B$5:$B$29,INT((COLUMNS($C:DY)-1)/5)+1)

=INT((COLUMNS($C:DY)-1)/5)+1 try removing the "+1"

[Billy Spivy]

Mr. Batnoob thanks for the reply but that doesn't work.

[kroko]

I think your formula is not working very well yet, i mean this one:

Please Login or Register  to view this content.

if you observe the result:
C3 & L3: should be employee 1 and 2 with ID 112 and 118 (your formula is correct)
but starting from U3 it suppose to be 3rd employee which is ID 119 but your formula turn to 120 which skip one employee.. i am not sure if it is your intention.

If not my propose formula to you is as below:

Please Login or Register  to view this content.

please let me know if it doesn't work or not meet your requirement.

[FDibbins]

Your INDEX ref is only 24 columns wide (5:29), but the INT is returning 26, so it is out of range

[Billy Spivy]

Yes I posted another thread because I noticed it is skipping the 3rd ID. Cell U3 should be ID 119 not ID 120

[Glenn Kennedy]

The query relating to cell U3 has been answered....

[kroko]

Billy Spivy, yes i also note that, anyway my solution to you yesterday (post number 6 within this thread) will help you to solve all of that issue.

[Billy Spivy]

Billy Spivy, yes i also note that, anyway my solution to you yesterday (post number 6 within this thread) will help you to solve all of that issue.

The issue has been solved thank you. I had to extend the range.

[FDibbins]

Happy to help

[kroko]

good to hear that.