Question description:
This user has given permission to use the problem statement for this
blog.
How can I automatically change different cells and the information they display if we select different option from a drop down list?
Solved by V. U. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/10/2018 - 08:40
Hello and thank you for choosing Got It Pro-ExcelChat! I can help you with your problem.
Excelchat Expert
07/10/2018 - 08:40
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows. Let me ask you a couple of quick questions to make sure I fully understand your problem. :)
Excelchat Expert
07/10/2018 - 08:41
It seems you need help with changing data once you select an option from a drop down list. Will you be able to share the file you're working on and elaborate the requirement? Thanks!
User
07/10/2018 - 08:41
Yes. Just one moment please.
Excelchat Expert
07/10/2018 - 08:43
Sure thing, will wait for your file.
User
07/10/2018 - 08:45
[Uploaded an Excel file]
Excelchat Expert
07/10/2018 - 08:46
I can see you have a drop downlist in E6 that is used as reference of the other cells in column E. What values needs to be changed if you select an option from the drop down list?
User
07/10/2018 - 08:48
basically all the columns from AK to AW. So thats the min, mid and max compensation and the segments
Excelchat Expert
07/10/2018 - 08:50
Where can we get the data to return in these columns?
User
07/10/2018 - 08:50
You can find the info under the ranges sheet
User
07/10/2018 - 08:50
/tab
Excelchat Expert
07/10/2018 - 08:51
It seems they have different naming conventions. Is compensation to be linked to column F of ranges tab?
User
07/10/2018 - 08:51
yes Column F and H
Excelchat Expert
07/10/2018 - 08:52
Oh, so in column AK of data tab, we need to combine the details of column F and H of Ranges tab?
Excelchat Expert
07/10/2018 - 08:55
Hello? :)
User
07/10/2018 - 08:55
Hm.. The range levels AD01, AD02 have different countries and those different countries have job profiles related to them which is Table H
Excelchat Expert
07/10/2018 - 08:55
Do you mean column H?
User
07/10/2018 - 08:56
Ys Column H sorry.
Excelchat Expert
07/10/2018 - 08:56
So you mean to say, in column AK of the data tab, we need to return the value from column H of ranges tab?
User
07/10/2018 - 08:56
Well.
Excelchat Expert
07/10/2018 - 08:57
Yes? :)
User
07/10/2018 - 08:58
In column AK we need to return value from column F yes
User
07/10/2018 - 08:58
as well as AM is the same
User
07/10/2018 - 08:59
then AN values are returned from column H
Excelchat Expert
07/10/2018 - 08:59
Thank you for clarifying. How about column AL?
User
07/10/2018 - 08:59
Coulmn AL would be the same date for all
Excelchat Expert
07/10/2018 - 09:00
How about Eligibility rule?
User
07/10/2018 - 09:02
eligibilty rule is ina separate workbook
Excelchat Expert
07/10/2018 - 09:02
Oh okay noted on that. I'll leave it untouched then. How about the number of segments (column AP)? Thanks! :)
User
07/10/2018 - 09:03
number of segments column AQ would be all 3
User
07/10/2018 - 09:04
AP*
Excelchat Expert
07/10/2018 - 09:05
Noted on that. Let me work on it for about 20 minutes. Thanks!
Excelchat Expert
07/10/2018 - 09:08
For the spread (column AT), where do we need to reference this?
User
07/10/2018 - 09:08
Column AQ is from Column J, column AR from Column L, Column AS is from N and the segment 1 2 and 3 from KMN respecivelt
User
07/10/2018 - 09:09
we can leave spread
Excelchat Expert
07/10/2018 - 09:09
Oh I see. Noted on that. :)
Excelchat Expert
07/10/2018 - 09:10
Already done! Please see attached for the updated file for your review.
[Uploaded an Excel file]
User
07/10/2018 - 09:12
ok. i will check
Excelchat Expert
07/10/2018 - 09:15
Sure thing, please do. Please let me know should you have any concerns.
User
07/10/2018 - 09:15
In the AM column inle row 1 is changing
Excelchat Expert
07/10/2018 - 09:16
Sorry, I thought column AM is the same with column AK.
Excelchat Expert
07/10/2018 - 09:18
Could you elaborate further on what the error in this is?
Excelchat Expert
07/10/2018 - 09:19
Could you refer to this excel file instead. I reconfigured column AM to instead be linked to column AK.
[Uploaded an Excel file]
User
07/10/2018 - 09:19
ok sure, let me see
Excelchat Expert
07/10/2018 - 09:19
So basically, AM will eventually change based on column AK's values (just replicating what AK displays).
User
07/10/2018 - 09:20
Oh okay.
User
07/10/2018 - 09:20
Yes. This is correct.
Excelchat Expert
07/10/2018 - 09:20
Glad to be of help! Does this particularly solve your main query? :)
User
07/10/2018 - 09:21
Yes it does. What about the decription column
Excelchat Expert
07/10/2018 - 09:21
Oh yes, let me do that in a quick.
Excelchat Expert
07/10/2018 - 09:22
Please check this one out and please confirm if it is as well correct. Thanks!
[Uploaded an Excel file]
User
07/10/2018 - 09:24
Yes. Seems fine :)
Excelchat Expert
07/10/2018 - 09:24
Nice to know! Do you have any more clarifications with the provided solution? :)
User
07/10/2018 - 09:24
Now can you run me down on the steps you took
Excelchat Expert
07/10/2018 - 09:26
Oh sure. Actually, if you look at it, the formula for all uses the same functions IF, INDEX and MATCH. Mainly, the IF function is only used to check if cell E6 is blank or not. If blank, the formula just returns a blank value. However, if not, it makes use of INDEX and MATCH to return the appropriate value based on the column E values.
Excelchat Expert
07/10/2018 - 09:27
MATCH mainly functions by getting the value in column E and matching it against column C of the ranges tab.
Excelchat Expert
07/10/2018 - 09:27
After it gets a match, the function memorizes the row number of this match. INDEX, on the other hand, then uses this row number to get the value on the index range.
Excelchat Expert
07/10/2018 - 09:28
So when the MATCH says that a match was found in row 10 within the match range, the INDEX function returns the value within the index range that is in row 10.
Excelchat Expert
07/10/2018 - 09:29
So fundamentally, that's how the formula works. What varies only is the index range of the different columns.
Excelchat Expert
07/10/2018 - 09:29
Like for example, the index range of column AK is column F of ranges tab while the index range of column AN is column H of the ranges tab.
Excelchat Expert
07/10/2018 - 09:30
The match range will always be column C of the ranges tab. :)
User
07/10/2018 - 09:31
Okayy...So If we were to add additional information/columns there is no need to change the formula just the index range
User
07/10/2018 - 09:31
?
Excelchat Expert
07/10/2018 - 09:31
That is correct.
Excelchat Expert
07/10/2018 - 09:31
Well, you will need to change the "index range" within the formula.
Excelchat Expert
07/10/2018 - 09:31
Everything else will remain the same though.
User
07/10/2018 - 09:32
okay so i shall click on which cell to do that?
User
07/10/2018 - 09:32
or which button
Excelchat Expert
07/10/2018 - 09:32
Well, you will need to check on all cells. Let me paste in the formula for cell AN6: =IF(E6="","",INDEX(Ranges!$H$3:$H$975,MATCH(E6,Ranges!$C$3:$C$975,0)+ROW(1:1)-1))
Excelchat Expert
07/10/2018 - 09:33
In here, the index range would be Ranges!$H$3:$H$975
Excelchat Expert
07/10/2018 - 09:33
This says that the index range is H3:H975 of the ranges sheet.
Excelchat Expert
07/10/2018 - 09:34
If for example you want to create another column and have this referenced to column O of ranges sheet, you can just change Ranges!$H$3:$H$975 to Ranges!$O$3:$O$975
Excelchat Expert
07/10/2018 - 09:34
Notice that what changed only was the "H" characters.
Excelchat Expert
07/10/2018 - 09:35
So eventually, the formula that you'll be using for the column referenced to column O of ranges tab would be: =IF(E6="","",INDEX(Ranges!$O$3:$O$975,MATCH(E6,Ranges!$C$3:$C$975,0)+ROW(1:1)-1))
User
07/10/2018 - 09:36
ok so the column that will be populated would be in the first argument
Excelchat Expert
07/10/2018 - 09:36
that is correct
User
07/10/2018 - 09:36
and from where we are getting the info is the 2nd argument?
User
07/10/2018 - 09:36
And the ROW will be same as well?
Excelchat Expert
07/10/2018 - 09:36
Sorry, what do you mean second argument?
Excelchat Expert
07/10/2018 - 09:37
ROW will always start at ROW(1:1). When you drag it down, notice that it changes to ROW(2:2) and ROW(3:3) and so on.
Excelchat Expert
07/10/2018 - 09:37
That's just correct.
User
07/10/2018 - 09:38
2nd argument meaning the MATCh is where we are taking the values from
User
07/10/2018 - 09:38
right?
Excelchat Expert
07/10/2018 - 09:38
Oh I see. That would remain as is since it will always be linked to column C.
User
07/10/2018 - 09:38
okay
Excelchat Expert
07/10/2018 - 09:38
Nice to know! Do you have any more clarifications with the provided solution? :)
User
07/10/2018 - 09:39
That will be all for today. You've been very helpful :)
Excelchat Expert
07/10/2018 - 09:39
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
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.
