+ Reply to Thread
Results 1 to 15 of 15

Blank Cell Referencing

  1. #1
    Registered User
    Join Date
    03-02-2009
    Location
    UK
    MS-Off Ver
    Excel 2003/07
    Posts
    10

    Blank Cell Referencing

    On the spreadsheet Im creating I am using cell referencing. The user will input data into the blank cells, and cell referencing will put them into another table. Then I add these using an IF(OR(ISBLANK formula.
    However, the empty cells come through as 0's rather than a blank cell. As they're coming through as 0's, they're being calculated into the formula, and this is causing problems in my main table.
    Is there a formula I can use to ensure that the empty cells being copied over do not give off any other value?

    I tried to use a few different IF formulas to only let specific data go through, but it didn't work. I tried to have the blank cells produce a letter so that they wouldn't be calculted in the formula, but letters are also counted as measured values.

    Does that make sense? It's a bit hard to explain... Hopefully you can help me out?

    Taaa!
    Last edited by Wragg1; 03-05-2009 at 11:13 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Blank Cell Referencing

    You can use IF(cell<>0... or IF(cell>0...
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Blank Cell Referencing

    Instead of just using =A1 to reference another cell try

    =IF(A1="","",A1)

    Now the cell with that formula will be blank when A1 is blank. If you use ISBLANK(C2) though that will be FALSE [because "" is not a real blank] so try

    =C2="".......

  4. #4
    Registered User
    Join Date
    03-02-2009
    Location
    UK
    MS-Off Ver
    Excel 2003/07
    Posts
    10

    Re: Blank Cell Referencing

    NBVC - That one returns the 0 in the cell.
    Daddy - That formula does work, but it presents another problem. I'm going to post the spreadsheet in a minute, see if you can work it out.

    For some reason, on my spreadsheet there is a return of "#VALUE!".
    Last edited by Wragg1; 03-03-2009 at 07:54 AM.

  5. #5
    Registered User
    Join Date
    03-02-2009
    Location
    UK
    MS-Off Ver
    Excel 2003/07
    Posts
    10

    Re: Blank Cell Referencing

    Attached the spreadsheet.


    Also, is it possible to return a max result for the "Highest scoring game". I tried using =MAX( but it returned the sum of the goals scored in the game rather than seperating them? Is it possible to return the full result?
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Blank Cell Referencing

    You can use my suggestions and then format those cells not to display 0 values.

    formula in AE45: =IF(AE46<>0,AE46,0)

    formula in AG45: =IF(AG46<>0,AG46,0)

    Then format those cells as Custom with this format: 0;-0;;@

    Please clarify what you mean by "highest scoring game". What if there are more than one game with same high score?

    Note: I fixed up some of your formulas for you in rows 47-52...

    Now if you change the home team in AD14 and/or AJ14, then the stats will change accordingly....
    Attached Files Attached Files
    Last edited by NBVC; 03-03-2009 at 10:15 AM. Reason: Updated formulas in AE45 and AG45.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Blank Cell Referencing

    =IF(AE46<>0, AE46, 0)
    What does that formula do?
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Blank Cell Referencing

    I get it shg, thanks.. I guess it could just as well have been simply =AE46 and format to hide the zero... lol. not thinking straight yet... hopefully not one of those days...

  9. #9
    Registered User
    Join Date
    03-02-2009
    Location
    UK
    MS-Off Ver
    Excel 2003/07
    Posts
    10

    Re: Blank Cell Referencing

    NBVC - I think I love you Cell referencing works now.

    If there was more than one game with the same score, it wouldn't matter - just the highest result shown would be nice if that can happen?


    I dunno why but I can't view the posts made after my previous one in the topic, so I can't view the edits you've made. I'll look again tomorrow

    In the meantime, thanks a lot for your help

    I can see them now.. The formula changes you made are ace, thanks a lot mate! If the MAX( cant be done the way I want I'll just get rid of it.

    Actually, got another problem - still with the cell referencing. I don't want the data input cells (BA9) if they're blank to produce a 0. However, there will be times when a score has a 0 in it and this will need to be copied over.
    Does that make sense?
    Last edited by Wragg1; 03-04-2009 at 08:11 AM.

  10. #10
    Registered User
    Join Date
    03-02-2009
    Location
    UK
    MS-Off Ver
    Excel 2003/07
    Posts
    10

    Re: Blank Cell Referencing

    Okay I've worked out the cell referencing... had to use an IF(ISBLANK(cell ..

    Is there a way to calculate the largest score? Duplicates allowed i.e largest score was 5-0, but it occured twice. It wouldn't matter, just needs to show the largest one.

    If not, it doesn't matter. Would just like to know if it can be done.

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Blank Cell Referencing

    Largest score means 5+0=5?

    what if there is also a 3-2 or 2-3?

  12. #12
    Registered User
    Join Date
    03-02-2009
    Location
    UK
    MS-Off Ver
    Excel 2003/07
    Posts
    10

    Re: Blank Cell Referencing

    Ahh yeah I see what you mean. Hmm.. Tell ya what, it's alright, I'll get rid of it.
    I'll mark this as solved anyway. Thanks a lot for the help mate

  13. #13
    Registered User
    Join Date
    03-02-2009
    Location
    UK
    MS-Off Ver
    Excel 2003/07
    Posts
    10

    Re: Blank Cell Referencing

    Aww damn! Another problem..
    You know the =MAX(IF(AD$16:AD$45<>$AD$14,AE$16:AE$45)) formula you used for me, it was working at first as it had {} enclosing it. But now if I try and put the {} back in, it just returns the formula rather than the actual answer..? How do I get them {} to enclose it and produce the answer?

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Blank Cell Referencing

    Do not manually put in those curly brackets.

    Type in the formula as you normally would, then hold the CTRL and SHIFT keys down and then press ENTER.. the brackets should automatically appear.

  15. #15
    Registered User
    Join Date
    03-02-2009
    Location
    UK
    MS-Off Ver
    Excel 2003/07
    Posts
    10

    Re: Blank Cell Referencing

    Wicked!
    Well yeah that's everything! Rep up for you
    Thanks so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1