Filtering or updating the table screws up this formula for some reason

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Jun 4, 2017

• #2

Are you deleting the data or deleting the rows? (delete the data)

 

Upvote 0

M

mikemck

New Member

Joined

Apr 23, 2016

Messages

26

• Jun 4, 2017

• #3

I'm deleting the rows, except for the very first row.

I delete the rows because there may be fewer or more records in the new month's data.

 

Upvote 0

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Jun 4, 2017

• #4

Deleting rows can screw up formulas, either by changing the range or causing a REF error. Rather, delete the data

 

Upvote 0

M

mikemck

New Member

Joined

Apr 23, 2016

Messages

26

• Jun 4, 2017

• #5

This is a table that gets updated each month, and the other is a table that shrinks throughout the month.

Deleting the rows themselves means I don't end up with extraneous rows with no data in them.

The table expands if there is more data, so what I need is a way to automatically shrink the table if there is less data.

 

Upvote 0

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Jun 4, 2017

• #6

I have suggested how to do this to avoid problems. Excel has over 1 million rows, a few "extraneous" rows here and there will not make much of a difference.

If you are concerned that you will have formulas showing stuff for empty rows, adjust the formulas so they only show answers if there is data to work on

 

Upvote 0

M

mikemck

New Member

Joined

Apr 23, 2016

Messages

26

• Jun 4, 2017

• #7

Okay, I got it. I wasn't clear myself on what I wanted to do until you suggested that last.

Here's what I have now: =IF(OR(ISBLANK(A2),ISBLANK(E2)), "",IF(B2="","",IF(ISNA('Print ref'!B2),NA(),'Print ref'!B2-TODAY()&" Days remaining")))

Thank you.

 

Upvote 0

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Jun 4, 2017

• #8

Cool, nice job

maybe an adjustment to this?
=IF(OR(A2="",E2="",B2=""),"",IF(ISNA('Print ref'!B2),NA(),'Print ref'!B2-TODAY()&" Days remaining")

 

Upvote 0

M

mikemck

New Member

Joined

Apr 23, 2016

Messages

26

• Jun 4, 2017

• #9

Aha. Even better. This is excellent stuff.

Updated all of the formulas in these sheets along the same lines.

Just learned about dbl clicking the fill handle to update, made it all much easier as well.

 

Upvote 0

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Jun 4, 2017

• #10

Great. Out of curiosity, what could be in 'Print ref'!B2 that would require testing for an error?

 

Upvote 0