dont know how to fix this INDEX formula with #REF! errors

C

curiouscoder

New Member
Joined
Jun 24, 2022
Messages
31
Office Version
  1. 365
Platform
  1. Windows
getting REF! errors in customerID column

customer ID formula
Excel Formula: 
=INDEX(Table1[Customer ID],[@[Cell Row]])
cell row formula =
Excel Formula: 
=IFERROR(SUBSTITUTE(SUBSTITUTE([@[Cell Changed]],"H",""),"$",""),[@[Cell Changed]])
in this table

Date + TimeChange typeCell ChangedOld StatusNew StatusCustomer IDCell RowDateTime
29/06/2022 17:35​
Cell Change$H$19
4​
1 - AssignedJ1919
29/06/2022​
17:35​
29/06/2022 17:35​
Cell Change$H$19
4​
2 - In ProgressJ1919
29/06/2022​
17:35​
29/06/2022 17:36​
Cell Change$H$192 - In Progress3 - Escalated Pre CCJ1919
29/06/2022​
17:36​
29/06/2022 17:36​
Cell Change$H$192 - In Progress4 - Sent for Initial CheckJ1919
29/06/2022​
17:36​
29/06/2022 17:36​
Cell Change$H$192 - In Progress5 - Returned from Initial CheckJ1919
29/06/2022​
17:36​
29/06/2022 17:36​
Cell Change$H$192 - In Progress6 - Sent for CCJ1919
29/06/2022​
17:36​
29/06/2022 17:36​
Cell Change$H$192 - In Progress7 – Request for more info from CCJ1919
29/06/2022​
17:36​
29/06/2022 17:36​
Cell Change$H$192 - In Progress8 – Customer response received from CCJ1919
29/06/2022​
17:36​
29/06/2022 17:36​
Cell Change$H$192 - In Progress8i - Escalated Post CCJ1919
29/06/2022​
17:36​
29/06/2022 17:36​
Cell Change$H$192 - In Progress9 - Uplift in ProgressJ1919
29/06/2022​
17:36​
29/06/2022 17:36​
Cell Change$H$192 - In Progress10 - Sent for QCJ1919
29/06/2022​
17:36​
29/06/2022 17:37​
Cell Change$H$192 - In Progress11 - Returned from QCJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress12 - Sent for QC2J1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress13 – Final uplift completeJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress14 – Awaiting GWSJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress15a - CD: FCRP R&RJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress15b - CD: FCRP ExitJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress15c - CD: Non-responder exitJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress15d - CD: R&R LocalJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress15e - CD: DowngradedJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress15f - CD: DescopedJ1919
29/06/2022​
17:37​
29/06/2022 17:37​
Cell Change$H$192 - In Progress16 – Alert closed/ updatedJ1919
29/06/2022​
17:37​
29/06/2022 17:38​
Cell Change$H$1916 – Alert closed/ updated1 - AssignedJ1919
29/06/2022​
17:38​
29/06/2022 17:40​
Cell Change$H$20
5​
8i - Escalated Post CCJ2020
29/06/2022​
17:40​
29/06/2022 17:40​
Cell Change$H$20
5​
10 - Sent for QCJ2020
29/06/2022​
17:40​
29/06/2022 17:41​
Cell Change$H$21
5​
8i - Escalated Post CCJ2121
29/06/2022​
17:41​
29/06/2022 17:47​
Cell Change$H$22
1​
16 – Alert closed/ updatedJ2222
29/06/2022​
17:47​
29/06/2022 17:47​
Cell Change$H$22
1​
15f - CD: DescopedJ2222
29/06/2022​
17:47​
29/06/2022 17:47​
Cell Change$H$22
1​
15e - CD: DowngradedJ2222
29/06/2022​
17:47​
29/06/2022 17:47​
Cell Change$H$22
1​
15d - CD: R&R LocalJ2222
29/06/2022​
17:47​
29/06/2022 17:47​
Cell Change$H$22
1​
15c - CD: Non-responder exitJ2222
29/06/2022​
17:47​
29/06/2022 17:47​
Cell Change$H$22
1​
15b - CD: FCRP ExitJ2222
29/06/2022​
17:47​
29/06/2022 17:47​
Cell Change$H$22
1​
15a - CD: FCRP R&RJ2222
29/06/2022​
17:47​
29/06/2022 17:47​
Cell Change$H$22
1​
14 – Awaiting GWSJ2222
29/06/2022​
17:47​
30/06/2022 09:09​
Cell Change$H$31 - AssignedJ33
30/06/2022​
09:09​
30/06/2022 09:09​
Cell Change$H$32 - In ProgressJ33
30/06/2022​
09:09​
30/06/2022 09:09​
Cell Change$H$51 - AssignedJ55
30/06/2022​
09:09​
30/06/2022 09:21​
Cell Change$H$71 - AssignedJ77
30/06/2022​
09:21​
30/06/2022 12:04​
Cell Change$H$31 - AssignedJ33
30/06/2022​
12:04​
30/06/2022 12:42​
Cell Change$H$31 - AssignedJ33
30/06/2022​
12:42​
30/06/2022 12:42​
Cell Change$H$61 - AssignedJ66
30/06/2022​
12:42​
30/06/2022 12:42​
Cell Change$H$91 - AssignedJ99
30/06/2022​
12:42​
30/06/2022 12:43​
Cell Change$H$51 - AssignedJ55
30/06/2022​
12:43​
30/06/2022 12:44​
Cell Change$H$71 - AssignedJ77
30/06/2022​
12:44​
30/06/2022 12:44​
Cell Change$H$31 - AssignedJ33
30/06/2022​
12:44​
30/06/2022 12:44​
Cell Change$H$32 - In ProgressJ33
30/06/2022​
12:44​
30/06/2022 12:44​
Cell Change$H$51 - AssignedJ55
30/06/2022​
12:44​
30/06/2022 12:45​
Cell Change$H$52 - In ProgressJ55
30/06/2022​
12:45​
30/06/2022 12:50​
Cell Change$H$31 - AssignedJ33
30/06/2022​
12:50​
30/06/2022 12:50​
Cell Change$H$41 - AssignedJ44
30/06/2022​
12:50​
30/06/2022 12:50​
Cell Change$H$31 - Assigned2 - In ProgressJ33
30/06/2022​
12:50​
30/06/2022 12:50​
Cell Change$H$41 - Assigned2 - In ProgressJ44
30/06/2022​
12:50​
30/06/2022 12:51​
Cell Change$H$41 - Assigned3 - Escalated Pre CCJ44
30/06/2022​
12:51​
30/06/2022 12:51​
Cell Change$H$32 - In Progress4 - Sent for Initial CheckJ33
30/06/2022​
12:51​
30/06/2022 12:51​
Cell Change$H$43 - Escalated Pre CC5 - Returned from Initial CheckJ44
30/06/2022​
12:51​
30/06/2022 13:09​
Cell Change$H$31 - AssignedJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$32 - In ProgressJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$33 - Escalated Pre CCJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$34 - Sent for Initial CheckJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$35 - Returned from Initial CheckJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$36 - Sent for CCJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$37 – Request for more info from CCJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$38 – Customer response received from CCJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$38i - Escalated Post CCJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$39 - Uplift in ProgressJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$310 - Sent for QCJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$311 - Returned from QCJ33
30/06/2022​
13:09​
30/06/2022 13:09​
Cell Change$H$312 - Sent for QC2J33
30/06/2022​
13:09​
30/06/2022 13:10​
Cell Change$H$313 – Final uplift completeJ33
30/06/2022​
13:10​
30/06/2022 13:10​
Cell Change$H$314 – Awaiting GWSJ33
30/06/2022​
13:10​
30/06/2022 13:10​
Cell Change$H$315a - CD: FCRP R&RJ33
30/06/2022​
13:10​
30/06/2022 13:10​
Cell Change$H$315b - CD: FCRP ExitJ33
30/06/2022​
13:10​
30/06/2022 13:10​
Cell Change$H$315c - CD: Non-responder exitJ33
30/06/2022​
13:10​
30/06/2022 13:10​
Cell Change$H$315d - CD: R&R LocalJ33
30/06/2022​
13:10​
30/06/2022 13:10​
Cell Change$H$315e - CD: DowngradedJ33
30/06/2022​
13:10​
30/06/2022 13:10​
Cell Change$H$315f - CD: DescopedJ33
30/06/2022​
13:10​
30/06/2022 13:10​
Cell Change$H$316 – Alert closed/ updatedJ33
30/06/2022​
13:10​
30/06/2022 13:38​
Cell Change$H$31 - AssignedJ33
30/06/2022​
13:38​
30/06/2022 13:38​
Cell Change$H$81 - AssignedJ88
30/06/2022​
13:38​
30/06/2022 13:38​
Cell Change$H$2StatusColumn1J22
30/06/2022​
13:38​
30/06/2022 13:38​
Cell Change$H$2StatusColumn1J22
30/06/2022​
13:38​
30/06/2022 13:38​
Cell Change$H$2StatusStatusJ22
30/06/2022​
13:38​
30/06/2022 13:38​
Cell Change$H$221 - AssignedJ2222
30/06/2022​
13:38​
30/06/2022 13:39​
Cell Change$H$211 - AssignedJ2121
30/06/2022​
13:39​
30/06/2022 15:02​
Cell Change$H$31 - AssignedJ33
30/06/2022​
15:02​
30/06/2022 15:02​
Cell Change$H$32 - In ProgressJ33
30/06/2022​
15:02​
30/06/2022 16:28​
Cell Change$H$261 - AssignedJ2626
30/06/2022​
16:28​
30/06/2022 16:29​
Cell Change$H$31 - AssignedJ33
30/06/2022​
16:29​
30/06/2022 16:29​
Cell Change$H$32 - In ProgressJ33
30/06/2022​
16:29​
30/06/2022 16:34​
Cell Change$H$41 - AssignedJ44
30/06/2022​
16:34​
30/06/2022 16:35​
Cell Change$H$51 - AssignedJ55
30/06/2022​
16:35​
30/06/2022 16:35​
Cell Change$H$52 - In ProgressJ55
30/06/2022​
16:35​
30/06/2022 16:39​
Cell Change$H$61 - AssignedJ66
30/06/2022​
16:39​
30/06/2022 16:41​
Cell Change$H$271 - AssignedJ2727
30/06/2022​
16:41​
30/06/2022 16:52​
Cell Change$H$281 - Assigned
#REF!​
28
30/06/2022​
16:52​
30/06/2022 16:55​
Cell Change$H$32 - In Progress2 - In ProgressJ33
30/06/2022​
16:55​
30/06/2022 16:55​
Cell Change$H$32 - In Progress1 - AssignedJ33
30/06/2022​
16:55​
30/06/2022 16:55​
Cell Change$H$32 - In Progress2 - In ProgressJ33
30/06/2022​
16:55​
30/06/2022 16:55​
Cell Change$H$32 - In Progress3 - Escalated Pre CCJ33
30/06/2022​
16:55​
30/06/2022 17:16​
Cell Change$H$291 - Assigned
#REF!​
29
30/06/2022​
17:16​
30/06/2022 18:19​
Cell Change$H$31 - AssignedJ33
30/06/2022​
18:19​



it is being referenced off this table

Alert IDCustomer IDEntity TypeStatusPod (Team)ReviewerAssignedIn Progress
J11 - Assigned
#N/A​
J2
J3
J4
J5
J6
J7
J8
J9
J10
J11
J12
J13
J14
J15
J16
J17
J18
J19
J20
J211 - Assigned
30/06/2022 13:39:24​
J22
J231 - Assigned
#N/A​
J241 - Assigned
#N/A​
J251 - Assigned
#N/A​
J261 - Assigned
30/06/2022 16:28:50​
J271 - Assigned
30/06/2022 16:41:33​

any help would be great because i dont know how to adjust my formula
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Your formula try to fetch item 28 or 29 out of a table that has 27 lines; so you should check how it is possible that column [Cell Changed] be filled with a value higher than 27...
Of cours e you could hide the error using =IFERROR(TheFormula,"") but hiding a situation doesn't mean fixit it...
 
Upvote 0
Anthony47 said:
Your formula try to fetch item 28 or 29 out of a table that has 27 lines; so you should check how it is possible that column [Cell Changed] be filled with a value higher than 27...
Of cours e you could hide the error using =IFERROR(TheFormula,"") but hiding a situation doesn't mean fixit it...
Click to expand...
1656666580633.png


as you can see values are in range so unsure as to why its still not working
 
Upvote 0
I refer to content of column [Cell Changed] in the first table: the two #REF! come out from those $H$28 and $H$29
 

Attachments

  • REF_ERR_Immagine 2022-07-01 111357.jpg
    REF_ERR_Immagine 2022-07-01 111357.jpg
    124.6 KB · Views: 3
Upvote 0
I figured it out, my table started at row 2. I moved it back to one and the errors disappeared
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
60
Fluff
Fluff
G
  • Question
Formula to Retrieve uniqueID row Above
Replies
1
Views
350
Trebor76
T
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
239
SaraWitch
SaraWitch
G
  • Solved
Splitting Dates with Unique ID
Replies
6
Views
411
Akuini
Akuini
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
309
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,962
Messages
6,122,482
Members
449,088
Latest member
Melvetica

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top