Expert Solution - Excel COLUMN Problems

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

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

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

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

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

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

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

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

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

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

all data must be on the total scope

User 03/08/2018 - 09:07

i have to change the values based on things happening