Validation Cell To Populate Data?

[thussain]

I'm having trouble thinking up of a formula. I have tried some different methods but they don't work hence I can do with your expertise.

E3 = Weeks remaining
E5 = Annual amount (£)
E6 = Validation box


Basically I have a validation box which allows users to choose the month i.e. Sep, Oct, Nov etc. Based on this selection I want the cells below to be populated with results.

Column A Column B
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun

If the user selects September as the validation date I would like the following to be populated

Column A Column B
Sep (E3/E5)*4
Oct (E3/E5)*4
Nov (E3/E5)*4
Dec (E3/E5)*3
Jan (E3/E5)*4
Feb (E3/E5)*3
Mar (E3/E5)*4
Apr (E3/E5)*3
May (E3/E5)*3
Jun (E3/E5)*4

If however the user selects October, I need September to read zero and then the following

Column A Column B
Sep 0
Oct (E3/E5)*4
Nov (E3/E5)*4
Dec (E3/E5)*4
Jan (E3/E5)*4
Feb (E3/E5)*4
Mar (E3/E5)*4
Apr (E3/E5)*4
May (E3/E5)*4
Jun (E3/E5)*4

Again if the user selects say January then I need the months prior to that to read zero and the following to be populated:

Column A Column B
Sep 0
Oct 0
Nov 0
Dec 0
Jan (E3/E5)*6
Feb (E3/E5)*6
Mar (E3/E5)*6
Apr (E3/E5)*6
May (E3/E5)*6
Jun (E3/E5)*6

I don't know how to do this. I tried the following under column B1
=IF(E6="September", (E5/E3)*4, "0")

Then B2
=IF(OR(E6="September", E6="October"), (E5/E3)*4, "0")

Then B3
IF(OR(E6="September", E6="October", E6="November"), (E5/E3)*4, "0")

But this only works correctly for September as the logic in this incorrect. Is there any way I can solve this problem?

I've attached my Excel file if that helps. If I haven't made sense please let me know and I'll clarify. Thanks.

Calculator.xlsx

[ChemistB]

One way:
In E9 copied down

=IF(ISERROR(MATCH($E$6,$D$9:$D9,0)),0, $E$3/$E$5*4)
then fix the cells that are *3
Does that work for you?

[Tony Valko]

Try this...

Entered in E9 and copied down:

=IFERROR(IF(ROWS(E$9:E9)<MATCH(E$6,D$9:D$18,0),0,(E$3/E$5)*4),"")

[thussain]

That didn't work - the results are coming up as 29 pence?

The results should September be selected in the Validation box:

Sep 56
Oct 56
Nov 56
Dec 42
Jan 56
Feb 42
Mar 56
Apr 42
May 42
Jun 56

EDIT

Aw I think the E3 and E5 were the wrong way round in your formula - changed it and it does display the correct result but again, the problem is that it isn't going to be *4 in the formula every time. I can't manually change it every time as this is going to be passed on to other people and I can't expect them to change it. And the total always needs to match the annual.

In your formula if I choose January - it gives the zeros for Sep-Dec and for Jan-Jun 56 each which equals £336 but the annual amount is £504 which needs to be divided by the remaining months if that makes sense.

Can I do a table in Sheet 2 and then can that be copied over depending on the Validation selected?

P.S. But thanks for your input, much better result than mine!

[ChemistB]

Once the formulas are set up, you can lock them down by protecting the sheet and cells so they can't be changed. Alternately, you could have a VLOOKUP table and refer to that in the formula. Would that be preferable?

[thussain]

I think a VLOOKUP table would work better. If I have all the months set up in Sheet 2 with the calculations then get it to refer to the result depending on the month selected, that would work better.

The reason the current method won't work is because the calculation will change depending on the month selected. If the choose January it will no longer be *4 it will then be *6 so locking them down wouldn't work either.

I think VLOOKUP would be best. I understand how it works but I have no idea how to implement it in this case. Help would be appreciated.

EDIT - Give me 5 mins and I will attach sheet 2 with the tables.

[thussain]

I've attached the Excel file with Month tables in Sheet 2 - I only did Sep, Oct and Jan. If you can help me with those I can do the rest. So basically if they choose January in Sheet 1, I'd like it to display the January table from Sheet 2 as the results in Sheet 1. Is that possible?

Calculator Months.xlsx

[ChemistB]

For how you have it set up, I'd use OFFSET
In E9 copied down

=OFFSET(Sheet2!$B$13, ROWS($A$1:A1)-1,MATCH($E$6,Sheet2!$B$13:$X$13,0))
Does that work for you?

[thussain]

That's perfect! I wouldn't have ever been able to come up with that formula. Thank you so much ChemistB... The "ForumGuru" under your name is justified lol! But thanks, I appreciate it. I've been racking my brains for hours trying to figure it out (I'm a beginner so not good with complex formulas), I should have asked sooner. Thanks again! Saved me a lot of time.

[ChemistB]

Glad to help.