Solved

#ref error comes in index match formula when I delete the source row

Posted on 2014-04-16
14
1,206 Views
Last Modified: 2014-04-21
Hi Team,

I am copying sheet1-->Table column values to sheet2 using Index Match formula, which works quite fine.

But issue is only when I delete the source row i.e. Sheet1-->Table-->Row.

Because of deletion I do get #ref error in sheet2.Enclosing excel for your reference.

In sample file:
1. when I try to delete row which is referenced in sheet2 index match formula, I am getting #ref error.

Looking forward to hearing form you.
IndexMatch-Error.xlsx
0
Comment
Question by:satmisha
  • 7
  • 5
  • 2
14 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 40005115
Copy & Past Special as Values.

Thanks
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40005128
Sorry Past specials as values for values & past special as formula for formulas.
Just seen your attached file.

Thanks
0
 

Author Comment

by:satmisha
ID: 40005143
Thanks for replying.

I dont want to use VBA, only want to use formula.

Can I make some changes in the formula itself to handle this ?
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40005155
i dont know why you getting this error as i open this WB in my end & deleted one row in sheet1 ----no error "#ref#" just my formula range change row 30 - 36 to  30 - 35.

Thanks
0
 

Author Comment

by:satmisha
ID: 40005175
I am sorry, I gave wrong info ..

when you delete source row, I want destination table adjust itself and should not reflect row source deleted row.

for example, if you delete row ID:3 in sheet 1 then in sheet2 row3 should not come.
0
 

Author Comment

by:satmisha
ID: 40005207
I have attached sample file which shows expected resultset from the source table when user deletes the row in sheet1.

Hope this explains better.

Looking forward to hearing from you.
IndexMatch-Error.xlsx
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40005214
Then you have to use formula for column C.

Try this formula for Cell C7 & formula Drop Down.

=IFERROR(INDEX(Sheet1!$C$30:$C$49,MATCH(0,INDEX(COUNTIF(Sheet2!$C$6:C6,Sheet1!$C$30:$C$49),0,0),0)),"")

Thanks
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Expert Comment

by:itjockey
ID: 40005220
above formula i had used range C30:C49 you can use as per your requirement.but thing is that last values appear "0" after that it will remain blank.

Thanks
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40005228
Is this is the one you are looking for?
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40005242
Going Of The Desk  ----- To Late Here ------- Any More Assistance, Revert Me Back, I Will See In The Morning.

Thanks
0
 

Author Comment

by:satmisha
ID: 40005275
I tried on cell Sheet2-->D7 but nothing is happening, I am sure I am doing something wrong.
Can you please have a look on it.
IndexMatch-Error.xlsx
0
 
LVL 80

Expert Comment

by:byundt
ID: 40005611
I don't know why you need to allow the user to delete rows willy-nilly from Sheet1, but the following formula in cell C30 will return each cell in the table:
=IF(INDEX(Sheet1!C:C,30+ROWS(C$30:C30)-1)="","",INDEX(Sheet1!C:C,30+ROWS(C$30:C30)-1))
You may copy this formula down and across.

If the user deletes a row from the middle of the original table, the values returned by the formula will close up the gap. If the user deletes a row from the beginning or end of the original table, the formula will return an empty string (looks like a blank).
IndexMatch-ErrorQ28414299.xlsx
0
 
LVL 80

Accepted Solution

by:
byundt earned 260 total points
ID: 40005629
Another approach is to put the original data in a Table using the Insert...Table menu item. You can then use a formula like this to return the values on Sheet2:
=IFERROR(INDEX(Table1,ROWS(F$30:F30),COLUMNS($F30:F30)),"")

As written, the formula was placed in cell F30. It may be copied down and across, and will return an empty string (looks like a blank) if rows are deleted from the original Table (named Table1 in the above formula).
IndexMatch-ErrorQ28414299x.xlsx
0
 

Author Closing Comment

by:satmisha
ID: 40013580
Thanks a lot for wonderful solution, this is exactly I was looking for.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now