How to create formula that will switch out information

C

Calgaryexcel

New member

Joined

Jul 22, 2020

Messages

5

Reaction score

0

Points

0

Excel Version(s)

365

• Jul 22, 2020

• #1

I'm trying to build a replace formula, but want the data to be pulled from a list that has two columns with 107 rows. The first column would have staff names, and the second would have staff community of residence. Essentially if a staff name is entered in a cell, I want the text to be replaced with the community associated with the staff.

 

Pecoflyer

Admin Alumnus

Joined

Oct 13, 2011

Messages

1,802

Reaction score

1

Points

38

Location

Brussels Belgium

Excel Version(s)

2010 on Xubuntu - O365

• Jul 22, 2020

• #2

Hi
please post a sample sheet with some data and expected results added manually. Thanks

 

C

Calgaryexcel

New member

Joined

Jul 22, 2020

Messages

5

Reaction score

0

Points

0

Excel Version(s)

365

• Jul 23, 2020

• #3

So if a staff name were entered in Column A it would then switch it out to reflect the associated community of residence.

 

Attachments

• Name Example.xlsx

  11 KB · Views: 12

AliGW

Administrator

Joined

Nov 8, 2015

Messages

1,843

Reaction score

8

Points

38

Location

Ipswich, Suffolk, England

Excel Version(s)

MS 365 (Beta Insider Channel)

• Jul 23, 2020

• #4

Show us manually what you want. It's not really clear what you mean by "switch it out".

 

Bob Phillips

Super Moderator

Staff member

Joined

Mar 21, 2011

Messages

1,960

Reaction score

0

Points

36

Excel Version(s)

O365

• Jul 23, 2020

• #5

You can't do that in standard Excel, but you could do it with VBA, or a sleight-of-hand with the camera tool.

 

C

Calgaryexcel

New member

Joined

Jul 22, 2020

Messages

5

Reaction score

0

Points

0

Excel Version(s)

365

• Jul 24, 2020

• #6

So a formula that does this is =Substitute,INDEX(FIND,1),INDEX(REPLACE,1). The problem is that you can only nest max of 64 and I need 107+

 

C

Calgaryexcel

New member

Joined

Jul 22, 2020

Messages

5

Reaction score

0

Points

0

Excel Version(s)

365

• Jul 24, 2020

• #7

Assuming the file uploaded. If you see in cell A2 I’ve entered “staff 2” which in reality would be the staffs name. Once that is typed it then needs to convert to the community beside their name in the list.

 

Attachments

• Name Example.xlsx.xls

  11 KB · Views: 14

AliGW

Administrator

Joined

Nov 8, 2015

Messages

1,843

Reaction score

8

Points

38

Location

Ipswich, Suffolk, England

Excel Version(s)

MS 365 (Beta Insider Channel)

• Jul 24, 2020

• #8

The workbook still doesn’t show what you want.

 

C

Calgaryexcel

New member

Joined

Jul 22, 2020

Messages

5

Reaction score

0

Points

0

Excel Version(s)

365

• Jul 24, 2020

• #9

Sorry that was an error on my end. So in column A you can see that I have entered various "staff names". When I do this it should then change the text to be the community in which the staff lives in.

 

Attachments

• Name Example.xlsx

  11 KB · Views: 10

AliGW

Administrator

Joined

Nov 8, 2015

Messages

1,843

Reaction score

8

Points

38

Location

Ipswich, Suffolk, England

Excel Version(s)

MS 365 (Beta Insider Channel)

• Jul 24, 2020

• #10

I give up.

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Staff Name							Staff Name	Staff Community
2​								Staff 2	Canyon Meadows
3​								Staff 3	Silverado
4​								Staff 4	Cochrane
5​								Staff 5	Mahogany
6​								etc	Bankview
7​									Abbeydale
8​									Airdrie
9​									Midnapore
10​									Woodbine
11​									Mayland Heights
12​									Mount Royal
13​									Coach Hill
14​									Cochrane
15​									Airdrie
16​									Discovery Ridge

Sheet: Sheet1