Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-04-16
14
Medium Priority
?
1,309 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:Naresh Patel
ID: 40005115
Copy & Past Special as Values.

Thanks
0
 
LVL 8

Expert Comment

by:Naresh Patel
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Naresh Patel
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:Naresh Patel
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
 
LVL 8

Expert Comment

by:Naresh Patel
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:Naresh Patel
ID: 40005228
Is this is the one you are looking for?
0
 
LVL 8

Expert Comment

by:Naresh Patel
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 81

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 81

Accepted Solution

by:
byundt earned 1040 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

926 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