Delete certain rows but keep formulas intact

[Linh Le]

Hello,

First time user here. Hoping the community can help me as i've exhausted all resources and have been trying to figure it out for hours. Here's the sticky situation:

- On one sheet is a values only Master List tab that could run between 2000 - 4000 items
- The other is a calculable sheet that has the following formula filled in to, say row 3000, =IF('Master Fund List'!B1811<>"",'Master Fund List'!B1811,"") to capture the running items. The problem is if the Master List only have 1500 items then the first 1500 items would get captured based on the formula, but then from row 1501 to 3000 it would say "#REF!" which is correct because there's no information on the main tab (see screenshot)
- Question: is there a way for me to stop the auto calculations from pulling in data below the last relevant item or "remove" the rows/formulas w/o either hiding said rows or delete out the formulas entirely?

Thank you so much!
Le

 

[kevin9999]

You have 365 so the Filter function should be available to you. I don't know what column you are putting your formulas in on the second sheet, but assuming it's column A, try this:

Book1
	A	B
1	HDR1	HDR2
2	x	2 data
3	x	3 data
4	x	4 data
5	x	5 data
6	x	6 data
7	x	7 data
8	x	8 data
9	x	9 data
10	x	10 data
11	x	11 data
12	x	12 data
13	x	13 data
14	x	14 data
15	x	15 data
16	x	16 data
17	x	17 data
18	x	18 data
19	x	19 data
20	x	20 data
Master Fund List

Book1
	A
1	HDR2
2	2 data
3	3 data
4	4 data
5	5 data
6	6 data
7	7 data
8	8 data
9	9 data
10	10 data
11	11 data
12	12 data
13	13 data
14	14 data
15	15 data
16	16 data
17	17 data
18	18 data
19	19 data
20	20 data
Calcs

Cell Formulas
Range	Formula
A1:A1000	A1	=FILTER('Master Fund List'!B:B,'Master Fund List'!B:B<>"","")
Dynamic array formulas.

 

[Linh Le]

Hi Kevin,

thank you for this. I tried it but got a "spill" error. I'm probably doing it incorrectly. Maybe my question isn't clear either. Please lmk if there's an alternative solution.

Thanks again,
Le

 

[kevin9999]

Hi Kevin,

thank you for this. I tried it but got a "spill" error. I'm probably doing it incorrectly. Maybe my question isn't clear either. Please lmk if there's an alternative solution.

Thanks again,
Le

You only put the formula in one cell on the Calcs sheet, in A1. Plus you can't have anything else below that in column A in the Calcs sheet.

 

[Linh Le]

solved it. thank you!

 

[Linh Le]

Hi,

One follow up question: could you suggest how i should nest the filter formula with the following: =IF(ISBLANK(VLOOKUP(E5,'Master Fund List'!E:Z,6,FALSE)),"",-VLOOKUP(E5,'Master Fund List'!E:Z,6,FALSE))

thank you.

 

[kevin9999]

Hi,

One follow up question: could you suggest how i should nest the filter formula with the following: =IF(ISBLANK(VLOOKUP(E5,'Master Fund List'!E:Z,6,FALSE)),"",-VLOOKUP(E5,'Master Fund List'!E:Z,6,FALSE))

thank you.

I believe this is a different question to your original post and as such, please start a new thread.

 

[Linh Le]

Will do. Thanks for your patience as I just discovered this forum this week.

 

[kevin9999]

solved it. thank you!

Did post #4 solve your issue, or did you find a different solution yourself? If you did, you can post it and mark it as a solution to assist others in future.