clearing table data and pasting in new- how to keep countif looking at it's row

[indesignkat]

Sorry about the clunkiness of the title, was trying to be descriptive of the problem without guessing a solution.


I have a data table that is 28 columns and around 20,000 rows. Five of those columns represent currency that can be positive or negative. I want a single column that indicates if this row has both positive and negative amounts between those five currency columns. (for example: 0, -1, 5, 0, 0 would return "YES" while 0, 1, 0, 1, 1 would not) I got it to work by using a couple helper columns, one for debits and one for credits. The formula is simply '=COUNTIF($F2:$J2,">"&0)' for debits and the same but with a < for credits. Then my display column shows a text message if both debit and credit counts are above 0.


My problem arises when I update the worksheet. At the start of a new week I delete all the table rows and paste in updated data using paste values. When I do this, those countifs change from $F2:$J2 to $F2:$J20000, which makes the spreadsheet take ages to update until I remember to go in and fix the formulae to be 2s again.


I thought about using row() but I don't know how to get that to work inside a countif range. Using the table names instead of column letters causes the same problem.


While I'm at it, is there a cleaner way to get my desired end result of a column that indicates in one cell whether the currency columns have both positive and negative numbers on that row? Without using VBA?


Thanks,


idk

 

[Debraj]

Hi idk!


I still not able to understand where is the problem..

If the problem is check if any both type (+ve & -ve) of data exist..

then you can go for below Array formula also..

=SUM(ABS($F2:$J2))=SUM($F2:$J2)

Regards,

Deb

 

[Debraj]

Hi idk!


When I am trying to sleeping, suddenly, I realize.. may be you are searching for this..


Reference keeps changing.. try below...

`=COUNTIF(INDIRECT("$F2:$J2"),">"&0)'


Regards,

Deb

 

[indesignkat]

I figured out a way to avoid the problem. Instead of deleting all the data in the table before pasting in new, I leave the top couple rows. That way it doesn't change the reference from F2:J2 to F2:J20000.


Thank you for the suggestion of indirect, Debraj. That's one function I haven't sat down and figured out yet.

 

[indesignkat]

Trying the provided formula (with indirect) it locks in row 2 for the formula on every row. I want it to update as it goes down the table (F2:J2 on row 2, F3:J3 on row 3, etc) but the problem was that when I deleted all the rows and pasted in new data it switched from F2:J2 on row 2 to F2:J20000 on row 2, then F3:J20001 on the next row, etc. Again, leaving a couple rows at the top before I paste in the new data prevents the issue, just curious if there's a better way to write the formula so it's not necessary.


thanks,


idk