Vlookup across multiple sheets, return value with conditional formatting

**TheYoungDrea** · 08-01-2017, 09:53 AM

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!

**Olly** · 08-01-2017, 09:59 AM

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.

**TheYoungDrea** · 08-01-2017, 10:18 AM

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

**TheYoungDrea** · 08-01-2017, 10:36 AM

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

**Olly** · 08-01-2017, 10:58 AM

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?

**TheYoungDrea** · 08-01-2017, 11:13 AM

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.

**Olly** · 08-01-2017, 11:15 AM

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...

**JohnTopley** · 08-01-2017, 11:23 AM

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.

**TheYoungDrea** · 08-01-2017, 11:32 AM

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

**Olly** · 08-01-2017, 11:37 AM

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

**Olly** · 08-01-2017, 11:58 AM

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.

**TheYoungDrea** · 08-01-2017, 12:10 PM

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.

**TheYoungDrea** · 08-01-2017, 01:25 PM

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?

**JohnTopley** · 08-01-2017, 03:05 PM

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.

**TheYoungDrea** · 08-02-2017, 07:49 AM

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

Vlookup across multiple sheets, return value with conditional formatting

LinkBack

Thread Tools

Rate This Thread

Display

Vlookup across multiple sheets, return value with conditional formatting

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Conditional Formatting Across Multiple Sheets

Conditional Formatting - 2 sheets using Vlookup or Index/Match or SumProduct?

VLookup Across Multiple Sheets - How do you return a Minimum Value or Sheet Name?

Vlookup function to return multiple values from multiple sheets

VLOOKUP to look through multiple sheets and return the sheet names

Excel 2007 : Conditional formatting - multiple sheets

Conditional formatting multiple sheets?

Bookmarks

Bookmarks

Posting Permissions