+ Reply to Thread
Results 1 to 7 of 7

Anyone know how to use a cell value in a formula?

  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    SC, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Anyone know how to use a cell value in a formula?

    Very new to excel and have been finding code all over the internet to make my spreadsheet work but having a tough time with this issue.
    I am using the code =MIN(IF(A1:C1000=CL1,ROW(A1:C1000))) ,control, shift ,enter... to match a range and display the cell I am looking for.
    Then I need to take that result and do a conditional format to highlight the text red if the result is a blank cell.
    For example if the result of the above code is 20 then i need to insert it into a conditional format code and tell me if c20 is blank by highlighting the text red.

    I have been researching isblank and indirect but cant seem to get it working properly.

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

    Re: Anyone know how to use a cell value in a formula?

    I posted an answer then realised it might not work (so deleted)

    Is A1:C1000 correct? can CL1 occur in any of the three columns? Could it occur more than once in a single row?
    Audere est facere

  3. #3
    Registered User
    Join Date
    12-27-2012
    Location
    SC, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Anyone know how to use a cell value in a formula?

    Thanks for assisting me Daddlylonglegs.

    Actually the exact code is =MIN(IF(A10:CI1008=CL1,ROW(A10:CI1008))) entered with control shift enter.
    I have that code entered in cell CU6 and it is giving me the answer of 20 which tells me that the text in CL1 matches one of the
    columns in row 20. So if cell CI20 is blank then i would like it to highlight the CU6 cell result of 20 in red with conditional formatting.
    I can type in =isblank(CI20) but every time I put new text into CL1 the result in CU6 changes to 0 for no match or a number between 1 and 1008 if there is a match.
    So I need to figure out how to have CF search the result (20) in column CI and highlight CU6 red if it is blank.
    Last edited by Fratshack; 01-02-2013 at 10:32 AM.

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

    Re: Anyone know how to use a cell value in a formula?

    Try using INDEX, i.e. conditionally format CU6 using the "use a formula to determine which cells to format" option and use this formula

    =INDEX(CI:CI,CU6)<>""

  5. #5
    Registered User
    Join Date
    12-27-2012
    Location
    SC, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Anyone know how to use a cell value in a formula?

    Quote Originally Posted by daddylonglegs View Post
    Try using INDEX, i.e. conditionally format CU6 using the "use a formula to determine which cells to format" option and use this formula

    =INDEX(CI:CI,CU6)<>""

    It wont highlight the result red because I have text entered in other rows in column CI. I am trying to find a way to have CF highlight CU6 red if only CI20 is blank. Then if i enter new text into CL1 and the result changes to 45 in CU6 then tell me CI45 is blank by a red highlight or just stay green (which is default color) if it already has text in it.
    I edited my post above to more accurately describe my dilemma.
    Thanks

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

    Re: Anyone know how to use a cell value in a formula?

    OK I think the formula is essentially correct, it was for CI20 (or whatever cell) to be populated, but if it's when that cell is blank it should work like this:

    =INDEX(CI:CI,CU6)=""

    The INDEX formula is only looking at a single cell - if CU6 = 20 then the INDEX formula will just give you the contents of CI20....and compare that to blank [""]

    If the result in CU6 changes then the conditional formatting will adjust to look at the new cell

  7. #7
    Registered User
    Join Date
    12-27-2012
    Location
    SC, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Anyone know how to use a cell value in a formula?

    GOD BLESS YOU my good man!
    You are a Gentleman and a Scholar!
    I take back all those bad things I said about you being a donkey lover!
    I have been trying to fix this for countless hours!
    Oh Glorious Day!

+ 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