Solved

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

Posted on 2014-04-16
14
1,249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

751 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