Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-05-2019, 09:19 PM
radicalrom radicalrom is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2019
Posts: 9
radicalrom is on a distinguished road
Default Recommendation on different worksheets

I am using the index function on a lot of data. To make it quicker I have split off my data into multiple worksheets. I have a drop down list and the user would select a state. Each worksheet is a state.



I have no problem of when a user selects a state that it somehow copies that data from the correct worksheet and then the index function could run on the current sheet. Maybe you can somehow index directly the correct worksheet after it is selected.

There maybe another way to solve this issue. If you have an idea, I am willing to run tests.

I am attaching a basic example file and B1 cell is the pull down. Once the user selects a state, I would like it to get the information from the correct worksheet (to make it easy the values in the drop down = the worksheets name).


Thank you so much for your recommendation.

Jeff Unruh
Attached Files
File Type: xlsx Dropdown List and Index option.xlsx (9.9 KB, 2 views)
Reply With Quote
  #2  
Old 01-05-2019, 11:46 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 942
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

Maybe like the attached
Attached Files
File Type: xlsx Dropdown List and Index option.xlsx (13.4 KB, 8 views)
Reply With Quote
  #3  
Old 01-06-2019, 12:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,256
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@radicalrom
Please read the last post at thread http://www.msofficeforums.com/excel/...tml#post136994
Thank you
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #4  
Old 01-06-2019, 04:34 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 349
ArviLaanemets is on a distinguished road
Default

Another example which uses CHOOSE() to select Table.

On sheet States I entered full list of states, but as I didn't add sheets and Tables for all 50 states (otherwise I couldn't create nRepRange), I added there a table tTest with 2 states listed too, and used it as base for dynamic ranges. You have to edit them when you want to use the full list of states.
Attached Files
File Type: xlsx IndexExample.xlsx (16.2 KB, 6 views)
Reply With Quote
  #5  
Old 01-06-2019, 05:55 AM
radicalrom radicalrom is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2019
Posts: 9
radicalrom is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
Maybe like the attached

That is very cool about the indirect function. I am going read up on how it works, but this is great. Thank you.
Reply With Quote
  #6  
Old 01-06-2019, 05:58 AM
radicalrom radicalrom is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2019
Posts: 9
radicalrom is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Another example which uses CHOOSE() to select Table.

On sheet States I entered full list of states, but as I didn't add sheets and Tables for all 50 states (otherwise I couldn't create nRepRange), I added there a table tTest with 2 states listed too, and used it as base for dynamic ranges. You have to edit them when you want to use the full list of states.
What a very creative way to solve this problem. I am going to dig into looking into this example, but when I played with it, it was perfect.

I want to thank you and everyone for the help and recommendations. My knowledge has improved greatly with these responses.
Reply With Quote
Reply

Tags
index, recommendation, worksheet

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Several Worksheets into one master caz46 Excel Programming 2 05-11-2018 11:13 PM
Recommendation for good powerpointer clicker or remote app s_turner PowerPoint 1 07-21-2014 06:53 PM
Need weekly Work log template recommendation Shazamy Excel 2 10-04-2013 10:50 AM
Interactive worksheets on the web beingbobbyorr Excel 1 08-22-2011 05:32 AM
File compression recommendation bb80 Office 1 08-13-2009 10:58 AM


All times are GMT -7. The time now is 06:33 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft