Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    This has to be simple, but I cannot seem to fumble my way through an IF command ... I end up with a #VALUE! error.

    Here is my attempt: =IF('Loan Request'!B9,'Loan Request'!B9,'Loan Request'!B8)

    Both B9 and B8, as well as the cell into which the formula result will be placed, are formatted as Phone Numbers. If B9 has a phone number, then I want that number entered. If B9 is blank, then I want the phone number from B8 entered.

    Thanks, Diane

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    Try this:
    <code>
    =IF('Loan Request'!B9<>"",'Loan Request'!B9,'Loan Request'!B8)
    </code>
    or
    <code>
    =IF(ISBLANK('Loan Request'!B9),'Loan Request'!B8,'Loan Request'!B9)</code>

  3. #3
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    Both formulas worked perfect!

    I thought I could then apply them to another area. Part of my attempt works, but I need help with the other part.

    This is what I want to accomplish: If B57 from Construction Budget - New Build is populated, enter it, otherwise enter B74 from Construction Budget - Rehab.

    What's happening is if B57 is populated, B57 is entered correctly. If B57 is not populated, a zero appears, rather than the amount from B74 in the Construction Budget - Rehab. Here's how I've written it:

    =IF('Construction Budget - New Build'!B57<>"",'Construction Budget - New Build'!B57,'Construction Budget - Rehab'!B74)

    Both B57 and B74 are formatted as Accounting. What am I overlooking?

    Diane

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    The formula looks OK. Check *very* carefully that you've used the correct cell references, and that the cells referred to are populated as intended.

  5. #5
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    I had someone else check the correct spelling for the tab names, making sure there were no extra spaces, and the cell reference.

    We changed the formula to reference other cells (for instance, column A has something in every cell), and the result is still "0." The result cell is formatted as General; when I changed it to Accounting, the result then appears as "$0.00."

    I edited the sheet tab to confirm that the name is correct in the formula; and the formula automatically picked up the change ... so I'm confident that is correct. When I click on the cells referenced from the two budgets, the cell names are the same as shown in my formula.

    Any other ideas?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    Could you attach (a stripped down copy of) the workbook? Remove or alter sensitive data, and omit anything that is not relevant to the problem.

  7. #7
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    Attached is the workbook minus four sheets to which some of the fields are referenced -- obviously.

    In the Home Value Worksheet, my goal is to have only one column (replacing Rehab and New Build). This formula can be used for those items to pull from the correct construction budget.

    In the Home Value Worksheet, cells C9 and D9 currently individually reference the same cells used in the two Constructions Budgets.

    -------------------
    A separate question ... on Construction Budget - Rehab, Cell E2 displays the formula, rather than the result, even though all other formulas will display the result. I know how to hide/unhide formulas for the entire sheet, but I don't understand why this one formula is displayed. I'd like to know why it is displayed and how to make the result display. I, and others here, experience this now and then in other workbooks. Usually, deleting the formula and re-linking resolves the problem, but this particular time, the formula remains.
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    Last question first: the number format of cell E2 on the Rehab sheet has been set to Text, so anything you enter in this cell is seen as literal text.
    To correct this:
    - Select the cell.
    - Set the format to General.
    - Press F2 to edit the formula, then press Enter.
    - You should now see the result of the formula.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    The value of B57 on the New Build sheet is never blank because it contains a sum. If there are no contributing data, the sum is 0. So change the formula to
    <code>
    =IF('Construction Budget - New Build'!B57<>0,'Construction Budget - New Build'!B57,'Construction Budget - Rehab'!B74)</code>

  10. #10
    Lounger
    Join Date
    Jul 2006
    Location
    Colleyville, Texas, USA
    Posts
    45
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF B9 is populated, then insert it,else insert B8 (2003/SP2)

    Perfect! And changing the format to General fixed the other problem -- all of us here thank you for that!

    Thanks for your quick and accurate solutions, Hans.

    Diane

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •