I've been looking for a almost two hours on how to do this, and everything I've found is all about Dates, or has me using the AutoFill/Pull Down function. I'm not working with dates, I'm building a list of "Intake Numbers" for a wildlife rehabilitation. When they get animals in, they record date and time, species, and a bunch of other information. However, there are multiple locations and people doing intake. There's not reliable means of issuing the state required Intake Number without potential overlap while the animals are being processed in.
What I need is a formula that, at the end of each day, when the daily total number of intakes is put into A1, it generates a sequence with Text in the beginning (), the year, which is listed in another cell, K1, and the Quarter (M1), listed numerically. If A1 is 35, then the formula will generate 35 rows of H1-K1-M1[Q1= "1", Q2= "2", Q3= "3". Q4= "4")-01[through whatever number is listed in A1]. I have filled out the Mini Sheet below with the information as it will appear, basically, on the form.
Here's the code I've used to get where I'm at, but it doesn't list leading zero(s), which I need, since it looks like it's starting at "11"/:21/31/41 instead of "101"/"201"/"301"/"401", and it looks like we've missed about 100 animals when we get past the ninth intake...
=IFS($M$1="Q1",(H1&"-"&K1&"-1"&SEQUENCE(A1)),
$M$1="Q2",(H1&"-"&K1&"-2"&SEQUENCE(A1)),
$M$1="Q3",(H1&"-"&K1&"-3"&SEQUENCE(A1)),
$M$1="Q4",(H1&"-"&K1&"-4"&SEQUENCE(A1)))
(Yes, I know I write my formulas all spaced out and big! I'm very dyslexic, though, and this is the only way I can actually manage to read them!)
I appreciate your help!
What I need is a formula that, at the end of each day, when the daily total number of intakes is put into A1, it generates a sequence with Text in the beginning (), the year, which is listed in another cell, K1, and the Quarter (M1), listed numerically. If A1 is 35, then the formula will generate 35 rows of H1-K1-M1[Q1= "1", Q2= "2", Q3= "3". Q4= "4")-01[through whatever number is listed in A1]. I have filled out the Mini Sheet below with the information as it will appear, basically, on the form.
Here's the code I've used to get where I'm at, but it doesn't list leading zero(s), which I need, since it looks like it's starting at "11"/:21/31/41 instead of "101"/"201"/"301"/"401", and it looks like we've missed about 100 animals when we get past the ninth intake...
=IFS($M$1="Q1",(H1&"-"&K1&"-1"&SEQUENCE(A1)),
$M$1="Q2",(H1&"-"&K1&"-2"&SEQUENCE(A1)),
$M$1="Q3",(H1&"-"&K1&"-3"&SEQUENCE(A1)),
$M$1="Q4",(H1&"-"&K1&"-4"&SEQUENCE(A1)))
(Yes, I know I write my formulas all spaced out and big! I'm very dyslexic, though, and this is the only way I can actually manage to read them!)
I appreciate your help!
| Book1 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | 035 | Fish and Game Conservation Wildlife Rehabilitation Permit Quarterly Report | PERM-42710 | 2023 | Q1 | |||||||||||
| 2 | Species | Unique Identification | Age | Sex | Intake Date | Intake Method | Collection County | Collection Latitude | Collection Longitude | Collection Address | Facility Number of Origin | Unique Identification2 | Disposition Date | Disposition Method | ||
| 3 | Virginia Opossum | PERM-42710-2023-11 | ADULT | MALE | 1/1/23 | PUBLIC TURN IN | DADE | PERM-42710 | N/A | 2/28/2023 | RELEASE | |||||
| 4 | PERM-42710-2023-12 | |||||||||||||||
| 5 | PERM-42710-2023-13 | |||||||||||||||
| 6 | PERM-42710-2023-14 | |||||||||||||||
| 7 | PERM-42710-2023-15 | |||||||||||||||
| 8 | PERM-42710-2023-16 | |||||||||||||||
Sheet1 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B3:B37 | B3 | =IFS($M$1="Q1",(H1&"-"&K1&"-1"&SEQUENCE(A1)), $M$1="Q2",(H1&"-"&K1&"-2"&SEQUENCE(A1)), $M$1="Q3",(H1&"-"&K1&"-3"&SEQUENCE(A1)), $M$1="Q4",(H1&"-"&K1&"-4"&SEQUENCE(A1))) |
| Dynamic array formulas. | ||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| H3:H37 | Whole number | between 25 and 36 |
| I3:I37 | Whole number | between -107 and -93 |
| J3:J37 | Any value | |
| K3:K37 | Any value | |
| L3:L37 | Any value | |
| M3:M37 | Date | between 1/1/2015 and 12/31/2025 |
| B3:B37 | Any value | |
| C3 | Any value | |
| D3 | Any value | |
| E3:E37 | Date | between 1/1/2010 and 12/31/2025 |
| C4:C37 | List | Adult,Young,Unknown |
| D4:D37 | List | Female,Male,Unknown |
