using conditional format and seeing the wrong text in the formula bar

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows
Hi
I use conditional format for the following:
I put “Pending” in a cell (F) and when the date changes to today or after, it changes to “payment”

It worked perfectly for at least a year until Jan 1, 2021

Now conditional format changes the text BUT any cell that references it …doesn’t............including the formula bar...and If statfements
I shortened the spreedsheet to make it easier to understand

mike
sorry, I don't know how th make the row width's less after I used the L2BB
sample problem.xlsx
ABCDEFGHIJKLMNOPQR
1this is a sample of my problem
2It's supposed to >>>I put in "pending" and when the date is today and after, it changes to "payment" >>> Row N keeps a runninh total
3it has worked trouble free all last year and started not working on Jan 1,2010
4
5dates formatted as mm/dd/yythis row has conditional format=IF(AND(B7="abc",F7="charge"),+C7,IF(AND(B7="abc",F7="payment"),-C7,IF(AND(B7="abc",F7="credit"),-C7,"")))
6
712/12/20abc100.00statement in fullpayment=AND(a7<=today(),F7="pending")….format…custom.. ;;;"payment"-100
812/13/20abc7.99bookcharge7.99
912/21/20abc100.00charge100
1012/24/20abc3.99bookchargeput in "pending" and the format changes it to "payment"3.99
1112/28/21abc19.03charge19.03
1212/30/20abc2.99bookcharge2.99
1301/01/21abc156.12in fullpaymentformula bar shows pending If statement returns nothing
1401/03/21abc47.44charge47.44
1501/04/21abc100.00in fullpaymentformula bar shows pending If statement returns nothing
1601/21/21abc100.00in fullpaymentformula bar shows pending If statement returns nothing
17after I formatted F7, I used the "format painter" and dragged it down to F16
18format changes this to "payment" even though the date is in the future
19
20It worked good from 12/20/20 and before
21I tried changing the date format which didn't helf
22computer date and time are correct
23
24I shortened the spreesheet to show that A7:N7 works fine
25
26the rest does not even with the same formaula
27
Sheet1
Cell Formulas
RangeFormula
L7:L16L7=IF(AND(B7="abc",E7="charge"),+C7,IF(AND(B7="abc",E7="payment"),-C7,IF(AND(B7="abc",E7="credit"),-C7,"")))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E8:E16Expression=AND(XFB8<=TODAY(),E8="pending")textNO
E7Expression=AND(XFB7<=TODAY(),E7="pending")textNO
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
Now conditional format changes the text BUT any cell that references it …doesn’t............including the formula bar...and If statfements
That is because formatting (condition or otherwise) does not change the underlying value in the cell, it only changes the appearance of the cell. It has always worked like that.

It is just like cell A1 below. I have formatted as currency with 2 decimal places so in that cell you see the appearance of a "$" sign and a decimal point followed by two zeros, but actually in the cell is still just 100 as you see in the formula bar.

1610101149010.png
 

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows
Hi Peter_SSs
Happy New Year
That doesn't explain why E7,which is formatted the same shows "payment" in the formula bar and the formula in L7 works.
It also doesn't explain why the format worked before.


mike
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
That doesn't explain why E7,which is formatted the same shows "payment" in the formula bar ...
Perhaps you haven't uploaded the actual information in your worksheet then?
I copied your XL2BB sheet to mine and E7 does contain "payment" so no surprise to me that "payment" shows in the formula bar.
If E7 actually contains something else, then perhaps you could send us that XL2BB sheet so that we can try to understand what you are saying.
 

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

...E7 does contain "payment"...
BUT E7 originally had "pending" till conditional format changed it to "payment". (that's the point I was trying to make)
For at least a year, I had no problem, them at the beginning of this year it started.
L7 has an if statement that looks for payment and pending
It recognizes it in E7 but not the others.
Every thing in E:E16 is conditionally formatted to change pending to payment if the date is today or previous
It worked fine till January
I noticed it because the running total (E) was wrong
If I clear the format and type in payment, the if statement works
It seems that somehow the spread sheet stopped recognized the format change when before it did.
I changed the format of the date which didn't help
I put an if statement in a blank cell that refereed to E7. (.which was changed using conditional format) .
=if(E7="payment",1,2)
it returned 1
I did it for E13
it returned 2
I typed in "payment in E13 and it returned 1
I cleared conditional format for E13 and retyped it. (originall, I use the paint bruch and copied the format from E7),
didn't work

sorry to give you a long and boring review of what i did !!!!!!!!!!!!!!!!
Till i fix it, i will just change pending to payment myself
If I get a chance this week end, I'll start a new workbook and type in the info instead of copy and paste and see what happens. It'll be Monday before i get back here

I DO appreciate your help. you've been a great help it the paste, too


mike
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
Sorry, but Conditional Format has never actually changed the value in a cell. That is why it is called Conditional Format, it just changes the appearance, the underlying value in the cell remains the same.
I don't know what you had before, but I can assure you that you did not have Conditional Formatting changing the actual value in a cell.

BTW, what are the references to column XFB in the CF formulas in post 1?
 

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows
Hi Pete
That reference was an OPPS. it was supposed to refer to B
I changed it in the sample and it didn't help
I opened a new workbook and type in the info and as you said. it only changed the appearance of the cell.
It is weird because it seem to work before this year
What I going to do is rerwrite the sheet. glad its the beginning of the year. anything before is for reference only
What I do is enter charges and credits for my credit cards on sheet1, then on sheet2,sheet3..etc. I use Advance filter to keep each card separate
the formulas and if statements refer to each card.
the running totals are on sheet1
maybe i'll have the totals on each sheet and just have the current ones on sheet1

Thanks for your time and input

mike
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top