Home

Inventory tracker

%3CLINGO-SUB%20id%3D%22lingo-sub-179546%22%20slang%3D%22en-US%22%3EInventory%20tracker%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-179546%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20wondering%20if%20anyone%20could%20help%20me%20come%20up%20with%20a%20formula%20for%20the%20following.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20a%20spreadsheet%20that%20has%20each%20vehicle%20in%20my%20fleet.%20It%20has%20mileages%2C%20dates%20and%20part%20number.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20it%20possible%20to%20have%20a%20part%20number%20turn%20red%20if%20on%20another%20sheet%20i%20have%20the%20inventory%20count%20to%20zero.%20But%20if%20the%20number%20stays%201%20or%20more%20the%20part%20number%20would%20stay%20black.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eexample%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESheet%20one%3A%20Part%20%23%20%3CFONT%20color%3D%22%23FF0000%22%3E3626%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3ESheet%20two%3A%20Part%20%23%203626%200%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-179546%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359800%22%20slang%3D%22en-US%22%3ERe%3A%20Inventory%20tracker%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359800%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20haytham!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-179564%22%20slang%3D%22en-US%22%3ERe%3A%20Inventory%20tracker%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-179564%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3EThis%20is%20possible%20and%20can%20be%20done%20by%20using%20the%20Conditional%20Formatting.%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3EPlease%20follow%20the%20below%20steps%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20the%20part%20numbers'%20range%20in%20Sheet1%3C%2FLI%3E%0A%3CLI%3EGo%20to%20Home%20%26gt%3B%26gt%3B%20Styles%20%26gt%3B%26gt%3B%20Conditional%20Formatting%20%26gt%3B%26gt%3B%20New%20Rule%20%26gt%3B%26gt%3B%20Use%20a%20formula...%3C%2FLI%3E%0A%3CLI%3EPut%20this%20formula%20in%20the%20formula%20box%2C%20but%20this%20is%20with%20assuming%20that%20the%20range%20in%20which%20the%20inventory%20count%20is%20from%20B2%3AB18%20of%20the%20Sheet2%20and%20the%20part%20numbers%20is%20from%20A2%3AA18.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%3E%3DVLOOKUP(%24A2%2CSheet2!%24A%242%3A%24B%2418%2C2%2C0)%3D0%3C%2FPRE%3E%0A%3CUL%3E%0A%3CLI%3EClick%20Format%20button%20to%20choose%20the%20format%20that%20you%20want%2C%20and%20then%20hit%20OKs.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20find%20the%20attached%20file.%3C%2FP%3E%0A%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
null null
Occasional Visitor

I was wondering if anyone could help me come up with a formula for the following.

 

I have a spreadsheet that has each vehicle in my fleet. It has mileages, dates and part number.

 

Is it possible to have a part number turn red if on another sheet i have the inventory count to zero. But if the number stays 1 or more the part number would stay black.

 

example 

Sheet one: Part # 3626

Sheet two: Part # 3626 0

 

Thanks

2 Replies

Hi,
 
This is possible and can be done by using the Conditional Formatting.
 
Please follow the below steps:

  • Select the part numbers' range in Sheet1
  • Go to Home >> Styles >> Conditional Formatting >> New Rule >> Use a formula...
  • Put this formula in the formula box, but this is with assuming that the range in which the inventory count is from B2:B18 of the Sheet2 and the part numbers is from A2:A18.
=VLOOKUP($A2,Sheet2!$A$2:$B$18,2,0)=0
  • Click Format button to choose the format that you want, and then hit OKs.

 

Please find the attached file.

Hope that helps

thanks haytham!