Deleting Rows Messes Up Formula

[Johnny0811]

Good Evening,

Please Login or Register  to view this content.

I have the following formula which list the row numbers of the number entered in C1 as well as matching the criteria. The formula works perfectly.

However, the only problem is that there will be times I will be deleting entire rows from sheet2 which ruins my formula for some reason (as in the ranges changes e.g from A1:A100 to A1:A97). Is there any way to prevent this? I have read there I must use the index function, but i didn't know the correct syntax.



Can anyone help?

JJ

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Johnny0811]

On sheet 2 it has the data. On sheet 1 in has the number u enter to search in cell C1 and in column D is where the formula should be and as you can see it list all the
row numbers. In sheet 2, if you delete the row that is highlighted yellow, you will see it messes up the formula in sheet 1.
Hope that makes sense.

[Glenn Kennedy]

Try this:

=IFERROR(SMALL(IF((Sheet2!$A$1:$A$100=C$1)*(Sheet2!B$1:B$100={"FW","DU","PU","SRP"}),ROW(Sheet2!$A$1:$A$100)),ROW(A1)),"")

array entered.

[Johnny0811]

Great, thank you so much!

[JohnTopley]

I would make columns A and B in sheet2 dynamic named ranges which expand/contract as rows are added/deleted

For A

Name: COL_A (Choose your name)

set Refers to: =OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A$1:$A$1000),1)

For B

Name: COL_B

=OFFSET(Sheet2!$B$1,,,COUNTIF(Sheet2!$B$1:$B$1000,"?*"),1)

in D2=1 of Sheet1

=IFERROR(SMALL(IF((COL_A=C$1)*(COL_B={"FW","DU","PU","SRP"}),ROW(COL_A)),ROW(A1)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Glenn Kennedy]

You're welcome...