Forum Discussion
Creating a Form
I have a workbook of 30 different tabs and I need a summary chart on a separate tab that will have a drop down list with the names of each tab so that when one is selected, only the data from that specific tab can be used. I know how to create the drop down list, I just don't know how to make sure that when a specific tab is selected in the drop down, the data that is being used is from the correct tab. The summary tab needs to have fillable blank spots so that once a particular tab is selected from the drop down, a number can be placed in the fillable blank spot and a hidden formula, referencing math that is being done on the specified tab, can then produce a result.
For example, say I have twelve tabs, each with the months of the year on them and inside those tabs, are different pieces of information regarding amount of money made, number of tickets sold, labor hours, etc, with a formula to predict how good the month is with one of those variables missing (i.e. how many labor hours are needed to produce x amount of tickets). On the Summary tab, a drop down list is needed to identify which tab the data needs to be pulled from. Once the tab is selected, hidden formulas automatically adjust to the data on that particular tab so that when a piece of information is entered, a number will generate stating what can be expected for that month based off of past patterns.
I have been racking my brain for the last 2 hours trying to figure it out and I'm getting nowhere fast. Any help is greatly appreciated. Hopefully my explanations are enough to help you understand what I'm trying to accomplish.
ineedhelp , okay, when in Summary tab
stay on C2, on ribbon Data->Data validation, select list and add names of your tabs as here
To count number of weekdays for the selected sheet in C3 formula
=COUNTIF(INDIRECT("'" & $C$2 & "'!B:B"), $B3)and drag it down.
To return value of the cell S77 in selected sheet in D3
=INDEX(INDIRECT("'" & $C$2 & "'!S:S"),76+ROW()-ROW($B$2))and drag it down.
Sample is attached.
18 Replies
Hi
I agree with the team mate who recommended that you post a sample file. Specially your request has lots of details and community members would recommend different options (Functions, Power Query, VBA...) for you to chose what works best according to your preferences and experience.
Good Luck
Nabil Mourad
https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605is the right tool fo such applications to consolidate, transform, summarize data from multiple places. have you explored this yet ?
once you have teh data consolidated , then you can use Pivot /Charts , which by default provide you dropdown to pick the options to present a specific tab or any other variable.
While there may be other methods, given the amout of data you seem to have and need to transform, it is best to use power query.
If you provide a sample data file , you will get a better and specific solution in this forum.
I have not explored a Power Query. I have never even tried setting up a power query / pivot table to explore those options.
Here is some sample data. Basically, on the Summary tab I would need to be able to select Samples 1-3, since they are each different months, and then be able to put in a number in C3:C9 (3-5) and have a hidden formula off to the right hand side somewhere (maybe in Column E or something), that will know to go to the sheet selected in the drop down and multiply the number on the summary tab by S77:S83
Just to clarify - to put C3:C9 you mean exactly these numbers
and nothing more, you don't need other weeks? And what is (3-5)?
