Google Sheets: How to delete rows with #REF! error while referencing other rows from a different sheet

[RicardoRincon]

I think the solution must be simple. So I have two sheets, one with data that is added every time a new order is made, Column A is the Brand Name and column B is the Order Name for that brand. In the second sheet I reference Column A and B of the first sheet to have the exact same data, brand name and order name, the other columns in the second sheet are other calculations that I make for the specific Order, but I think that's irrelevant for my problem. My problem is that when data is eliminated in either columns A or B or an entire row is eliminated in the first sheet, I get a #REF! error in the relevant row of the second sheet and the reference formula of the entire column skips this row with the #REF! error. So the optimal scenario would be for the entire row that gives #REF! error to be eliminated and to prevent it from being skipped from the reference formula to the other sheet. I hope this makes sense, I will attach both sheets to have a better understanding.

When I eliminate highlighted row...

 

[RasGhul]

Hello you should have the filter function available in Excel 2021.

If you extract A & B columns with filter, deleting rows doesn't affect the extracted rows. Enter this in A2 on Lead Time Def tab.

This will extract 500 rows, extend your Cadena Column A range in Filter formula as required;

=FILTER(Cadena!$A$4:$B$500,Cadena!$A$4:$A$500<>"","")

 

[RicardoRincon]

Hello you should have the filter function available in Excel 2021.

If you extract A & B columns with filter, deleting rows doesn't affect the extracted rows. Enter this in A2 on Lead Time Def tab.

This will extract 500 rows, extend your Cadena Column A range in Filter formula as required;

=FILTER(Cadena!$A$4:$B$500,Cadena!$A$4:$A$500<>"","")

Thank you so much for your reply RasGhul,

I tried what you recommended, but excel gave me an #N/A error that says:

FILTER has mismatched range sizes. Expected row count: 497. column count: 1. Actual row count: 1, column count: 1.

If you have any idea I would appreciated greatly

 

[RasGhul]

Okay we can use the old method, this will also return 500 rows, extend your ranges just to cover your total Cadena rows sheet.

My sample data but same application;

Sample Data.xlsx
	A	B
1	Customer/Branch	Name
2	C3000088	ELTRON
3	C3000088	ELTRON
4	C3000088	ELTRON
5	C3000088	ELTRON
6	C3000088	ELTRON
7	C3000088	ELTRON
8	C3000195	REECE PTY LTD - SA BURWOOD
9	C3000208	S.C LIGHTING & WATTLE PARK
10	C3000288	AUSLEC PURCHASING CO WINGFIELD
11	C3000414	CNW ELECTRICAL BERRIMAH
12	C3000414	CNW ELECTRICAL BERRIMAH
13	C3000426	CNW ELECTRICAL EDWARDSTOWN
14	C3000426	CNW ELECTRICAL EDWARDSTOWN
15	C3000426	CNW ELECTRICAL EDWARDSTOWN
16	C3000440	CNW ELECTRICAL KENT TOWN
17	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
18	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
19	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
20	C3000573	HAYMANS ELECTRICAL FERRYDEN PA
Sheet1

Cell Formulas
Range	Formula
A2:A20	A2	=IF(ROWS($A$2:A2)>COUNTA(Cadena!$A$2:$A$500),"",INDEX(Cadena!$A$2:$A$500,SMALL(IF(Cadena!A:A<>"",ROW(Cadena!$A$2:$A$500)-ROW(Cadena!$A$1)),ROWS($A$2:A2))))
B2:B20	B2	=IF(ROWS($B$2:B2)>COUNTA(Cadena!$A$2:$A$500),"",INDEX(Cadena!$B$2:$B$500,SMALL(IF(Cadena!A:A<>"",ROW(Cadena!$A$2:$A$500)-ROW(Cadena!$A$1)),ROWS($B$2:B2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RicardoRincon]

OMG IT WORKED!!! Now columns A and B don't give me errors when deleting rows in the first sheet. But sadly columns C, D and E do give the same REF error. I mean I guess we need to do the same for them? Column C gives the date for when someone marks a check, Column D does the same, And E substracts the dates to have a duration, simple. But I don't know how to integrate your formula on them ?.

Formula column C:

Formula column D:

Formula column E:

YOU HAVE BEEN A LIFE SAVER RashGul

 

[Fluff]

Are you using Excel, or Google Sheets?

 

[RicardoRincon]

Are you using Excel, or Google Sheets?

Google sheets

 

[Joe4]

Google sheets

Be sure to clearly mention that in your title and/or initial question posting (while Excel and Google Sheets are similar, they are NOT the same, and have differences).

Also, do not post the question in the "Excel Questions" forum, but rather the "General Discusison & Other Applications" forum (note the description).

I have moved the thread for you and updated the thread title.

 

[Fluff]

In that case, in future please make it clear that it is for Sheets & post in the General Discussion & Other Applications section of the board.

 

[RicardoRincon]

I understand, now I know for next time.