Change cell colour based on value from a vlookup?

blemon

New Member
Joined
Apr 16, 2021
Messages
2
Hi all,
I am still very much a beginner with excel but I am trying to create a warehouse location tracking spreadsheet to help me manage my warehouse a bit better (which I know is over my head but I like the challenge).
I don't even know if I am going to properly ask this question but here goes.

I have a worksheet that has all the possible pallet locations in my warehouse (much more but you get the point):
1618596960846.png


I have another sheet that has each location on a row, with it's item and quantity, etc. (we will call this live inventory).
I created a pivot table from the live inventory, because sometimes the locations repeat if there are more than one product in that location. I only need to know there is something in there so I copied the data from the pivot table, leaving the value as "count".

1618597165206.png


My question is, how can I format the pallet position cells so they will show red if the pivot table shows something there?

Again I am a beginner, but I've tried:

conditional formatting, with a vlookup formula that works but it only works on one cell and if I drag the formula, it changes the whole thing to red.
an if formula with a nested vlookup that went nowhere
should I be using an hlookup? I don't think so, because the pivot table is vertical, am I wrong?
do I even need conditional formatting? Should this be a simple IF formula?

and I'm not sure exactly how to word this properly so I've been having a hard time googling stuff. Lots of conditional formatting videos, but I'm lost and/or overthinking it or completely looking the wrong way.

any guidance would be appreciated.

thank you in advance.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

If I understand correctly.

Use this formula for CF (conditional formatting) in your Pallet Location sheet:

Excel Formula:
=COUNTIFS(Inventory!$A$1:$A$1000,A1,Inventory!$B$1:$B$1000,">0")

Where A1 is the 1st cell of your Pallet Location Table
Change Inventory! (sheet name) to match your Live Inventory sheet, change/adjust cell references/ranges as needed.
 

blemon

New Member
Joined
Apr 16, 2021
Messages
2
Hi,

If I understand correctly.

Use this formula for CF (conditional formatting) in your Pallet Location sheet:

Excel Formula:
=COUNTIFS(Inventory!$A$1:$A$1000,A1,Inventory!$B$1:$B$1000,">0")

Where A1 is the 1st cell of your Pallet Location Table
Change Inventory! (sheet name) to match your Live Inventory sheet, change/adjust cell references/ranges as needed.
Hi,
Thank you very much. i will try this.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,930
Messages
5,639,051
Members
417,066
Latest member
rhenman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top