Change cell value based on the value of another...

[okcalla]

Sorry in advance if this is confusing...

I need to find a way to put a value in a cell based on the text value of another. For example If I type "Company A" in cell B7 then I would like excel to look at another sheet I have with contact information and apply the appropriate info to the correct cells.

So, on sheet 1 In cell B7 I enter "company A"

excel looks to the worksheet I Have titled "Control Sheet" and in cell B4 is our contacts name for company A. I want excel to apply the value in "control sheet" B4 to worksheet 1 in cell B10.

this way if I update our contact information for "Company A" on our "Control sheet" I don't have to update each sheet individually to match.
also I would like to be able to have "Company B", "Company C" etc.

the company A B C, etc would be vendors so it is possible that the value of cell B7 one day could be Comapny A and the next company B and as such I would like the appropriate values to follow in cell B10 on worksheet 1.

I have almost no VB experience but i am willing to use VB. please be explicit in the instructions if VB is required.

thanks.

[dilipandey]

Hi okcalla,

welcome to the forum.
Sounds like a lookup query, would you able to upload a sample workbook with your expected results. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[okcalla]

test.xlsx In this case Cell B3 is the user input field. Cells B4 B5 and B6 should autofill from the values provided on the contact sheet.

[okcalla]

Thanks to your First comment i may have found my answer...

=LOOKUP(B3,'Contact Sheet'!1:1,'Contact Sheet'!2:2)

would somthing like that work or is there a better way?

[FDibbins]

Hi okcalla and welcome to the forum

if you make sure that ALL of your headings are identical on EVERY sheet, you can use this, copied down...

=INDEX('Contact Sheet'!$A$1:$E$4,MATCH($A4,'Contact Sheet'!$A$1:$A$4,0),MATCH(B$3,'Contact Sheet'!$A$1:$E$1,0))

Note that "contact" on sheets 1-3 has a traling space, and "help desk num." is not the same on Contact Sheet

You can use this, by adjusting the ranges, if your tables grow sidesways and downwards

[okcalla]

This worked. Can't thank you enough.

[FDibbins]

Happy to help, and thanks for the rep