HOW TO: If cell contains specific text that is part of a different list then return specific text

[Hagop Ipdjian]

I'm trying to setup a stock in and out spreadsheet with a master sheet that will have all the values automatically calculated. I need help on the formula that will look for the 'sku' in the other sheets and give me the value right next to it. i.e if i type apples in the master sheet i want the formula to find the text value of apple which in this case should be green.

I need a formula purely on a text and not =sumif that im currently using for my numerical values.

This what im currently using for the numerical side which as mentioned above is working fine =SUMIF('ABC UK'!$J$2:$J$200050,A3,'ABC UK'!$K$2:$K$200050).

Open for suggestions and new formulas. 

[#2]

Hi Hagop Ipdjian,

From above i understood that, you have a 'ABC UK' named sheet with Column J having fruit names & Column K having numbers.

I can't under stand what 'sku' means?

And next, if you apple's respective value VLOOKUP is my choice. But if you have multiple values for apple repeating more than one time in a row SUMIF function (you using currently) is my choice .

You specified 'should be green'. So you want the value with colored, right? Then i will suggest CONDITIONAL FORMATTING. But it need some requirements. On which basis it should be colored in green? (mention it).

If i understood wrongly, let me know more.

[#3]

Hi There,

Firstly thank you for taking the time to read this and reply.

I have attached an example file.

I need help with Columns B and D which I've highlighted in the spreadsheet attached.

I want those to be automatically filled in when i input any information in Column C.

The data for this automation is found in Sheet called 'In'.

I would like sheet 1 to act as my 'statement' if you like of what i have currently in stock.

Once again thank you for your time.

[#4 · Accepted solution]

Hi Hagop Ipdjian,

I've modified your excel file. Just check this what you want.

Or else. Ask more.

[#5]

Hi Logaraj,

Thank you for your assistance you have been really helpful.

I'm looking for a different solution now and was wondering if you can help.

I want to automate sheet 1 even further and would like column C to be completed automatically when a New value has been inputted in "In" Sheet but also make provisions to avoid doubles.

Meaning if it already exists in sheet1 please do not re-input.

Looking forward to your reply

 Thank You

Best Regards,

[#6]

Hi 

So,

1. You want to avoid duplication in "Column C" in "Sheet 1". (You can use Conditional Formatting to avoid duplication or else i should write macro)

2. If any item added in "Column D", you want to add them in "Sheet 1" automatically. Right?

Clarify the above.

[#7]

Hi There,

Yeah I would like it to be added in Sheet 1 just once so if I have the same item reoccurring in Column D "In" sheet more than once to appear just once in Column C in "Sheet 1" 

I prefer to have it as a macro to be honest.

Please let me know if the above is better or if you need me to elaborate further.

The objective is to have "Sheet 1" completely automated and to be used as the current stock sheet.

Thank You