Solved

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

Posted on 2014-04-16
14
1,235 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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 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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 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