Pulling cell data from one tab on a spreadsheet to a cell on another tab in same spreadhseet

[tonyna22]

I have a spreadsheet where 2 of the tabs where there is a list of over a thousand customers and the corresponding sales rep. The sales reps have completely changed and I need to make the change on both tabs and want to change on 1 tab and have it automatically pullover to the other tab.

So on tab1 the company is in column A & sales rep is column C. On tab2 the company is column E & sales rep is column I.

Example: if tab2 has ABC Company (e2) & the current sales rep is john smith (i2). I need to remove john smith from i2 and make it bob brown. I then want bob brown to automatically move to the corresponding rep cell on tab1 (c2) assuming ABC Company is A2 and sales rep is in c2

 

[Ludwig]

Why have duplicated text values? Use vlookup (or better still, index/match) to show the current sales rep names in tab2 that you have entered in tab1?

e.g. in tab2 column I2 enter the formula

=iferror( if( index( tab1!C:C , match( E2 , tab1!A:A , 0 ) ) = "" , "" , index( tab1!C:C , match( E2 , tab1!A:A , 0 ) ) ) , "not found in other table" )

Reason for the if around the 'duplicate' index/match is in case you haven't coded a sales rep in the first tab.

You'll need to adjust row# (2) used above for any heading/title row/s you may have, and copy formula down.

Just leave that formula in place forever. Never need adjust in two places again. Or if for some reason you need to have it duplicated as text, after entering formula down the column, just copy all the cells & immediately paste-as-values over the top.

 

[tonyna22]

thank you