Question description:
This user has given permission to use the problem statement for this
blog.
If we were to remove a drop down list for a worksheet and instead display all of its values in all the rows and columns, how would we go on about doing that in a quick way.
Thanks.
Solved by K. Y. in 36 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/10/2018 - 04:42
Hello and thank you for choosing Got It Pro-ExcelChat! I can help you with your problem.
Excelchat Expert
09/10/2018 - 04:42
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
09/10/2018 - 04:42
I see you need help populating your data and removing the dropdown list. In this case, may you please share the file you're currently working on. Thanks!
User
09/10/2018 - 04:43
Okay. One moment.
User
09/10/2018 - 04:44
Here
[Uploaded an Excel file]
Excelchat Expert
09/10/2018 - 04:45
I see you have a drop down list in E6 and certain formulas on the columns to the far right. We can easily eliminate the drop down list and just display all its options within that column.
User
09/10/2018 - 04:46
Okay.
User
09/10/2018 - 04:46
Can you walk me through please.
Excelchat Expert
09/10/2018 - 04:47
Oh, I see the formulas in column E are linked to the LOOKUP sheet. If I get it correctly, you want to display all these options by the number of times they have as count in the LOOKUP sheet, and then apply all your formulas to these options. Is that correct?
User
09/10/2018 - 04:48
Yes.
User
09/10/2018 - 04:48
That is correct.
Excelchat Expert
09/10/2018 - 04:48
Nice to know! Let me work on this within a local version I downloaded and will upload it here in our chat once done. I may be able to do this within 30 minutes. Let me work on it for a while and update you the soonest! :)
User
09/10/2018 - 04:49
Okay
Excelchat Expert
09/10/2018 - 04:55
Already done! Please see attached for your review.
[Uploaded an Excel file]
Excelchat Expert
09/10/2018 - 04:56
What I did was to eliminate the dropdown list which is what was required, and then extended down all your formulas on the right columns until the last option in column E.
Excelchat Expert
09/10/2018 - 04:59
Please let me know if this answers your concern.
User
09/10/2018 - 05:00
Ok. How do we eliminate the list. By going to data validation tool on the ribbon?
Excelchat Expert
09/10/2018 - 05:01
Sorry, kindly refer to this one instead. The list is already gone. And yes, you're right. We can eliminate the list by going to the Data validation and removing it from there.
[Uploaded an Excel file]
User
09/10/2018 - 05:03
From Row 502 onwards it is displaying REF! error
Excelchat Expert
09/10/2018 - 05:04
I see. Let me check that out.
Excelchat Expert
09/10/2018 - 05:06
It seems some of the formulas are eroneously configured and are going beyond the index ranges. Let me correct these for a while.
User
09/10/2018 - 05:07
Hm.okay
Excelchat Expert
09/10/2018 - 05:08
Particularly the ROW(1:1) part of the formulas. This was built only for the original setup, wherein only a max of 27 items occupy column E. In that case, the ROW(1:1) will only go until ROW(22:22).
Excelchat Expert
09/10/2018 - 05:09
However, since the column E lists reaches until 978 items, the ROW function goes until ROW(973:973). This will need to be changed in that matter.
User
09/10/2018 - 05:10
Okay...
Excelchat Expert
09/10/2018 - 05:12
Already done! Please see attached for your review. I changed the ROW function with a COUNTIF function so that it resets the count once the options in column E changes.
[Uploaded an Excel file]
User
09/10/2018 - 05:15
I see.
Excelchat Expert
09/10/2018 - 05:15
Please do let me know if you have any more concerns regarding the provided solution. :)
User
09/10/2018 - 05:17
Everything seems to be working fine :)
Excelchat Expert
09/10/2018 - 05:18
Glad to be of help! Does this particularly solve your main query? :)
User
09/10/2018 - 05:18
It does solve the main query.
Excelchat Expert
09/10/2018 - 05:18
Nice! You may 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.
