0

I know the caption is little confusing one. as me too struggling to point you out what I exactly need, in the limitation of my English am trying to express what I want. I have a sheet in which there are three tabs

  1. Stock (where all the entries must be there)
  2. Input (Where we input the names it must go to OUTPUT automatically)
  3. Output (must display only the names which are not in stock)

instruction

Assume that Stock tab contains several names, and when the next time we paste names into INPUT tab the names which already in stock tab must go red, and the names which are not red must go to OUTPUT tab.

Hope its clear, still in the shared sheet there is 3 columns as eg.

https://docs.google.com/spreadsheets/d/1Zr0SyktYteQoOrRbWiNFqG_HWznone4Le32olFTZGv8/edit#gid=0

NightEye
  • 10,634
  • 2
  • 5
  • 24
ikercasyaz
  • 11
  • 2

1 Answers1

0

Solution:

The red marks can be done with conditional formatting with a custom formula. You can set it by selecting the needed range and selecting Format -> Conditional Formatting

=VLOOKUP(INDIRECT("Input!D6:D"),INDIRECT("Stock!D6:E"),1,FALSE)<>""

enter image description here

And we can use this VLOOKUP as basis for the second formula in the Output sheet:

=ARRAYFORMULA(QUERY(IF(IFERROR(VLOOKUP(Input!D6:D,Stock!D6:E,1,FALSE))="",Input!D6:E,""),"select * where Col1 <> ''",0))

enter image description here

References:

Conditional Formatting from Another Sheet

VLOOKUP()

QUERY()

CMB
  • 4,950
  • 1
  • 4
  • 16
  • I plugged the formulas / conditional formats on your sheet. Edited my answer as well to show how to set conditional formatting. – CMB Mar 15 '21 at 17:07
  • I mean whatever we enter in input sheet must come in stock also if ABC111 comes twice then it must be there in STOCK tab. but we need to display ABC111 one time for that we can create one more tab and take unique of stock and hide the previous stock tab – ikercasyaz Mar 15 '21 at 17:12