SOLVED

Creating a Form

Copper Contributor

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.

18 Replies

@ineedhelp 

 

Power Query is 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.  

@ineedhelp 

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

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

@ineedhelp 

Just to clarify - to put C3:C9 you mean exactly these numbers

image.png

and nothing more, you don't need other weeks? And what is (3-5)?

@Sergei Baklan No, I mean C3:C9 on the summary tab with 3-5 being the number of days to input on the summary tab next to number of sundays, mondays, etc.

@ineedhelp 

 

So, let select some tab. When in C3 of summary tab it'll be number of Sundays from column A (alternatively from Column B, doesn't matter) of selected tab - lot of them. In D3 shall appear magic 3, 4 or 5 - from where they shall be taken?

image.png

 

 

@Sergei Baklan No. Say tab 1 is January. In C3 of the Summary tab, I need to be able to put in a number between 3 and 5 (i.e. there were 4 Sundays in January) and have a hidden formula in column D to go to S77 on tab 1 (January) and multiple it by the 4 Sundays to see the total revenue expected on Sundays in January. 

 

There also needs to be a drop down list on the summary tab for when I need to do the same for February, March, etc. and have it so that the hidden formula in column D of the summary tab automatically adjusts (i.e. knows to go to January, February, etc. depending on what's been selected on the drop down) so that if I want to say there were only 3 Sundays in February, I just select February from the drop down, put 3 into C3, and have the hidden formula multiply 3 by S77 of the February tab (tab 2)

@ineedhelp , thank you for the clarification. One more - in you sample file there is Jan 2018, Jan 2019 and Jan 2020 in one tab, which year to take? Or your actual file has another structure and in January tab you have only records for January 2019? Are there any other changes in actual file compare to your sample? At least it looks like we shall rename Sample 1 tab to January, Sample 2 tab to February, etc., and remove from each tab any other records. For which years?

@Sergei Baklan The Chart from O76:U83 is only analyzing data from January 2019. I'm dealing with FY 18-19 right now so the only January is 2019. 

 

It's just a sample chart so I wasn't largely concerned with having the tabs the actual names of the months. 

@ineedhelp 

So, in formulas keep Sample 1, etc and count all Sundays,.. which are in column B of each tab, you will adjust result yourself?

@Sergei Baklan All I need the formula to reference is cell S77 on the sample tabs (again, just making sure the formula adjusts to the tab based on the drop down. (i.e. if Sample 1 is selected from the drop down, I need it to multiply by S77 on the Sample 1 tab, not by S77 on the Sample 2 tab, etc.). I can adjust the formulas later to reference the exact cells needed.

best response confirmed by ineedhelp (Copper Contributor)
Solution

@ineedhelp , okay, when in Summary tab

image.png

stay on C2, on ribbon Data->Data validation, select list and add names of your tabs as here

image.png

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.

@Sergei Baklan This is perfect, thank you so much!

@ineedhelp , you are welcome

@Sergei Baklan Me again. So the formatting of the chart needs to change a little bit. All of the data is going to be on one master sheet, but it will have multiple rows to choose from (see below)

 

1.png

 

Here is what the summary chart will look like. I put in references (DO2, CL2, and CO2) so you know where the information is coming from. The desired Revenue increase needs to be pulled from cell DP2, but it can change in that it's set at 20,000 right now but if I put 30,000 in cell C4 below, DP2 (above) should automatically update to 30,000, which would then update the amounts for Additional Softline (ES2), Additional Hardline (FA2), etc.

 

2.png

 

I didn't put cell references for the remainder of the sheet since I figured if I at least know how to do the references for a few of the cells, I can adapt them for the remainder.

 

I already have the drop down list set up, it just needs to know to go to a different row, rather than a different tab.

 

Hope this makes sense.

@ineedhelp 

That could be like

=INDEX('Master Sheet'!$DP:$DP,MATCH($C$1,'Master Sheet'!$A:$A,0))

 

I tried editing the formula to put in the correct tab name and am getting #N/A

 

=INDEX('with dollar amount pick'!$DP:$DP,MATCH($C$1,'with dollar amount pick'!$A:$A,0))

 

This is what I adjusted the formula to say. 

@ineedhelp 

Please check formula in attached file

1 best response

Accepted Solutions
best response confirmed by ineedhelp (Copper Contributor)
Solution

@ineedhelp , okay, when in Summary tab

image.png

stay on C2, on ribbon Data->Data validation, select list and add names of your tabs as here

image.png

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.

View solution in original post