Complicated, Formula or Macro?

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
BCDEFGHIJKL
1StartEndNumberJanFebMarAprMayJune
2FebApr100 33.3333333333.3333333333.33333333  
3
4
Sheet3
 

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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),""),"")
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,540
Latest member
Nereus A

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top