Forum Discussion
Jeffern
Nov 28, 2023Copper Contributor
Use of IF
=IF(B9=0;0;IF(B9='B3'='B2';IF(B9='B4'='B3))) I get error messages If a field='B3', I want to replace it with 'B2', if a field ='B4', I want to replace it with'B3*The fields are either blank(emty) =...
HansVogelaar
Dec 01, 2023MVP
A formula in B9 cannot refer to B9. That would cause a circular reference.
Can you explain what you want to accomplish?
Jeffern
Dec 01, 2023Copper Contributor
For the time being, we use courts 2 and 3, but it used to be courts 3 and four. The change I would like is one in court 3 plays in court 2, one who plays in court 4, goes to court 3. If one cell is empty, then it continues empty. Of course, I know I could change the cells manually, bur then the problem reoccurs next season. I also saw this as an intellectual challenge, if I was able to change it quickly by a formula. I hope this makes sense!
- HansVogelaarDec 05, 2023MVP
Jeffern wrote:
I understand a formula in B9 causes a circular reference, but where could I put it? Could be useful to know for similar use later.See the attached version.
- HansVogelaarDec 05, 2023MVP
Good to hear that. Thanks for the feedback.
- JeffernDec 05, 2023Copper ContributorHans,
I used the Replace dialog. It worked just fine, and it saved me a lot of time. - JeffernDec 05, 2023Copper ContributorI understand a formula in B9 causes a circular reference, but where could I put it? Could be useful to know for similar use later.
- JeffernDec 04, 2023Copper ContributorHans, yes, I believe the Replace option is simpler, I think that I inadvertently sent you the whole workbook, instead of one part of it. No problem!
- HansVogelaarDec 04, 2023MVP
A formula in B9 cannot refer to B9 - that causes a circular reference, as I mentioned that before.
Why don't you use the Replace suggestion that I mentioned in a previous reply:
"You could use the Replace dialog (Ctrl+H).
Select the range.
First, enter B3 in the 'Find what' box and B2 in the 'Replace with' box, then click 'Replace All'.
Next, enter B4 in the 'Find what' box and B3 in the 'Replace with' box, then click 'Replace All'.
Done!"
- JeffernDec 04, 2023Copper ContributorHans, I will try again, see attachment. You mentioned in an earlier post:"return the desired values in another range, not in the same range", is this the problem in this case?
- HansVogelaarDec 03, 2023MVP
Please send the workbook again, with the formula.
- JeffernDec 03, 2023Copper ContributorHans,
What you sent me was fine, but when I enter correct formulas i "B9" I get the error message: The formula contains unknown text. Can you help once more?
I try to read about it but to no avail. - JeffernDec 01, 2023Copper ContributorHans,
Thank you very much for your valuable help. I believe that I prefer formulas that I can later use in solving similar problems, although there seems to be more than one way to skin a cat! - HansVogelaarDec 01, 2023MVP
You could use the Replace dialog (Ctrl+H).
Select the range.
First, enter B3 in the 'Find what' box and B2 in the 'Replace with' box, then click 'Replace All'.
Next, enter B4 in the 'Find what' box and B3 in the 'Replace with' box, then click 'Replace All'.
Done!
If you prefer formulas, you could return the desired values in another range, not in the same range.
See the attached version.
It would also be possible to run a macro to replace the values, but that might be overkill.