Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-05-2019, 09:19 PM
radicalrom radicalrom is offline Recommendation on different worksheets Windows 10 Recommendation on different worksheets Office 2016
Novice
Recommendation on different worksheets
 
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, 8 views)
Reply With Quote
  #2  
Old 01-05-2019, 11:46 PM
xor xor is offline Recommendation on different worksheets Windows 10 Recommendation on different worksheets Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe like the attached
Attached Files
File Type: xlsx Dropdown List and Index option.xlsx (13.4 KB, 14 views)
Reply With Quote
  #3  
Old 01-06-2019, 12:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Recommendation on different worksheets Windows 7 64bit Recommendation on different worksheets Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@radicalrom
Please read the last post at thread https://www.msofficeforums.com/excel...tml#post136994
Thank you
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 01-06-2019, 04:34 AM
ArviLaanemets ArviLaanemets is offline Recommendation on different worksheets Windows 8 Recommendation on different worksheets Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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, 12 views)
Reply With Quote
  #5  
Old 01-06-2019, 05:55 AM
radicalrom radicalrom is offline Recommendation on different worksheets Windows 10 Recommendation on different worksheets Office 2016
Novice
Recommendation on different worksheets
 
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 Recommendation on different worksheets Windows 10 Recommendation on different worksheets Office 2016
Novice
Recommendation on different worksheets
 
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 on different worksheets Recommendation for good powerpointer clicker or remote app s_turner PowerPoint 1 07-21-2014 06:53 PM
Recommendation on different worksheets 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
Recommendation on different worksheets File compression recommendation bb80 Office 1 08-13-2009 10:58 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft