Embedded formulas

[bclaredal]

I've got an interesting one that I hope someone can help me with.

I have a spreadsheet with two drop-down lists and two cells to fill in and this populates data in a table so that a journal entry can be entered into the accounting software.

The drop-down lists consist of the following:
1. Type = Annual or Prorated
2. Month = months of the year showing first three letters of the month

I have figured out how to calculate the prorated amount per month but I am struggling on how to calculate the annual amount. The annual amount depends on what month is selected in the month drop-down list, and of course annual being selected in the Type drop-down list. For each account that I have listed in column B, if annual is selected, and say for example JUL (July) is selected in the month, I want everything from April to July included in July's data and all other month's going forward to have their prorated amount.

Here's an example for you:

If the data was stating the following for each month: April = 1, May = 2, June = 3, etc. until March = 12. If I select July in the month drop-down list, I would want July's data total to be 10 (1+2+3+4) and August to March to show their correct amounts (i.e., 5 to 12).

I would be very grateful if someone could help me with this. The solution can be macro or formula generated, I have no preference.

Let me know if you require further information.

Thanks for you help!!!

 

[ChrisR]

Would this work?

Assuming you put this data in cells A1:B12

[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]April[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]August[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]September[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]October[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]November[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]December[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]February[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]March[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[/TR]
</TBODY>[/TABLE]


Then enter this formula to work off your drop down

=SUM($B$1:INDIRECT(ADDRESS(MATCH(F4,A:A,0),2)))

Just change F4 for whatever your drop down is...........

This will give you a cumulative total, based on April being 1, May being 2 etc

 

[JLeno]

I'm not sure I fully understand, you could create a formula that does the following:
If a month is before the selected month, then 0
If a month is the selected month, then sum of that month and all previous months (until April)
If a month is after the selected month, then just the amount ofthat month

=IF(A1<'Month selection cell',0,IF(A1='Month selection cell',SUM(OFFSET(B1,-A1,0,A1+1,1)),B1))

where A1 (and down) is the month number;
B1 is the amount for that month; and
'Month selection cell' is the cell with the selected month (in this case 4, July)

 

[bclaredal]

Could you transpose your tables and do it that way? I have the months laid out across column headings rather than rows and am trying to adjust your formulas for this difference but am having troubles.

 

[bclaredal]

I want the formula to be in the cells where the data is because the data is already driven by another formula that is multiplying two numbers together.

I have this formula in there already:
=ROUND(IF($D$4="Prorated",$F13*SUMIF('Prorate List'!$B:$B,'Input (3)'!$D13,'Prorate List'!C:C),0),2)

Where:
D4 = Type drop-down (Prorated or Annual)
F13 = Annual hours
the sumif is looking up a percentage in a table
I have the false argument in the if statement set to zero because that is where the annual formula would go, or conversely, if I used the annual formula first then the prorated formula could go in the false, which come to think of it might be easier.

 

[ChrisR]

It's very difficult to see what you're trying to do without any data........But here is a transposed version of what I posted

=SUM($A$2:INDIRECT(ADDRESS(2,MATCH(F4,A1:L1,0))))

Assuming Months are in A1:L1
numbers 1 to 12 are in A2:L2

F4 is your drop down

 

[bclaredal]

I can't post files yet... hopefully that will be unlocked at some point... but if you pm me your email I could send you the file I am working with.

 

[ChrisR]

PM sent

 

[bclaredal]

Email sent.

 

[ChrisR]

Hi Brian,

I have returned the workbook to you.......Let me know if it works for you.

Cheers
Chris