The image I uploaded explains it all. What I am trying to do is write a formula that will take the text in a cell and put it in the target cell. If the cell is empty or blank it will take the text from a different cell.
Your formula works for me, see the sample file I uploaded; you don’t say how “none work” for you. As you have only provided a picture and not an actual spreadsheet showing the issue you are having, it is difficult to diagnose your problem. For a guess, perhaps the cells in C that you are testing for blank are not blank?
If that isn’t the problem, please upload an actual Calc file showing the problem.
goalie.ods (12.1 KB)
Thank you for your quick response. My spreadsheet has 21 sheets and contains full names, etc. so I wasn’t about to upload it. I created the small sample of the problem. Having read your reply I added that formula to the sample file and it did work. However, it does not work in my real spreadsheet.
Now that I know that it can work I am now looking for reasons why it doesn’t in my master sheet. On the master sheet column B is a VLOOKUP formula based on column A.
This spreadsheet that I am designing is meant to be a template that I can re-use season after season. It generates all kinds of statistical reports based on the data. I am trying to automate most of the work and data entry. I enter the schedule at the beginning of the season. I am now attempting to have the goalies automatically entered based on the team they play for. Substitutions happen all the time but I don’t get that info until game day. If I enter the sub-goalie on the master entry page I am overwriting a formula. I need a way for the spreadsheet to choose the sub if there is one, otherwise, choose the team’s regular goalie.
This is not a solution to your problem but you have posted it via Suggest a solution. Others may see a solution has been added and not bother trying to help with your problem. So please delete this and repost as a comment. I will then attempt to help further.
If Column C contains Formulas the =ISBLANK(…) doesnt work, so change the initial Formula in D3 to:
=IF(C3="";B3;C3)
1 Like
21 pages, maybe Data > Calculate > Auto calculate is disabled?
It often happens that copied names bring some spaces with them, there might be a residual space. To modify @karolus solution slightly, =IF(TRIM(C3)="";B3;C3)
IfBlank.ods (13.9 KB)
Auto-calculate is turned on. I tried Karolus’s formula and it did not work, however, the =IF(TRIM(C3)="";B3;C3) did work and solved my problem. I cannot say that I understand it but it does solve the issue. Thank you, everyone, for your assistance. I am very grateful.
It depends on exactly what is in column C, which you haven’t described. Broadly, if C contains anything, even a formula, then ISBLANK will properly return FALSE.
If TRIM provides a solution then there may be one or more spaces in the cell; again without knowing precisely what you have done this is all guessing.
It tells you that the Formulas in Column C returns »white space« … and IMHO … you should change these formulas so that they do NOT return invisible spaces
1 Like
How do I do that? I use vlookup, as well as validity > drop-down lists or ranges, and sometimes direct = cell.
Short term: Leave as it is, and trust TRIM() to remove leading/trailing/stray spaces.
Long term:
- Train your users/yourself to use deletion when you mean “no data” Do not use the space bar to blank a cell. It looks fine, but it is wrong.
- If you import data from other source, make sure empty cells are exported as a “null”, not e.g. as an empty string or some other “blank lookalike” entity.
4 Likes
Thank you. I do have a bad habit of using the space bar to clear a cell. Up until now, it hasn’t been a problem. Going forward I will endeavor to Clear Contents. Thanks again.
Backspace works equally well.