Inventory Excel Challenge

Hello everyone,

I have a challenging question if anyone would be able to answer it, I`d really appreciate it

So I have an Excel sheet with different TABS

for example

I have Sheet 1 - CARS

Sheet 2- TIRES


Now if in Sheet 1 I have a car in 1 cell and the tire in another cell s

e.g.

Description Tire 1 Tire 2 Tire 3 Tire 4

Honda Civic Tire 112 Tire 113 Tire 114 Tire 115


Tire 112, Tire 113, Tire 114 .. are serial numbers of Tires being Pulled from Sheet 2.


Now I want to make it happen in such a way that if some tires are being used by Sheet 1 then

in Sheet 2, I would see the tires as highlighted red, green, yellow or something appended to it saying these tires are available or not available.


Any ideas ? ?

Hello ChemistB,

Thank you for your reply. I just wanted this to be a sample case explaining the situation, but attached is the Excel sheet I am working with

so if you notice in this one,


If you got to Unit # sheet , it is pulling in data from all other sheets

Now if you go to Engine Sheet you will see in Column M , that there are Engine S/N, which is the exact field being called by the Unit sheet , I want to make it such that this column gets highlighted or turns red or something if duplicate values are found.

I have summarized the algorithm as follows :

I want to compare COLUMN M of Sheet Engines to Column E of Sheet Unit #, and if a Duplicate value is found than , Column M (Cell value gets highlighted) .

Thank you very much for your help.

COUNTIF formula can also be used to check for duplicates, but it checks CONDITIONS based on SINGLE RANGE, not multiple RANGES.

What do you suggest ?

ok so this formula is comparing COLUMN E of Unit # sheet, and COLUMN M of Engine sheet but its comparing only with a single cell M2 ( the formula cannot be dragged down to all cells as it is built in conditional formatting ) ,

But I can create this formula in another COLUMN parallel and put this formula there =COUNTIF('Unit #'!$E:$E, M2)>0 ( but I don`t understand what this formula is doing ? is it COUNTING the values in M2 in Comparison with COLUMN E of Engines ? if Yes than it needs to be greater than 2 right ?

This is only Checking condition on cell M2 , and is returning true, as the formula is not extendable in Conditional Formatting to match all cells in COLUMN M.

Hello Billy,

Thank you for your reply, appreciate your efforts.

Yes the helpers are checking condition separately on each column to check if those columns have duplicate values in them,

but the condition we need to check is that if both columns have the same value,

the GIST of the problem is that if an engine is being used by Unit # sheet, then it highlights the cell with a color in the Engines sheet.

Thank you,
Syed