Forum Discussion
Creating a Form
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.
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?
SergeiBaklan 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.
Please check formula in attached file
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.
SergeiBaklan 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)
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.
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 , you are welcome
SergeiBaklan This is perfect, thank you so much!
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.
SergeiBaklan 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.
So, in formulas keep Sample 1, etc and count all Sundays,.. which are in column B of each tab, you will adjust result yourself?