Results 1 to 4 of 4

Using Vlookup to retrieve cell data and color

This is a discussion on Using Vlookup to retrieve cell data and color within the Excel Questions forums, part of the Question Forums category; I'm using Vlookup to create a duplicate of spreadsheet where I only need to provide certain column information. I am ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Location
    Louisiana
    Posts
    194

    Default Using Vlookup to retrieve cell data and color

    I'm using Vlookup to create a duplicate of spreadsheet where I only need to provide certain column information. I am inputting all project numbers in column A of both sheets and have been doing a Vlookup to pull in the information from the other needed columns. What I can't figure out is how to make it bring in the color of the cell as well. I have to highlight the cells based on a cell containing percentage formula. I want the color that I highlight the row to copy over to the duplicate sheet.

    Can I do this using Vlookup?

  2. #2
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,181

    Default Re: Using Vlookup to retrieve cell data and color

    In a word, no.

    However, there might be other ways to achieve the result you're after, but I think you'll need to describe your data and formatting conditions in a little more detail in order to determine this. Fot instance, Conditional Formatting might be the way forward.

    HTH
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,941

    Default Re: Using Vlookup to retrieve cell data and color

    Weaver is right,
    Not with just a Vlookup formula (or any format for that matter).

    VBA code will be required.

    Post a little more detail about your layout and the vlookup formulas used.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Location
    Louisiana
    Posts
    194

    Default Re: Using Vlookup to retrieve cell data and color

    Sorry, I have unexpectedly been out for a few days.

    My original document lists the following:

    On the "ACTIVE" sheet:

    Columnd "A" is project numbers.

    Columns "B" through "I" are hard keyed descriptives about the project, like location, title, project manager, p.o. number, etc.

    Column "J" is the hard keyed dollar amount of the purchase order.

    Column "K" is a calculated field. It calculates the total expenditures that are hard keyed by me in columns "X" though "BZ". I have dates listed at the top of those columns and I key in the weekly expenditures in each project's row.

    Columns "L" through "N" are calculated values that have no bearing on the formats.

    Column "O" lists the percentage of the p.o. that has been spent. This is how I decide what color to make that row. I change the color manually each week according to the following criteria: Red = 100 % or more. Orange = between 95 & 99%. Blue = between 90 & 94% and yellow means there is no purchase order (column I - p.o. number and J - p.o. $ would be empty). I also use Purple to signify that a project is suspended and Green to mean we are on hold. This must be done manually as there are no calculations that tell me this.

    Column "P" through "W" are more hard keyed project descriptives and comments.

    On the "SEND TO ROY" sheet: (this is the sheet that is doing the vlookup)

    Column "A" is a hard keyed list of all projects that Roy is interested in reviewing.

    All of the rest of the columns contain vlookup functions using the number in "A" to find the corresponding information from the "ACTIVE" sheet. Not all columns from "ACTIVE" are pulled to this sheet. Only information that I need to share with Roy.

    Column "J" does pull the percentage information from "ACTIVES" column "O" that I use for formatting.

    Does this help?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com