Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Auto Divide Cell Value to Specified Cells in a Column

Copper Contributor

Hi. Newbie here, hope you can help me. Im working on a spreadsheet and I have to autodivide a certain number of cases to the number of headcount we have in a day across a column in a range of cells and at the same time if an employee had a remark on the remarks column that they be automatically skipped. Im not sure if I relayed it clearly but I dropped a pic of how I wanted it to be like. Thank you so much in advance! 

 

20220805_040118.jpg

9 Replies

@CloversBooks1993 

In E5:

=IF(D5="Absent",0,QUOTIENT($A$1,$A$2-COUNTIF($D$5:$D$9,"Absent"))+(COUNTIF(D$5:D5,"<>Absent")<=MOD($A$1,$A$2-COUNTIF($D$5:$D$9,"Absent"))))

Fill down to E9.

@Hans Vogelaar 

 

This is so helpful! How about if I have other values other than "Absent"? How can I add them? I appreciate you so much! 

@CloversBooks1993 

Let's say you also want to exclude Meeting.

Change COUNTIF($D$5:$D$9,"Absent") to (COUNTIF($D$5:$D$9,"Absent")+COUNTIF($D$5:$D$9,"Meeting"))

and COUNTIF($D$5:$D$9,"<>Absent") to

COUNTIFS($D$5:$D$9,"<>Absent",$D$5:$D$9,"<>Meeting")

You can add other options the same way.

You're amazing, Hans! Thank you so much!

@Hans Vogelaar 

Hello, Hans. I got another newbie roadblock. I'd like to set up a workbook in such a way that when I select a month from a dropdown in cell x, the dates along with the values I keyed in every day of those months will autopopulate accordingly. How can I go about doing that? Thank you so much in advance. 

Screenshot_20220812-114937_Office.jpg

@CloversBooks1993 

In B3:

=DATEVALUE(("1-"&A2&YEAR(TODAY())))

In C3:

=IF(B3<EOMONTH($B$3,0),B3+1,"")

Fill to the right to AF3.

Apply the custom number format mmm-dd to B3:AF3.

 

@Hans Vogelaar 

Thank you, Hans. What if I have a data list of all months of the year in A2, and I want to see the records I have in August so I select August right. But I also would like to view September records automatically when I select the September from A2 dropdown. Im not sure if that's a macro. But how can I make it function like that? Im not sure if Im being clear. Haha. I wish I am. Thank you again!

@CloversBooks1993 

My previous reply will automatically fill the days of the month you enter in A2. So if you enter August, it will enter Aug-01 to Aug-31. If you enter September, it will enter Sep-01 to Sep-30.

It doesn't fill any "records" since I don't know how that would work.

Oh, I see. Thank you so much, again and again, Hans.