Vlookup output as a cell argument

  • I have a sub called GETCOLOR which retrieves the index color of a cell.


    If I type it directly as =GETCOLOR (AJ6,”index”) it yields the background color of cell AJ6, which is 14 (green)


    However, I need to get the background color of that same cell, but expressed as the result of a VLOOKUP function. AJ6 is the output of the function =VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE) and I need to use that as the cell (first) argument for the GETCOLOR sub.


    If I use it this way =GETCOLOR(VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE),“index”) I get a #VALUE! Error


    “Excel Formulas and Functions for Dummies” states that for the function =CELL(“contents”,A1), if the argument cell contains a formula, the function returns the result of the formula and not the formula itself. Since the output of the VLOOKUP is AJ6, I tried the following:


    =GETCOLOR(CELL(“contents”,(VLOOKUP($D$5,$CV$2:$CW$314,2,FALSE)),“index”), but it says I’ve entered too many arguments for this function.


    I suspect that it has something to do with the placement of parentheses, but none of the placement combinations I've tried have worked. I'm sure it's something obvious and simple, but I can't seem to solve it - can anyone point out my error(s)?

  • Hello,


    To make everything much easier for everyone ... just attach a sample file to illustrate your expected result ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Dear Carim,


    Per you suggestion, I have attached a pared-down version of the file (most extraneous details removed). Also, here are some relevant notes (please pardon the cap's - not shouting, just copied the upper case notes from the spreadsheet, where they are also included). I greatly appreciate any assistance that you can provide.


    At cell C4, I have listed the formulas used and steps taken to build the code for Cell D7 and the results.


    MY QUESTION FOR THIS FORUM RELATES TO THE PROPER CODE TO RETRIEVE THE INDEX COLOR OF THE CELL ASSOCIATED WITH THE CONDO NUMBER ENTERED INTO CELL 'D5' ('43,' FOR EXAMPLE - WHICH IS THE BLUE CELL IN BULDING B - OR OTHER CONDO NUMBER AS QUERIED BY THE USER). ONCE IT IS WORKING, THIS CODE WILL RESIDE IN CELL 'D7.'


    ULTIMATELY (WHEN DEBUGGED) THE CODE FOR 'D7' WILL UTILIZE THE UDF "GetColor" WITH THE INDEX COLOR ASSOCIATED WITH THE CELL ADDRESS FOR THE SYMBOL REPRESENTING THE CONDO NUMBER ENTERED INTO CELL 'D5' AS THE Rng ARGUMENT AND "index" AS THE ColorFormat ARGUMENT.


    THE CELLS IN ROW 13 ARE ONLY COLORED TEMPORARILY FOR PURPOSES OF TESTING/VERIFYING CORRECT COUNTS.


    FOR PUPOSES OF A SIMPLIFIED .xlsm FILE (attached) FOR THIS POST, THE VLOOKUP TABLE (CV2:CW44) IN ONLY PARTIALLY POPULATED. THE FIRST COLUMN IS THE CONDO NUMBER, THE SECOND COLUMN IS THE CELL ADDRESS FOR THE SYMBOL REPRESENTING THAT CONDO.


    THE 'INPUT-QUERY INTERFACE' IS CURRENTLY NON-FUNCTIONAL (FUTURE IMPLEMENTATION).


    COMMENT CAPTIONS (I.E. INDIVIDUAL 1-, 2- OR 3-DIGIT CONDO UNIT NUMBERS) ARE NOT YET FUNCTIONAL (FUTURE IMPLEMENTATION).

  • Thanks for your test file :)


    For your Step 3, you could test following formula


    Code
    1. =CELL("CONTENTS",INDIRECT(VLOOKUP($D$5,$CV$2:$CW$44,2,FALSE)))


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • ... and for your Step 4


    you could also test following formula, using your UDF :


    Code
    1. =GetColor(INDIRECT(VLOOKUP($D$5,$CV$2:$CW$44,2,FALSE)),"index")


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested both formulas ....feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Very happy to hear you could fix your problem :)


    Thanks a lot for your Thanks ... AND for the Likes :thumbup:


    Also, wanted to congratulate you for a very smart spreadsheet ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Continuing with my VBA project, I’m trying to build a sub that includes the following statement. This statement successfully captures the cell address of the symbol for the cell representing Condo “43” (one of 309 condo numbers that can be entered in cell E5). I have verified this by monitoring that line with a “watch” (see attachment).


    CellToColor = WorksheetFunction.VLookup(Sheet1.Cells(5, 5), Sheet1.Range("CW2:CX310"), 2, False)


    For the sub's next step, I need to capture the background color for the cell corresponding to the variable output (e.g. “AK6”) of the above statement.


    Q1. What is the recommended formula for the “ColorOfCell” line below?


    ColorOfCell = _____________________________________________________________________________________________


    Q2. What variable type should be assigned to “ColorOfCell”?


    Then, once the index color of the looked-up cell is captured, the sub needs to assign a new index color (a constant e.g. “3”) – which will be different than the output from ColorOfCell – to the variable output (e.g. cell “AK6”) of CellToColor. I have no clue where even to start.


    Q3. How should the statement for assigning the index color ‘3’ to the output of CellToColor be structured?


    ____________________________________________________________________________________________________


    I've tried various methods for each of these lines of code, based on examples I've found on the web. Problem is, those examples do not have the same parameters as my situation, and I keep getting cryptic VBA errors, when I run the sub. As before, any help is greatly appreciated.

  • Hello,


    Could you please post the VBA code which needs to be fixed ....;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)