tianzl66/Sheetpedia huggingface dataset:
Spreadsheet: 730437d1619700807-average-non-continuous-cells-if-not-0-2021-22-tracker-copy
Sheet: C3 E (Gra)

 | Class Attendance & Effort
 | Teacher |  |  |  |  |  |  |  |  |  |  | Course |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | S month
 | Room |  |  |  |  |  |  |  |  |  |  | Period/Time |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Year
 | Pupils |  |  |  |  |  | Test name |  |  | Test name |  |  | Test name |  |  | Test name |  |  | Test name |  |  | Test name |  |  | Test name |  |  | Test name |  |  | Test name |  |  | Test name |  |  | Totals
 |  |  |  |  |  |  | Out of |  | 40 | Out of |  | ? | Out of |  | ? | Out of |  | ? | Out of |  | ? | Out of |  | ? | Out of |  | ? | Out of |  | ? | Out of |  | ? | Out of |  | ?
 | ID | Forename | Surname | Midys/additional | SEN | Target | M | % | Gra | /M | % | Gra | /M | % | Gra | /M | % | Gra | /M | % | Gra | /M | % | Gra | /M | % | Gra | /M | % | Gra | /M | % | Gra | /M | % | Gra | Average | On-track
 | 1 |  |  |  |  |  |  |  |  |  | 0 | 8 |  | 0 | 4 |  | 0 | 0 |  | 0 | 0 |  | 0 | 8 |  | 0 | 0 |  | 0 | 6 |  | 0 | 0 |  | 0 | 0
 | 2
 | 3
 | 4
 | 5
 | 6
 | 7
 | 8
 | 9
 | 10
 | 11
 | 12
 | 13
 | 14
 | 15
 | 16
 | 17
 | 18
 | 19
 | 20
 | 21
 | 22
 | 23
 | 24
 | 25
 | 26
 | 27
 | 28
 | 29
 | 30
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME] a new row ABOVE this one: Right-click on the row number, select [NAME], then press Ctrl+d.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [42]
 |  |  |  |  |  |  | Bound |  | %
 |  |  |  |  |  |  |  | 9 | 0.9
 |  |  |  |  |  |  |  | 8 | 0.8
 |  |  |  |  |  |  |  | 7 | 0.7
 |  |  |  |  |  |  |  | 6 | 0.6
 |  |  |  |  |  |  |  | 5 | 0.5
 |  |  |  |  |  |  |  | 4 | 0.4
 |  |  |  |  |  |  |  | 3 | 0.3
 |  |  |  |  |  |  |  | 2 | 0.2
 |  |  |  |  |  |  |  | 1 | 0.1
 |  |  |  |  |  |  |  | 0 | 0
Spreadsheet: 683728d1592916869-formula-that-returns-a-value-under-specific-conditions-region-issuev4
Sheet: Sheet1

Entered Account # | Entered Date | Region (as returned from formula) |  |  |  |  | [NAME] that Returns a Value under Specific Conditions
ABC | 2018-02-01 00:00:00 |  |  |  |  |  | My problem is as below: I have some data that was dumped from a database and I need to return the "Region" from that data (for the "Entered Account #" and "Entered Date") under the following conditions: - IF "Void?" = "Y", THEN STOP, no need to look further.  But if record NOT voided, then: 1.  IF "Span Duration is = 365 or 366 and  2.  "Entered Date" is >= the "From Date" and  3.  "Entered Date" is <= the "To Date"THEN return "Region" from the "matched" row.  BUT only perform steps 1-3 when the "Entered Account #" is = the "Account #" from the Database Data.  Need to check all the date spans within that "Account #". That's Part 1. Part 2 is:  IF "Void?" is <> "Y" and "Span Duration" is < 365, THEN use the "Region" from the next row in the database data but ONLY if that next row is for the same "Account #"   I tried using all kinds of combinations of INDIRECT, ADDRESS, CELL, COLUMN, ROW, iNDEX and am not having any luck.   I can't use a macro for this and was wondering if it is able to be done with a formula. I'm probably/definitely biting off more than I can chew :) but any help you can provide will be hugely appreciated.  Thanks!
ABC | 2020-03-01 00:00:00
ABC | 2020-02-01 00:00:00 | Voided Record
DEF | 2018-06-30 00:00:00
ABC | 2019-02-01 00:00:00
DEF | 2019-02-01 00:00:00 | Voided Record
DEF | 2017-01-01 00:00:00
DEF | 2019-02-01 00:00:00
Database Data
Account # | Void? | Span Duration (Days) | From Date | To Date | Region
ABC | N |  | 2018-01-01 00:00:00 | 2018-12-31 00:00:00 | Boston
ABC | N |  | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | New York
ABC | N |  | 2020-01-01 00:00:00 | 2020-12-31 00:00:00 | Sacramento
ABC | Y |  | 2018-02-01 00:00:00 | 2018-01-01 00:00:00 | Tulsa
DEF | N |  | 2018-01-01 00:00:00 | 2018-12-31 00:00:00 | Wichita
DEF | N |  | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | Sarasota
DEF | Y |  | 2018-02-01 00:00:00 | 2018-01-01 00:00:00 | Reno
DEF | N |  | 2020-01-01 00:00:00 | 2020-12-31 00:00:00 | Denver
So sorry I was not clear….It means if there is a "Y" in the "Void?" column, that means the record is a voided record and no need to do steps 1, 2, and 3, i.e., you don't need to check the "Span Duration" or if the "Entered Date" falls between the "From Date" and "Date".  Basically, just return a value that says "Voided Record"
So to the differences between your results and mine, in rows 7 and 9:
For Row 7:  You are right….it should return Sarasota
For Row 9:  When you look at the Account # and Date in Row 9, the formula should look in the DEF records, and it finds that the "Entered Date" of 1-Feb-19 falls into the the range shown in row 18...so the value returned should also be "Region" Sarasota, the region on row 18
Spreadsheet: 826571d1682087005-map-a-table-kg-to-g-map-kg-to-g
Sheet: prices (2)

id | qty | expected_result | current_result
Al-100 | 50 | 1165 |  |  |  |  |  |  |  |  |  | Al-100 | 2023-04-17 00:00:00 | 30 | g | 1165 | USD
Al-100 | 100 | 1165 |  | 1165 |  |  |  |  |  |  |  | Al-100 | 2023-04-17 00:00:00 | 1000 | g | 922 | USD
[NAME] | 250 | 1200 |  | 1200 |  |  |  |  |  |  |  | [NAME] | 2023-04-17 00:00:00 | 30 | g | 1500 | USD
[NAME] | 500 | 1100 |  | 1100 |  |  |  |  |  |  |  | [NAME] | 2023-04-17 00:00:00 | 500 | g | 1100 | USD
[NAME] | 1000 | 865 |  | 865 |  |  |  |  |  |  |  | [NAME] | 2023-04-17 00:00:00 | 1000 | g | 865 | USD
Al-102 | 50 | 1000 |  | 1000 |  |  |  |  |  |  |  | Al-102 | 2023-04-17 00:00:00 | 30 | g | 1000 | USD
Al-102 | 100 | 1000 |  | 1000 |  |  |  |  |  |  |  | Al-102 | 2023-04-17 00:00:00 | 1000 | g | 830 | USD
Al-103 | 250 | 940 |  | 940 |  |  |  |  |  |  |  | Al-103 | 2023-04-17 00:00:00 | 30 | g | 940 | USD
Al-103 | 500 | 940 |  | 940 |  |  |  |  |  |  |  | [NAME] | 2023-04-17 00:00:00 | 100 | g | 1200 | USD
Al-104 | 1000 | 46 |  | 46 |  |  |  |  |  |  |  | Al-104 | 2023-04-17 00:00:00 | 1000 | g | 46 | USD
Al-105 | 50 | 33 |  | 33 |  |  |  |  |  |  |  | Al-105 | 2023-04-17 00:00:00 | 25000 | g | 33 | USD
Al-130 | 100 | 29 |  | 29 |  |  |  |  |  |  |  | Al-130 | 2023-04-17 00:00:00 | 25000 | g | 29 | USD
Al-111 | 250 | 71 |  | 71 |  |  |  |  |  |  |  | Al-111 | 2023-04-17 00:00:00 | 1000 | g | 71 | USD
Al-112 | 500 | 39 |  | 39 |  |  |  |  |  |  |  | Al-112 | 2023-04-17 00:00:00 | 1000 | g | 39 | USD
Al-113 | 1000 | 7 |  | 7 |  |  |  |  |  |  |  | AA7075-100 | 2023-04-17 00:00:00 | 500 | g | 130 | USD
Al-113 | 50 | 8 |  | 8 |  |  |  |  |  |  |  | Al-113 | 2023-04-17 00:00:00 | 1000 | g | 7 | USD
AA7075-100 | 100 | 120 |  | 130 |  |  |  |  |  |  |  | AA7075-100 | 2023-04-17 00:00:00 | 1000 | g | 120 | USD
AA7075-101 | 250 | 106 |  | 106 |  |  |  |  |  |  |  | AA7075-101 | 2023-04-17 00:00:00 | 1000 | g | 106 | USD
AA7075-102 | 500 | ALERT |  | ALERT |  |  |  |  |  |  |  | Al-113 | 2023-04-17 00:00:00 | 500 | g | 8 | USD
AA7075-103 | 1000 | ALERT |  | ALERT
AA7075-104 | 50 | ALERT |  | ALERT
Spreadsheet: 178656d1346862800-excel-2010-how-do-i-substitute-certain-words-in-a-certain-column-substitute-solution
Sheet: Sheet1

 |  |  |  |  |  |  |  |  | Replacement Table
 |  |  |  |  |  |  |  |  | Apple | Apples
 |  |  |  |  |  |  |  |  | [NAME] | Grapes
 | Apple |  |  |  |  |  | Apples
 | [NAME] |  |  |  |  |  | Grapes
 | Orange |  |  |  |  |  | Orange
 | Apple |  |  |  |  |  | Apples
 | [NAME] |  |  |  |  |  | Grapes
 | Orange |  |  |  |  |  | Orange
 | Apple |  |  |  |  |  | Apples
 | [NAME] |  |  |  |  |  | Grapes
 | Orange |  |  |  |  |  | Orange
 | Apple |  |  |  |  |  | Apples
 | [NAME] |  |  |  |  |  | Grapes
 | Orange |  |  |  |  |  | Orange
Spreadsheet: 76879d1277365809-reverse-calculation-reverse_calculation
Sheet: Sheet1

0 | 0
0.1 | 1
0.2 | 2
0.3 | 3
0.4 | 4
0.5 | 5
0.6 | Error
0.7 | Error
0.8 | Error
0.9 | Error
1 | 6
1.1 | 7
1.2 | 8
1.3 | 9
1.4 | 10
1.5 | 11
1.6 | Error
1.7 | Error
1.8 | Error
1.9 | Error
2 | 12
2.1 | 13
2.2 | 14
2.3 | 15
2.4 | 16
2.5 | 17
2.6 | Error
2.7 | Error
2.8 | Error
2.9 | Error
3 | 18
3.1 | 19
3.2 | 20
3.3 | 21
3.4 | 22
3.5 | 23
3.6 | Error
3.7 | Error
3.8 | Error
3.9 | Error
4 | 24
4.1 | 25
4.2 | 26
4.3 | 27
4.4 | 28
4.5 | 29
4.6 | 30
4.7 | Error
4.8 | Error
4.9 | Error
5 | 30
5.1 | 31
5.2 | 32
5.3 | 33
5.4 | 34
5.5 | 35
5.6 | 36
5.7 | Error
5.8 | Error
5.9 | Error
6 | 36
6.1 | 37
6.2 | 38
6.3 | 39
6.4 | 40
6.5 | 41
6.6 | 42
6.7 | Error
6.8 | Error
6.9 | Error
7 | 42
7.1 | 43
7.2 | 44
7.3 | 45
7.4 | 46
7.5 | 47
7.6 | 48
7.7 | Error
7.8 | Error
7.9 | Error
8 | 48
8.1 | 49
8.2 | 50
8.3 | 51
8.4 | 52
8.5 | 53
8.6 | 54
8.7 | Error
8.8 | Error
8.9 | Error
9 | 54
9.1 | 55
9.2 | 56
9.3 | 57
9.4 | 58
9.5 | 59
9.6 | 60
9.7 | Error
9.8 | Error
9.9 | Error
10 | 60
10.1 | 61
10.2 | 62
10.3 | 63
10.4 | 64
10.5 | 65
10.6 | 66
10.7 | Error
10.8 | Error
10.9 | Error
11 | 66
11.1 | 67
11.2 | 68
11.3 | 69
11.4 | 70
11.5 | 71
11.6 | 72
11.7 | Error
11.8 | Error
11.9 | Error
12 | 72
12.1 | 73
12.2 | 74
12.3 | 75
12.4 | 76
12.5 | 77
12.6 | 78
12.7 | Error
12.8 | Error
12.9 | Error
13 | 78
13.1 | 79
13.2 | 80
13.3 | 81
13.4 | 82
13.5 | 83
13.6 | 84
13.7 | Error
13.8 | Error
13.9 | Error
14 | 84
14.1 | 85
14.2 | 86
14.3 | 87
14.4 | 88
14.5 | 89
14.6 | 90
14.7 | Error
14.8 | Error
14.9 | Error
15 | 90
15.1 | 91
15.2 | 92
15.3 | 93
15.4 | 94
15.5 | 95
15.6 | 96
15.7 | Error
15.8 | Error
15.9 | Error
16 | 96
16.1 | 97
16.2 | 98
16.3 | 99
16.4 | 100
16.5 | 101
16.6 | Error
16.7 | Error
16.8 | Error
16.9 | Error
17 | 102
Spreadsheet: 454886d1460017810-formula-to-create-store-by-store-inventory-transfers-excel-forum-store-transfer-generation-spreadsheet-v2
Sheet: Sheet1 (2)

 | Shop 1 | Shop 2 | Shop 3 | Shop 4 | Shop 5 | Shop 6 | Shop 7 | Shop 8 | Shop 9
Product 1 | -1 | -1 | -1 | -1 | 2 |  | 1 |  | 1
Product 2 |  | -1 | 1 | -1 | 1
Product 3 | 1 | 1 |  | 1 | -1 | -1 |  | -1
Product 4 |  |  |  |  |  | 1 | -1 | 1 | -1
Note : a negative number means the store is expecting the item from another store.  A positive number means the item need to be transferred out to another store.
Output Required
Shop 1 | Product 1 | Expect a delivery of this item
 | Product 2
 | Product 3 | Transfer this item to Shop 5
 | Product 4
Shop 2 | Product 1 | Expect a delivery of this item
 | Product 2 | Expect a delivery of this item
 | Product 3 | Transfer this item to Shop 6
 | Product 4
Shop 3 | Product 1 | Expect a delivery of this item
 | Product 2 | Transfer this item to Shop 2
 | Product 3
 | Product 4
Shop 4 | Product 1 | Expect a delivery of this item
 | Product 2 | Expect a delivery of this item
 | Product 3 | Transfer this item to Shop 8
 | Product 4
Shop 5 | Product 1 | Transfer this item to Shop 1 and Shop 2
 | Product 2 | Transfer this item to Shop 4
 | Product 3 | Expect a delivery of this item
 | Product 4
Shop 6 | Product 1
 | Product 2
 | Product 3 | Expect a delivery of this item
 | Product 4 | Transfer this item to Shop 7
Shop 7 | Product 1 | Transfer this item to Shop 3
 | Product 2
 | Product 3
 | Product 4 | Expect a delivery of this item
Shop 8 | Product 1
 | Product 2
 | Product 3 | Expect a delivery of this item
 | Product 4 | Transfer this item to Shop 9
Shop 9 | Product 1 | Transfer this item to Shop 4
 | Product 2
 | Product 3
 | Product 4 | Expect a delivery of this item
Spreadsheet: 799516d1665129597-generate-list-of-names-based-on-criteria-sample
Sheet: FOLDER 2.1

FOLDER 2.1 - MASTERLIST OF PRE-SCHOOL CHILDREN
HH NO. | ADDRESS | NAME OF MOTHER | NAME OF CHILD | BELONG TO IP GROUP | GENDER | DATE OF BIRTH | DATE OF MEASUREMENT | AGE IN MONTHS | WEIGHT | HEIGHT | [NAME] | L/HFA STATUS | WFL/H STATUS
 |  |  |  |  |  |  |  |  | (KG) | (CM)
COLUMN C | FOLDER 0.1 COLUMN B | COLUMN O | COLUMN G | COLUMN V | COLUMN Q | COLUMN J
Spreadsheet: 744389d1629213818-extract-fields-based-on-current-date-and-month-sample-workbook-with-expected-output-4-
Sheet: Expected Output

 | Expected Output |  |  |  |  |  |  | 2021-08-01 00:00:00 |  | 2021-09-01 00:00:00
 | Preparer | Backup | [NAME] | Activity Name | Frequency | Rotation Cycle Time Frame | Due Date
Spreadsheet: 888636d1739208762-pulldown-list-expenses
Sheet: Waste of Money

 | 865.92 | 2012.57
 | 9869.29 | 12
 | 5626.3 |  | First Year Rent Cost
 | 626.96
Total Intrest Paid |  | 60000 | Extra Mortgage Cost | 50000 | Shan Emergency
Months | 37 |  |  |  | Total Money Wasted
 |  |  | Total Cost for 37 Months
Days | 30 | 37 | Months
 |  |  | Total Monthly Cost
 |  | 30 | Days
 |  |  | Total Daily Cost
 |  | 24 | Hours
 |  |  | Total Hourly Cost
 |  | 60 | Minutes
 |  |  | Total Minute Cost
Spreadsheet: 298498d1392822868-how-to-add-a-custom-function-to-subtotals-sample_share
Sheet: Sample

 |  | 2013 |  |  | 2014 |  |  | 2015
Region | Part Nbr | Nbr Purchased | [NAME] | % Purchased | Nbr Purchased | [NAME] | % Purchased | Nbr Purchased | [NAME] | % Purchased
1 | 100101
 | 111001 | 15 | 17 | 88.23529411764706
 | 111201
 | 111690
 | 111501 | 23 | 25 | 92
 | 111681 | 6 | 25 | 24
 | 111792 | 23 | 25 | 92
 | 111680 | 1 | 25 | 4
 | 111790 | 1 | 25 | 4
 | 116780 | 1 | 20 | 5
 | 116801
 | 116902
 | 117081
 | 117190
 | 117080 | 1 | 25 | 4
 | 119901
 | 120090 | 25 | 25 | 100
 | 120191 | 25 | 25 | 100
 | 121001 | 6 | 8 | 75
 | 130501
 | 130690
Region 1 Total
2 | 110155 | 23 | 24 | 95.83333333333334
 | 110256 | 23 | 24 | 95.83333333333334
Region 2 Total
3 | 111001 | 23 | 24 | 95.83333333333334
 | 111102 | 23 | 24 | 95.83333333333334
 | 111203 | 24 | 24 | 100
 | 111304
 | 111490
 | 121001 | 25 | 24 | 104.1666666666667
 | 121102 | 21 | 24 | 87.5
 | 121290 | 18 | 24 | 75
 | 211001 | 10 | 20 | 50
Region 3 Total
This is just a portion of the spreadsheet--enough to illustrate the problem.
The Subtotals feature on the Data tab of the Excel ribbon was used to create the subtotal rows.
Objective:
Would like to automate the percentage calculations within the subtotals row.
The percentage formulas above were entered  manually, one at a time, within each of the subtotal rows.
Spreadsheet: 49057d1248342127-keeping-track-of-payments-test
Sheet: J11el

 |  |  |  | Job Name
 |  |  |  | Plot No./Type | 2
 |  |  |  | Labour Times |  | Plot / Type | Labour price
 |  |  |  |  | hrs | Joists 1st floor
 |  |  |  |  | hrs | Joists 2nd floor
 |  |  |  |  | hrs | Roof
 |  |  |  |  | hrs | Low Level
 |  |  |  |  | hrs | Garage Roof
 |  |  |  |  | hrs | First Fix
 |  |  |  |  | hrs | External doors
 |  |  |  |  | hrs | Second Fix
 |  |  |  |  | hrs | Final fix
 |  |  |  |  | hrs | Snag
 |  |  |  |  | hrs | Total
 |  |  |  | Price Quote for plot/type | Total | Profit | Fixings
 |  |  |  | Joists 1st floor
 |  |  |  | Joists 2nd floor
 |  |  |  | Roof
 |  |  |  | Low Level
 |  |  |  | Garage Roof
 |  |  |  | First Fix
 |  |  |  | External doors
 |  |  |  | Second Fix
 |  |  |  | Final fix
 |  |  |  | Snag
 |  |  |  | Extra add on's (see notes)
 |  |  |  | Total
Notes
Job Name |  |  |  |  | Plot No.
Gr | 1st | 2nd | Qty | Item | labour total | my total | fixings | Time
67 |  |  |  | Joist & Decking TJI psm 1st floor
 |  |  |  | Total Joist 1st floor
 |  |  |  | Joist & Decking TJI psm 2nd floor
 |  |  |  | Total Joist 2nd floor
 |  |  |  | Trusses psm
 |  |  |  | Pediment [NAME]
2 |  |  |  | Dormer Hand cut
 |  |  |  | [NAME]made Fibreglass
67 |  |  |  | Attic Trusses psm
3 |  |  |  | Velux
 |  |  |  | Valley pr/m Trusses
 |  |  |  | Hip pr/m
 |  |  |  | Hand cut section
 |  |  |  | Total Roof
3 |  |  |  | Low Levels
 |  |  |  | Garage Roof
1 | 1 |  |  | Stairs -Straight Flight
 |  |  |  | Stairs -Kite over Garage wall
 |  |  |  | Stairs-Double kite
 |  |  |  | Stairs-Kite
3 |  |  |  | Block casing
2 | 6 | 3 |  | [NAME] casing
1 | 1 | 1 |  | Plasterboard catch noggins(per floor)
1 | 2.7 |  |  | Soil boxing
 |  |  |  | Metal Studding pm
10 | 27 | 20 |  | Studding pm
4 | 6 | 2 |  | Window board
 |  |  |  | Temp handrail
 |  |  |  | Protect Stairs
 |  |  |  | Total 1st Fix
2 |  |  |  | Front/Back Door & Frame & Lock
3 | 5 | 3 |  | Fire door
2 | 1 |  |  | Standard door
60 | 69 | 40 |  | Skirting
 | 6 | 2 |  | Pipe Boxings p/m
 |  |  |  | Furniture
 |  |  |  | Arcs
 |  |  |  | Drilling and cutting latches
 |  |  |  | Strings hand rails, spindles per flight
 |  |  |  | Total 2nd Fix
 | 2 | 0.5 |  | Bath/Shower Panel
 |  | 1 |  | [NAME]
 |  |  |  | [NAME]
 |  |  |  | Door furniture
 |  |  |  | Intumescent strips
 |  |  |  | Re-hang doors
 |  |  |  | Total Final Fix
 |  |  |  | Snag price
Spreadsheet: 42278d1240882564-help-needed-for-a-vlookup-issue-book1
Sheet: Sheet2

lastname | Firstname | Alias
Abbot | [NAME] | SMH15197351
Abbott | Cecelia | [NAME]
Abbott | [NAME] | JABBOT0195636
[NAME] | Degu | dabebe0197169
Abhary | Sotoodeh | DH31501296325
[NAME] | Nina | NABRAH0196458
Accounts | Som | somacc0196680
Ackland | [NAME] | DHC20034697654
Adair | Kirstie | KADAIR0197967
Adamczyk | Wella | wadamc0195426
Adams | June | DH10091995633
Adams | [NAME] | sadamd96636
Adams | Trevor | TADAMS0194322
[NAME] | Penelope | PAdams0196732
[NAME] | [NAME]. | DHC20005597902
see how for doroth abbot, it has her username smh151, then numbers
need a lookup thingo in column d, which shows the las logon date, from this area.
oh, there is a lot of other information here that I have deleted, on sheet, 2.
Spreadsheet: 806920d1669668615-deduct-dates-drop-down-list-test-spreadsheet
Sheet: TEST SHEET

KEY: |  | APPROVED |  |  | LOW | MEDIUM | HIGH
 |  | SUBMITTED |  |  | MINUS 14 DAYS | MINUS 30 DAYS | MINUS 60 DAYS
 |  | TO BE SUBMITTED
 |  | REJECTED
NO | [NAME] | STATUS | PRIORITY | DUE DATE | SUBMISSION DATE |  |  |  |  |  |  |  | [NAME] | START DATE | END DATE
1 | 22 | APPROVED | LOW |  |  |  |  |  |  |  |  |  |  | 2022-12-12 00:00:00 |  | When selecting the drop down list in column D, when I select the priority (low, med high) I need the durations noted in rows 4 to be subtracted by start date in column O and inputed into column E
Spreadsheet: 210095d1359380970-status-value-dependant-upon-data-in-certain-cells-excel-query
Sheet: Sheet1

Status | Date Account Created | Date Documents Submitted | Date Documents Received | Documents are completed | Provisional Date | [NAME] | File Complete
Account Created | 2013-01-01 00:00:00
Documents Submitted | 2013-01-01 00:00:00 | 2013-01-02 00:00:00
Documents Received | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00
Documents Completed | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes
Provisional Date | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes | 2013-01-05 00:00:00
[NAME] | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes | 2013-01-05 00:00:00 | Yes
File Complete | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes | 2013-01-05 00:00:00 | Yes | Yes
 | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes | 2013-01-05 00:00:00 | Yes | Yes
 | 2013-01-01 00:00:00
 | 2013-01-01 00:00:00 | 2013-01-02 00:00:00
 | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00
 | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes
 | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes | 2013-01-05 00:00:00
 | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes | 2013-01-05 00:00:00 | Yes
 | 2013-01-01 00:00:00 | 2013-01-02 00:00:00 | 2013-01-03 00:00:00 | Yes | 2013-01-05 00:00:00 | Yes | Yes
Spreadsheet: 65467d1266186238-event-result-sheets-event-example
Sheet: Team

Team |  | Event 1 | Event 2 | Event 3 | Event 4 | Event 5 | Event 6 | Event 7 | Event 8 | Event 9 | Total Points | Place
School A
School B
School C
School D
School E
School F
School G
This sheet is partially designed to be printed for coaches to see.  A such, we have a couple of needs.
Team Points are awarded based on the event sheets.
Each team has two rows.  The top row is to show people the math used to determine total points for each event.
 | For instance, if School A had two contestants with prelim points and 1 in finals,
 | they might have the following team points:           4,  2,  and 6
 | The top row needs to show all of these numbers, NOT ADDED together.
 | Something like:   "4+2+6"  ;  "4, 2, 6"  ;  or even "4 2 6"
The bottom row will be used to add the points for each event together.  We will do this manually as we would like to have
a personal touch at the end.  In this case, we would type "12"
Colors and boarders are not important if you destroy them in the response.  They are just to help with visual clarity
Example:
Team | Code | Event 1 | Event 2 | Event 3 | Total
School A | A | 4+2+6 | 2+3 | 1+6
 |  | 12 | 5 | 7
Spreadsheet: 103668d1301406610-look-up-and-combine-like-this-maybe
Sheet: Sheet2

1.3.2 | Catalog Manager | 1.3.2-1 |  |  |  |  | Catalog Manager;
1.3.2 | Marketing Manager | 1.3.2-2 |  |  |  |  | Marketing Manager;
1.3.3 | Pricing Manager | 1.3.3-1 |  |  |  |  | Pricing Manager;
1.3.4 | Catalog Manager | 1.3.4-1 |  |  |  |  | Catalog Manager;
1.3.4 | Marketing Manager | 1.3.4-2 |  |  |  |  | Catalog Manager 2;
1.3.4 | Pricing Manager | 1.3.4-3 |  |  |  |  | Marketing Manager 2
1.3.8 | Catalog Manager | 1.3.8-1
1.3.8 | Marketing Manager | 1.3.8-2
1.3.8 | Pricing Manager | 1.3.8-3
1.3.8 | Catalog Manager | 1.3.8-4
1.4.9 | Marketing Manager | 1.4.9-1
1.4.9 | Pricing Manager | 1.4.9-2
1.3.8 | Catalog Manager 2 | 1.3.8-5
1.3.8 | Marketing Manager 2 | 1.3.8-6
1.5.1 | Pricing Manager | 1.5.1-1