Complicated, Formula or Macro?

[wazzulu1]

Hi;

I have a spreadsheet which has a number in column D, and I need to divide that number by 1 to 12 months, and post it to the appropriate cells for each month (Jan through Dec), in columns G through R.

I'm making a dropdown list, so the user will see Jan-Dec, etc, to chose their starting and ending month. I made a table on sheet2 with a column to show how many months they total, along with the list description, which is what I want to use for column E on sheet1 to divide column D by.

Last, once I get this number, I need to filter which months they are applied to by the label (Jan-Dec, Feb-Apr,etc), and I am stuck between looking at a formula or using a macro.

The macro I imagine would have to be executed to refresh the data, verses a formula that would automatically do it.

I would post the sample spreadsheet, but I don't see how I'm able to do so. Forgive me for being long winded, I'm new here.

Any suggestions would be appreciated.

 

[fairwinds]

Hi,

Try:

=IF((MATCH($B$2,$G$1:$R$1,0)<=COLUMN()-COLUMN($F$2))*(MATCH($C$2,$G$1:$R$1,0)>=COLUMN()-COLUMN($F$2)),$D2/(MATCH($C$2,$G$1:$R$1,0)-MATCH($B$2,$G$1:$R$1,0)+1),"")

in G2.

Drag right / down.

Book1
	B	C	D	E	F	G	H	I	J	K	L
1	Start	End	Number			Jan	Feb	Mar	Apr	May	June
2	Feb	Apr	100				33.33333333	33.33333333	33.33333333
3
4
Sheet3

 

[wazzulu1]

Thank You!!!!!!!!!!

 

[wazzulu1]

Hi again;

Is there anyway to suppress the #N/A message if no months are selected?

It makes the spreadsheet look very ugly with those #N/A's all over.

 

[fairwinds]

Change formula to:

=IF(AND(LEN($B$2),LEN($C$2)),IF((MATCH($B$2,$G$1:$R$1,0)<=COLUMN()-COLUMN($F$2))*(MATCH($C$2,$G$1:$R$1,0)>=COLUMN()-COLUMN($F$2)),$D2/(MATCH($C$2,$G$1:$R$1,0)-MATCH($B$2,$G$1:$R$1,0)+1),""),"")