Displaying name of column if a cell contains specific text

[thomasb82]

Hey guys, bare with me as this is a bit complicated to explain. I basically have a document that contains a long list of subdepartments in one sheet and a long list of people info in rows in another sheet where the subdeparment is displayed to every person. My goal is to add a column beside the subdeparment cell that displays which head deparment it belongs to. I have a separate sheet that contains all the subcomparments in columns, with the name of the head deparment, like this(with head department in bold and subdepartments below:

Engineering HR IT
mechanic staffing production
gears recruting smartphones
etc...

then i have another sheet that contains rows of people info that only shows their subdepartment(recruiting in this instance):

Daffy Duck streetadress jobtitle recruiting

now i want the cell next to "recruting" to display"HR" if it contains any of the text in the HR column.

So basically i want the code to search every cell in the subdepartment sheet and shows the corresponding main department (HR, Enigneering etc) in the people sheet.

Am i making sense? anyone got an idea? been wrapping my head around this for a while now, would appreciate some help...

 

[My Aswer Is This]

This could be a easy answer if you were to provide some specific details.
You have given no specific details.

You mention two or more sheets but give no sheet names.
And you mention several different columns but give no column numbers

You need to provide details like:

I have data on sheet named "Master" and in column(2) I have...

And I want to do this to sheet named "Next" when a Vba script sees this in column "5"

It' impossible with such limited details for me to help you.

Go to the store and get me some food is unspecific.
Go to "Publics" and get me "5 Red Apples" is specific.

 

[Peter_SSs]

See if you can adapt this (but you will need to be consistent with your spelling - you have "recruiting" in one spot and "recruting" in another )
This suggestion will also only work if there are no duplicate sub-department names.
Post back with details if you have any further difficulties.

Excel 2016 (Windows) 32 bit
	A	B	C
1	Engineering	HR	IT
2	mechanic	staffing	production
3	gears	recruiting	smartphones
Dept List

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
2	Daffy Duck	streetadress	jobtitle	recruiting	HR
Staff

Cell Formulas
Range	Formula
E2	=INDEX('Dept List'!$A$1:$C$1,SUMPRODUCT(('Dept List'!$A$1:$C$3=D2)*(COLUMN('Dept List'!$A$1:$C$3)-COLUMN('Dept List'!$A$1)+1)))

 

[thomasb82]

Hey guys, thanks for the fast respones and yeah, i guess i didnt provide much in the way of details sorry about that. heres a link to a review document that shows what im trying to do with some comments, maybe it will make it clearer? i will try peters formula now as well.

https://drive.google.com/file/d/0B9JnZa8s8xnTMFVSRkdtbUxrZkVRNHJFVzNmejk4dnpFbmdJ/view?usp=drivesdk

again really appreciate the help!

 

[Peter_SSs]

i will try peters formula now as well.

Provided you adjust the sheet names and ranges to match your setup, I think it should work for you.

 

[thomasb82]

Hey peter, thanks for the input. Having i hard time trying to adjust this to my document. would it be too much to ask if you had a look at the file above and adjusted the formula to fit that one? then it would be eaasier for me to grasp the formula and context. would be much appreiciated.

thanks, thomas

 

[Peter_SSs]

In C2 of Sheet1
=INDEX(Sheet2!$A$1:$C$1,SUMPRODUCT((Sheet2!$A$1:$C$20=B2)*(COLUMN(Sheet2!$A$1:$C$20)-COLUMN(Sheet2!$A$1)+1)))

Then copy that formula down.

 

[thomasb82]

ah thanks, running into an error but think thats because of my excel being in swedish. will let ya know if i get it to work

 

[thomasb82]

yep, it was an translation issue. apparently swedish excel uses semcolons instead of commas. thanks again, its working fine now!

 

[Peter_SSs]

Glad you got it sorted.