Forum Discussion
Solved
Lookup formatting issue when new row added to table
Have made great progress creating a file to track contracts, and other than the file changing the hyperlinks twice (grrrrr....) the only (current) issue I have is with a lookup command and Excel tabl...
- I was able to solve the problem by making the formula =IF(LOOKUP(3,1/($G9:$G10<>""),$G9:$G10)=0,"PAID",IF(OR($G4="",$G4>=TODAY()),"ACTIVE","EXPIRED")) into an array. Now the font formatting doesn't change when adding a new row.
stuckonweb I remembered helping you with this schedule a while ago and found that the issue at the time was related to conditional formatting. Did you change anything in that area?
As far as the conditional formatting, no, there have been no changes and it still works fine. I am changing the fill color of 3 rows based on the word in the status cell by using a reference to a hidden column. (Again, thank you so much for your suggestion!)
In order to determine what word appears in the status cell I needed to use tables to track a contract's balance. The lookup formula looks at the last cell in the Balance column and if 0.00 it puts the word PAID. If not zero it looks at the contract's expiration date and if it is older than today it is EXPIRED, otherwise it is ACTIVE. The words themselves work fine, but when I add a new row to the table the formatting of the word changes to match the formatting of the table.
In order to determine what word appears in the status cell I needed to use tables to track a contract's balance. The lookup formula looks at the last cell in the Balance column and if 0.00 it puts the word PAID. If not zero it looks at the contract's expiration date and if it is older than today it is EXPIRED, otherwise it is ACTIVE. The words themselves work fine, but when I add a new row to the table the formatting of the word changes to match the formatting of the table.