**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hi, I need help with excel formulas in linking the cells in same column

Solved by F. Q. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
03/08/2018 - 08:09

Hello

User
03/08/2018 - 08:09

Hi

Excelchat Expert
03/08/2018 - 08:09

thank you for using the got it pro

Excelchat Expert
03/08/2018 - 08:10

how may i help you

Excelchat Expert
03/08/2018 - 08:11

Hello

User
03/08/2018 - 08:12

i have checklist (64) in rows and site names in column .under site name in column(which have a dropdown around 6 devices) if I select 1 device name out of 6 from drop down I should get the respective answers which I update

Excelchat Expert
03/08/2018 - 08:13

ok do you have your file so we can check on it

User
03/08/2018 - 08:13

yes, I have

Excelchat Expert
03/08/2018 - 08:14

ok could i have your file

Excelchat Expert
03/08/2018 - 08:14

so we can check for the solution that you want

User
03/08/2018 - 08:14

sen

[Uploaded an Excel file]

User
03/08/2018 - 08:14

sent

Excelchat Expert
03/08/2018 - 08:15

ok checking on that

User
03/08/2018 - 08:15

Sure

Excelchat Expert
03/08/2018 - 08:17

i have it now,

Excelchat Expert
03/08/2018 - 08:17

which tab we will check on it

User
03/08/2018 - 08:20

we will take bangalore-MTP1 column

User
03/08/2018 - 08:20

I have 3 devices in that site for which I am doing upgrade

Excelchat Expert
03/08/2018 - 08:20

for Tocal scope tab?

User
03/08/2018 - 08:21

only scope one

Excelchat Expert
03/08/2018 - 08:21

ok

Excelchat Expert
03/08/2018 - 08:22

ok what will we do now here?

User
03/08/2018 - 08:22

down to bangalore mtp 1 , we will insert another row, in that we will create dropdown and add device names

Excelchat Expert
03/08/2018 - 08:22

ok

User
03/08/2018 - 08:23

if I select one device name it should give the respective values, which are asked in rows

Excelchat Expert
03/08/2018 - 08:23

uhm ok

User
03/08/2018 - 08:23

that drop down tab should be linked with other cells in same column

User
03/08/2018 - 08:23

is it possible to do

Excelchat Expert
03/08/2018 - 08:24

yes

User
03/08/2018 - 08:24

which is that option

Excelchat Expert
03/08/2018 - 08:24

like for sample we have a dropdown

Excelchat Expert
03/08/2018 - 08:24

then I select the name "cinbancl9cts01"

Excelchat Expert
03/08/2018 - 08:25

you want to automatically display the output for the other row right?

Excelchat Expert
03/08/2018 - 08:25

which was belong on that device name

Excelchat Expert
03/08/2018 - 08:25

is it right?

User
03/08/2018 - 08:25

correct

User
03/08/2018 - 08:25

exactly

Excelchat Expert
03/08/2018 - 08:25

ok

Excelchat Expert
03/08/2018 - 08:26

then the data is on the total scope tab?

User
03/08/2018 - 08:26

yes

Excelchat Expert
03/08/2018 - 08:26

ok got it

Excelchat Expert
03/08/2018 - 08:26

just give me a minute for this one

User
03/08/2018 - 08:26

sure

User
03/08/2018 - 08:27

DeviceTypeName DeviceModel EOLStatus Device Lifecycle Project
SvcLine
can you also please add these columns from total scope to rows in scope sheet

Excelchat Expert
03/08/2018 - 08:28

ok

User
03/08/2018 - 08:28

you can add after row 10 in scope sheet

Excelchat Expert
03/08/2018 - 08:28

ok :)

Excelchat Expert
03/08/2018 - 08:28

this is noted

User
03/08/2018 - 08:29

thanks

User
03/08/2018 - 08:29

I have a meeting now

User
03/08/2018 - 08:29

will be back in 15minutes

Excelchat Expert
03/08/2018 - 08:29

ok

Excelchat Expert
03/08/2018 - 08:29

just be on time :)

User
03/08/2018 - 08:29

please evaluate the option which I asked

User
03/08/2018 - 08:29

Sure

Excelchat Expert
03/08/2018 - 08:29

since we only have 20 minutes left

User
03/08/2018 - 08:29

correct

Excelchat Expert
03/08/2018 - 08:29

:)

User
03/08/2018 - 08:29

i will be online

Excelchat Expert
03/08/2018 - 08:30

ok :)

Excelchat Expert
03/08/2018 - 08:34

here you go

Excelchat Expert
03/08/2018 - 08:35

[Uploaded an Excel file]

Excelchat Expert
03/08/2018 - 08:37

i already created a dropdown below the "BANGALORE-MTP-1"

Excelchat Expert
03/08/2018 - 08:38

then once you selected the a device name there, the ff. row will automatically change depending on which device name are there

Excelchat Expert
03/08/2018 - 08:38

i've used here the formula

Excelchat Expert
03/08/2018 - 08:38

=INDEX('Total scope'!A:J,MATCH(Scope!F2,'Total scope'!E:E,0),2)

Excelchat Expert
03/08/2018 - 08:39

where in Index are used to lookup value on a certain row & column

Excelchat Expert
03/08/2018 - 08:39

then i used match to get the row number of the device name

User
03/08/2018 - 08:42

you have created only drop down list

User
03/08/2018 - 08:42

but the values are not changing

Excelchat Expert
03/08/2018 - 08:42

its changing, try to select other device name

User
03/08/2018 - 08:43

how you did that

User
03/08/2018 - 08:43

can you please tell me

Excelchat Expert
03/08/2018 - 08:43

yes

User
03/08/2018 - 08:44

so that i can make some other changes

Excelchat Expert
03/08/2018 - 08:44

i've used this formula

Excelchat Expert
03/08/2018 - 08:44

=INDEX('Total scope'!A:J,MATCH(Scope!F2,'Total scope'!E:E,0),2)

Excelchat Expert
03/08/2018 - 08:45

the Index function used to lookup a certain row & column

Excelchat Expert
03/08/2018 - 08:45

then match function there was used to locate the row number of the value that we are lookup

User
03/08/2018 - 08:45

how to add new rows into formula

Excelchat Expert
03/08/2018 - 08:45

what do you mean new rows?

User
03/08/2018 - 08:46

is there any formula to add drop down device names for that site

Excelchat Expert
03/08/2018 - 08:46

you want to add another device name on the dropdown?

User
03/08/2018 - 08:47

not in same column

User
03/08/2018 - 08:47

for particular site , i want associated devices under them

User
03/08/2018 - 08:47

you can find them in total scope

Excelchat Expert
03/08/2018 - 08:49

so you want only the device name to be on the dropdown, for that Building name?

User
03/08/2018 - 08:49

yes, respective devices for their buildings

Excelchat Expert
03/08/2018 - 08:50

uhm ok let me check on this

User
03/08/2018 - 08:53

done with my call

User
03/08/2018 - 08:53

free to talk

User
03/08/2018 - 08:53

now

Excelchat Expert
03/08/2018 - 08:53

ok i'm currently working on the solution that you want

User
03/08/2018 - 08:54

sure

User
03/08/2018 - 08:54

will wait for you

User
03/08/2018 - 08:57

row values are not changing from 17... i want the values to be changed for 78 row items

Excelchat Expert
03/08/2018 - 08:57

ok i check that too

Excelchat Expert
03/08/2018 - 09:00

for the dropdonw

User
03/08/2018 - 09:01

where i need to apply index formula?

Excelchat Expert
03/08/2018 - 09:01

for as to get only the dropdown based on the building name, we need to define names on each building name

User
03/08/2018 - 09:01

building name are unique

Excelchat Expert
03/08/2018 - 09:01

yes,

User
03/08/2018 - 09:02

is there any way we speak on call?

Excelchat Expert
03/08/2018 - 09:02

im sorry we dont have that service

Excelchat Expert
03/08/2018 - 09:02

only a chat

User
03/08/2018 - 09:02

ok

Excelchat Expert
03/08/2018 - 09:02

i will make you sample of define names and will send it to you

User
03/08/2018 - 09:03

Ok

User
03/08/2018 - 09:06

time is ending

Excelchat Expert
03/08/2018 - 09:06

im near on the solution now

User
03/08/2018 - 09:06

ok

User
03/08/2018 - 09:06

thanks

User
03/08/2018 - 09:06

please share formula and also how to use it

Excelchat Expert
03/08/2018 - 09:07

we're i will get the data drom row 17-78?

User
03/08/2018 - 09:07

put some sample data

Excelchat Expert
03/08/2018 - 09:07

ok

Excelchat Expert
03/08/2018 - 09:07

all data must be on the total scope

User
03/08/2018 - 09:07

i have to change the values based on things happening

Excelchat Expert
03/08/2018 - 09:08

you can used this formula

Excelchat Expert
03/08/2018 - 09:08

=INDEX('Total scope'!A:J,MATCH(Scope!F2,'Total scope'!E:E,0),2)

User
03/08/2018 - 09:08

oh.. that wont be there on tab

Excelchat Expert
03/08/2018 - 09:08

on all of that

User
03/08/2018 - 09:08

i need to update that manually

Excelchat Expert
03/08/2018 - 09:08

just change the column nmber

Excelchat Expert
03/08/2018 - 09:08

[Uploaded an Excel file]

Excelchat Expert
03/08/2018 - 09:08

here's the sample i did on the dropdown,

User
03/08/2018 - 09:08

Ok

Excelchat Expert
03/08/2018 - 09:09

is it ok for you to raise the question again so we can finish on it?

User
03/08/2018 - 09:09

how to extend the time

Excelchat Expert
03/08/2018 - 09:09

since this template need more time to work on

Excelchat Expert
03/08/2018 - 09:09

just raise it again so i can claim it again

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*