+ Reply to Thread
Results 1 to 15 of 15

Vlookup across multiple sheets, return value with conditional formatting

  1. #1
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    2014
    Posts
    9

    Question Vlookup across multiple sheets, return value with conditional formatting

    Hello! I will try to explain this as clearly as possible, bear with me...

    I am currently trying to build a heat map for work, showing bin locations and their statuses as to how empty they are. I built a macro to pull that information from our intranet, the bin type and number of items in the bin. (that was the easy part). Each bin type has a different limit of items that can be placed before reaching full capacity. Here is a screenshot of how that is depicted, for each tab/bin type. The header in row 1 is conditionally formatted - 1 being available capacity (1 item presently in given bin), and 10 being no available capacity (bin has reached it's limit and is full).



    I have another tab that lists all the bins in the building (Master List). What I need is each bin (in column A of Master List) to be looked up across the 7 different bin type sheets (each bin will only show up in 1 sheet at a given time), and return the color of where it falls under (row A in Sheets 1 to 7). The color will be returned next to the bin in the B column titled "Status".



    Is this possible? My boss and I have tried and tried with no luck.

    Thanks in advance!
    Last edited by TheYoungDrea; 08-01-2017 at 10:50 AM. Reason: Sensitive information - see sample workbook instead

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Vlookup across multiple sheets, return value with conditional formatting - Halp!

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    2014
    Posts
    9

    Re: Vlookup across multiple sheets, return value with conditional formatting - Halp!

    Sorry about that! I'm new. Will definitely do that. Give me a few minutes...

  4. #4
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    2014
    Posts
    9

    Re: Vlookup across multiple sheets, return value with conditional formatting - Halp!

    Okay, here is a quick sample book. You will see a few of the sheets with the conditionally formatted header rows, the master list before, and the master list after (desired automated results)
    Thank you
    Attached Files Attached Files

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Vlookup across multiple sheets, return value with conditional formatting

    It would probably be easier to build it from the source data, rather than the individually formatted sheets. You say you retrieve the individual bin data from somewhere - in what format do you get / process that data?

    It looks like Sheets 1 - 3 are grouping Bins by Capacity, and the Status is based on Utilisation. So you really want a master list of all bins, with fields Bin, Capacity and Fill Level. Then you could easily replicate your existing reports, and your desired Master report.

    Can you get the data in that format?

  6. #6
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    2014
    Posts
    9

    Re: Vlookup across multiple sheets, return value with conditional formatting

    It's a direct web excel link. I have a macro that opens the file, copies/pastes the file to the sheet, then closes the file. Unfortunately, there is no master list/utilization for all bins, they are separated out by bin type and number of items in bin (as there are over 34,000 bins) as their individual web excel link.

  7. #7
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Vlookup across multiple sheets, return value with conditional formatting

    We can work with that. What's the structure of the source data, and let's see your existing code.

    Edit: and which version of Excel are you using? Your profile says 2014, but there is no Excel 2014...

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,385

    Re: Vlookup across multiple sheets, return value with conditional formatting

    Try ...

    In Column C of "Master List After"

    =INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$J$200"),$A2)>0),0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    "SheetlList" is named range of your sheets

    The above finds which sheet a given bin is on

    in column B

    =IFERROR(INDEX(INDIRECT("'"&C2&"'!A1:J1"),,SUMPRODUCT((INDIRECT("'"&C2&"'!A2:J100")=A2)*(COLUMN($A$1:$J$1)))),"")

    returns value fro row 1 of sheet (1 to 10)

    Apply your CF to column B

    WARNING: Use of INDIRECT will have a real impact on performance.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    2014
    Posts
    9

    Re: Vlookup across multiple sheets, return value with conditional formatting

    It's Excel 2013! Oops.

    The structure of the source data is just a simple list of bins, that run down the A column. No headers or other data. I have a code for each bin type/number of possible items in bin. The code pulls from this page, this is how it is laid out:

    Source Data example.PNG

    Where you click the number of bins, it downloads an excel file of the list of specific bins. No headers, no additional data, just a simple list going down the A column of the bins that meet the criteria. I have a code written for each link, here is the structure of the code:

    Sub OpenWebXLS()
    ' *************************************************
    ' Define Workbook and Worksheet Variables
    ' *************************************************
    Dim wkbMyWorkbook As Workbook
    Dim wkbWebWorkbook As Workbook
    Dim wksWebWorkSheet As Worksheet

    Set wkbMyWorkbook = ActiveWorkbook

    ' *************************************************
    ' Open The Web Workbook
    ' *************************************************
    Workbooks.Open ("https:www.blahblahblahblah.not_a_real_website.com")

    ' *************************************************
    ' Set the Web Workbook and Worksheet Variables
    ' *************************************************
    Set wkbWebWorkbook = ActiveWorkbook
    Set wksWebWorkSheet = ActiveSheet

    ' *************************************************
    ' Copy The Web Worksheet To My Workbook and Rename
    ' *************************************************
    Workbooks("GetExcel.html").Worksheets("getExcel").Range("A1:A10000").Copy _
    Workbooks("Capacity.xlsm").Worksheets("Sheet 1").Range("A2")

    ' *************************************************
    ' Close the Web Workbook
    ' *************************************************
    wkbMyWorkbook.Activate
    wkbWebWorkbook.Close

  10. #10
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Vlookup across multiple sheets, return value with conditional formatting

    That's much nicer to work with. Which fields map to Bin Name, Capacity and Fill Level?

  11. #11
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Vlookup across multiple sheets, return value with conditional formatting

    So you understand where I'm going with this - look at the attachment. It generates a conditionally formatted pivot table to summarise results from all the source tables (which I've aggregated with Power Query.)

    But it would make a lot more sense to structure your source data ONCE, as your retrieve it, in a format which allows your existing reporting, and your required master list, and that's why I'm trying to understand where and how you get to the data in the first place.

    If we can write a query / some code that transforms all the data into one decent structure, then all your reporting will be easier, and more efficient.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    2014
    Posts
    9

    Re: Vlookup across multiple sheets, return value with conditional formatting

    Thank you Olly and JohnTopley for the help! I really appreciate it. Going to apply these principles and *knock on wood* hopefully they pull through.
    The problem is, this data is on my companys intranet, which isn't publicly available. I really like the idea of structuring the source data once in a format which allows existing reporting (that pivot is pretty cool). The master list will always stay the same but the bin status will change from day to day (as expected). I am still learning, so queries are my weak point, sadly.

    Ultimately, I am designing a map of the bin locations in the building, and having the status color update on the map, so employees know where to directly go to replenish product and not have to worry about the bins being over capacity. The visual map will really be helpful to those who aren't as familiar with the building.
    Last edited by TheYoungDrea; 08-01-2017 at 12:14 PM.

  13. #13
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    2014
    Posts
    9

    Re: Vlookup across multiple sheets, return value with conditional formatting

    Okay, I tried to plug in the formulas into the Master List, and kept getting an error. Upon diving into it, I see that the fact that on the sample the maximum number of items in each bin type is set to 10, which is throwing off my real file, as each bin type has a different item limit. (See attached, I copied it to realistically reflect my actual file - had to scale down on some of the bin lists, as the actual file exceeded limit size). Maybe this could help?
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,385

    Re: Vlookup across multiple sheets, return value with conditional formatting

    I modified the formula but only found 21 matches in Master list!

    Using FIND feature of Excel appeared to validate this.

    The other issue is variable number of bin types so I don't know how it is possible to set the CF according to the sheet where bin resides.

  15. #15
    Registered User
    Join Date
    07-28-2017
    Location
    USA
    MS-Off Ver
    2014
    Posts
    9

    Re: Vlookup across multiple sheets, return value with conditional formatting

    Yeah, with over 34,000 bins in the building, it caused the file to exceed the upload limit, so I had to omit quite a few bins in order to reduce the size for upload. That's why there were only 21 matches

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional Formatting Across Multiple Sheets
    By Flat Banana in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2016, 01:15 PM
  2. Conditional Formatting - 2 sheets using Vlookup or Index/Match or SumProduct?
    By phrankndonna in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2016, 01:38 PM
  3. Replies: 1
    Last Post: 03-13-2015, 09:39 AM
  4. Vlookup function to return multiple values from multiple sheets
    By bsamant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2013, 11:31 AM
  5. VLOOKUP to look through multiple sheets and return the sheet names
    By ackimbrough in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2011, 03:55 PM
  6. Excel 2007 : Conditional formatting - multiple sheets
    By Spike0907 in forum Excel General
    Replies: 4
    Last Post: 01-31-2011, 12:11 PM
  7. Conditional formatting multiple sheets?
    By preddy1110 in forum Excel General
    Replies: 1
    Last Post: 11-23-2010, 09:09 AM

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