Excel - IF Function Problem - Expert Solution

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
[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
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc