?
Solved

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

Posted on 2014-04-16
14
Medium Priority
?
1,341 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

592 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