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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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