Lookup today's date in a range and return value of an adjacent cell based on a certain criteria on that date

I

IBLAKExI

New Member
Joined
Sep 13, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Good day,

I'm pretty new to formulas in excel and I learn best by dissecting and experimenting. Really hoping that someone can help me with this.

I want to reference the value of a cell for the current day that has a particular category.

In this case I have the Daily_Database sheet and I want to have the value shown on another sheet, Sheet2.

The B column contains the date, C column the category I want to reference when looking up the total value in the G column.

I greatly appreciate any help provided.

1694609422540.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
index(G:G, match(cell with date to match&cell with category to match , B:B&C:C,0))

you will need to add the sheetname

index(sheetname!G:G, match(cell with date to match&cell with category to match , sheetname!B:B&sheetname!C:C,0))

but rather than an image
Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
etaf said:
index(G:G, match(cell with date to match&cell with category to match , B:B&C:C,0))

you will need to add the sheetname

index(sheetname!G:G, match(cell with date to match&cell with category to match , sheetname!B:B&sheetname!C:C,0))

but rather than an image
Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
Click to expand...
Tried working with XL2BB for a moment and figured it'd be faster for me to just share a OneDrive file.

Please let me know if this helps. Thanks

1drv.ms

Daily Recap - Assist.xlsx

Microsoft Excel Workbook
1drv.ms
 
Upvote 0
ok, i have the file - where do you expect the results to be displayed ?

the highlighted cells - are cat = Vessel
But whats your date criteria

are you just looking for a total of G UNITS - so a SUMIFS() will do

i'm not clear on what you are after now or where you want it put

if the date is not important , then just Vessel
with 365 version
you can use
=FILTER(Daily_Database!B3:U100,(Daily_Database!C3:C100="vessel"))
but its easy to add a date range if required

Or is it just a total of column G ???? SUMIF() would do that - but not clear on what you need

Daily Recap - Assist.ods
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1categoryindex(sheetname!G:G, match(cell with date to match&cell with category to match , sheetname!B:B&sheetname!C:C,0))
2vessel45163Vessel0000000ABCD-0001NZ00451630.3333333336431467800
345163Vessel0000000ABCD-0001NZ00451630.3333333316616833400
445166Vessel0000000ABCD-0001NZ00451660.333333337407400
545168Vessel0000000ABCD-0001NZ00451680.541666671006716700
645168Vessel0000000ABCD-0001NZ00451680.541666671211212400
745176Vessel0000000SAMR-335SSAES00451760.3333333378169400
845176Vessel0000000SALB-335NSAEN00451760.333333339509500
945176Vessel0000000CAHE-0013WUSAX00451760.333333332602600
1045176Vessel0000000PBRA-336SNAE 200451760.7916666713738600
1145177Vessel0000000IFUT-910EBICL651451770.3333333336230066200
1245177Vessel0000000TUCA-3123SNZ00451770.3333333314513728200
1345178Vessel0000000SEXP-045WEC2004517819:00 (Standby)356669102500
Sheet1
Cell Formulas
RangeFormula
C2:V13C2=FILTER(Daily_Database!B3:U100,(Daily_Database!C3:C100=B2))
Dynamic array formulas.


i only leave on dropbox for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
 
Upvote 0
looking at the title

Lookup today's date in a range and return value of an adjacent cell based on a certain criteria on that date​

but there are no listing for vessel for today() date

is that what you want to be able to do
=FILTER(Daily_Database!B3:U100,(Daily_Database!C3:C100=B4)*(Daily_Database!B3:B100=TODAY()))

Daily Recap - Assist - ETAF.ods
ABCDEFGHIJKLMNOPQRSTU
1Lookup today's date in a range and return value of an adjacent cell based on a certain criteria on that date
2
3
4vessel9/13/23Vessel0000000test1test2test3test4test5test6test7test8test90
5
6
Sheet2
Cell Formulas
RangeFormula
C4:V4C4=FILTER(Daily_Database!B3:U100,(Daily_Database!C3:C100=B4)*(Daily_Database!B3:B100=TODAY()))
Dynamic array formulas.


I added a new entry in the database - with =today() and vessel and then just TEST - we can lookup a specific column only as well

see sheet2

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
 
Upvote 0
etaf said:
looking at the title

Lookup today's date in a range and return value of an adjacent cell based on a certain criteria on that date​

but there are no listing for vessel for today() date

is that what you want to be able to do
=FILTER(Daily_Database!B3:U100,(Daily_Database!C3:C100=B4)*(Daily_Database!B3:B100=TODAY()))

Daily Recap - Assist - ETAF.ods
ABCDEFGHIJKLMNOPQRSTU
1Lookup today's date in a range and return value of an adjacent cell based on a certain criteria on that date
2
3
4vessel9/13/23Vessel0000000test1test2test3test4test5test6test7test8test90
5
6
Sheet2
Cell Formulas
RangeFormula
C4:V4C4=FILTER(Daily_Database!B3:U100,(Daily_Database!C3:C100=B4)*(Daily_Database!B3:B100=TODAY()))
Dynamic array formulas.


I added a new entry in the database - with =today() and vessel and then just TEST - we can lookup a specific column only as well

see sheet2

Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
Click to expand...
I'm looking to setup a dashboard and pull a specific cells value based on the current day and the category.

In the explanation below I'll just say I'm looking for the value of imports/exports in column S for anything showing category vessel on the current day.

So I have September 13, 2023 / Category= Vessel / Then it will pull the value from column S for that date's vessel.

Hopefully this will help.
 
Upvote 0
ok,
=FILTER(Daily_Database!B3:U100,(Daily_Database!C3:C100=B4)*(Daily_Database!B3:B100=TODAY()))
will pull all the data
if all you need is column S

=FILTER(Daily_Database!S3:S100,(Daily_Database!C3:C100="vessel")*(Daily_Database!B3:B100=TODAY()))

OR
=INDEX(Daily_Database!S:S,MATCH(TODAY()&B2,Daily_Database!B:B&Daily_Database!C:C,0))


Daily Recap - Assist - ETAF.xlsx
ABCDE
1FilterLookup
2vessel1212
Sheet2
Cell Formulas
RangeFormula
C2C2=FILTER(Daily_Database!S3:S100,(Daily_Database!C3:C100="vessel")*(Daily_Database!B3:B100=TODAY()))
E2E2=INDEX(Daily_Database!S:S,MATCH(TODAY()&B2,Daily_Database!B:B&Daily_Database!C:C,0))


Daily Recap - Assist - ETAF.xlsx
ABCDEFGHIJKLMNOPQRST
1Daily Operations Database
2DateCategoryRail VisitCarsWellsUnitsWave 1Wave 2ICL UnitsVes RefServiceQCXMWXVes ETAVes Ops StartImportsExportsTotalsComments
3August 24, 2023Inbound RailEQC00752-ILM7254916IPRI-908EBFriday, August 25, 20238:00
4August 24, 2023Outbound RailMWE2023082444
5August 24, 2023Fumigation1
6August 25, 2023VesselABCD-0001NZFriday, August 25, 20238:00364314678
7August 25, 2023VesselABCD-0001NZFriday, August 25, 20238:00166168334
8August 25, 2023Inbound RailEQC00753-ILM7173515IPRI-908EB46Friday, August 25, 20238:00
9August 25, 2023Chiller(2%)2
10August 25, 2023Outbound RailWQC00761-ILM40+65IPRI-908EB65Friday, August 25, 20238:00
11August 25, 2023Vacis11
12August 26, 2023Inbound RailEQC00754-ILM2813
13August 28, 2023VesselABCD-0001NZMonday, August 28, 20238:0074074
14August 29, 2023Vacis2
15August 30, 2023VesselABCD-0001NZWednesday, August 30, 202313:0010067167
16August 30, 2023VesselABCD-0001NZWednesday, August 30, 202313:0012112124
17September 5, 2023Chiller(2%)2
18September 5, 2023Vacis4
19September 5, 2023Inbound RailEQC00760-ILM611
20September 5, 2023Inbound RailEQC00761-ILM317
21September 5, 2023Inbound RailEQC00762-ILM740
22September 5, 2023Inbound RailEQC00763-ILM48
23September 5, 2023Outbound RailWQC00772-ILM17170
24September 7, 2023VesselSAMR-335SSAESThursday, September 07, 20238:00781694
25September 7, 2023VesselSALB-335NSAENThursday, September 07, 20238:0095095
26September 7, 2023VesselCAHE-0013WUSAXThursday, September 07, 20238:0026026
27September 7, 2023Vacis2
28September 7, 2023VesselPBRA-336SNAE 200Thursday, September 07, 202319:00137386
29September 8, 2023VesselIFUT-910EBICL651Friday, September 08, 20238:00362300662
30September 8, 2023VesselTUCA-3123SNZ0Friday, September 08, 20238:00145137282
31September 8, 2023Inbound RailEQC00767-ILM10285428IFUT-910EBFriday, September 08, 20238:00
32September 8, 2023Outbound RailWQC00775-ILM0
33September 8, 2023Outbound RailMWE202309080
34September 9, 2023VesselSEXP-045WEC2Saturday, September 09, 202319:00 (Standby)3566691025
35September 11, 2023Inbound RailEQC00768-ILM180
36September 11, 2023Inbound RailEQC00769-ILM7310
37September 11, 2023Inbound RailEQC00770-ILM6370
38September 11, 2023Vacis40
39September 11, 2023Chiller(2%)10
40September 11, 2023Outbound RailWQC00778-ILM19190
41September 11, 2023Outbound RailMWE202309110440All inbound and outbound for 9/11 totals out to 139 units. Need to have all drivers focus on rail.
42September 13, 2023Outbound RailWQC00780-ILM88002 x 20's and 6 x 40's
43September 13, 2023PWCS6510
44September 13, 20230
45September 13, 2023Vesseltest1test2test3test4test5test6test7test812
46
47
48
49
Daily_Database
Cell Formulas
RangeFormula
S6:S7,S34:S44,S28:S30,S24:S26,S15:S16,S13S6=SUM(Daily_Database!$Q6+Daily_Database!$R6)
B45B45=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:F46,J3:S46Expression=AND($C3="PWCS")textYES
D3:F46,H3:S46Expression=AND($C3="Chiller(2%)")textYES
M3:N5,S3:S10,M8:N9,N10,M11:N13,N14:N17,S18,M18:N22,N27:N44,S46Expression=AND($C3="Inbound Rail")textYES
M3:N5,S7:S10,M8:N9,N10,M11:N13,N14,S18,M18:N22,N27:N33,N35:N44,S46Expression=AND($C3="Vacis")textYES
M3:N5,S7:S8,M8:N9,N10,M11:N13,N14,S18,M18:N22,N27:N33,N35:N44,S46Expression=AND($C3="Vessel")textYES
D3:J46Expression=AND($C3="Vessel")textYES
H3:L46,O3:R44,M45:S45,O46:P46Expression=AND($C3="Outbound Rail")textYES
O3:P44,J3:K14,J18:K33,K34:K35,J36:K46Expression=AND($C3="Vacis")textYES
J3:K14,O3:O44,J18:K33,K34:K35,J36:K46Expression=AND($C3="Vessel")textYES
P3:P44Expression=NOT(ISERROR(SEARCH("Standby",P3)))textYES
P3:P44Expression=AND($C3="Vessel")textYES
D3:F46Expression=AND($C3="Vacis")textYES
D3:G46Expression=AND($C3="Vessel")textYES
C1:C2,C47:C1048576Expression=NOT(ISERROR(SEARCH("Vessel",C1)))textYES
H3:I46,L6:N7,L10:M10,L14:M17,Q16:Q17,R17:S17,S19:S45,L23:N26,Q23:Q44,L27:M44,M45:N45,S14:S16,N46Expression=AND($C3="Inbound Rail")textYES
L6:L7,L10,L23:L44,Q23:Q44Expression=AND($C6="Vacis")textYES
L6:L7,L10,L23:L44,Q23:Q44Expression=AND($C6="Vessel")textYES
H3:I46,M6:N7,M10,L14:M14,S14:S16,J15:N17,Q16:Q17,R17:S17,S19:S45,M23:N26,M27:M33,M34:N34,M35:M44,N46,J34:J35Expression=AND($C3="Vacis")textYES
H3:I46,M6:N7,M10,L14:M14,S14:S16,J15:N17,Q16:Q17,R17:S17,S19:S45,M23:N26,M27:M33,M34:N34,J34:J35,M35:M44,N46Expression=AND($C3="Vessel")textYES
M45:N45Expression=AND($C45="Vacis")textYES
M45:N45Expression=AND($C45="Vessel")textYES
Q45:R46Expression=AND($C45="Outbound Rail")textYES
Q45:R46,R16,R23:R44,L45:L46,M46Expression=AND($C16="Inbound Rail")textYES
R16,R23:R44,L45:L46,Q45:R46,M46,O46Expression=AND($C16="Vacis")textYES
L45,Q45:R46Expression=AND($C45="Vessel")textYES
R16,R23:R44,L46:M46,O46Expression=AND($C16="Vessel")textYES
C3:C46Expression=AND($C3="Vessel")textYES
C3:C46Expression=NOT(ISERROR(SEARCH("Vessel",C3)))textYES
O45Expression=AND($C45="Outbound Rail")textYES
O45,P46Expression=AND($C45="Vacis")textYES
S3:S4Expression=AND($C3="Vacis")textYES
O45Expression=AND($C45="Vessel")textYES
P46Expression=NOT(ISERROR(SEARCH("Standby",P46)))textYES
P46Expression=AND($C46="Vessel")textYES
P45Expression=AND($C45="Outbound Rail")textYES
P45Expression=AND($C45="Vacis")textYES
P45Expression=AND($C45="Vessel")textYES
P45Expression=NOT(ISERROR(SEARCH("Standby",P45)))textYES
P45Expression=AND($C45="Vessel")textYES
S3:S4Expression=AND($C3="Vessel")textYES
L3:L5,Q3:R10,L8:L9,L11:L13,Q11:S13,Q14:R15,L18:L22,Q18:R22Expression=AND($C3="Inbound Rail")textYES
Q3:R4Expression=AND($C3="Vacis")textYES
Q3:R4Expression=AND($C3="Vessel")textYES
L3:L5,Q5:S6,Q7:R10,L8:L9,L11:L13,Q11:S13,Q14:R15,L18:L22,Q18:R22Expression=AND($C3="Vacis")textYES
L3:L5,Q5:R6,Q7,L8:L9,Q8:R13,L11:L13,Q14:Q15,Q18:R18,L18:L22,Q19:Q22Expression=AND($C3="Vessel")textYES
S5:S6,R7,S9:S13,R14:R15,R19:R22Expression=AND($C5="Vessel")textYES
Cells with Data Validation
CellAllowCriteria
P3:P44List7:00,7:00 (Standby),8:00,8:00 (Standby),13:00,13:00 (Standby),19:00,19:00 (Standby)
P46List7:00,7:00 (Standby),8:00,8:00 (Standby),13:00,13:00 (Standby),19:00,19:00 (Standby)
C3:C46ListVessel,PWCS,Fumigation,Inbound Rail,Outbound Rail,Chiller(2%),Vacis,Tailgate,Steam Clean


Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
 
Upvote 0
Solution
etaf said:
ok,
=FILTER(Daily_Database!B3:U100,(Daily_Database!C3:C100=B4)*(Daily_Database!B3:B100=TODAY()))
will pull all the data
if all you need is column S

=FILTER(Daily_Database!S3:S100,(Daily_Database!C3:C100="vessel")*(Daily_Database!B3:B100=TODAY()))

OR
=INDEX(Daily_Database!S:S,MATCH(TODAY()&B2,Daily_Database!B:B&Daily_Database!C:C,0))


Daily Recap - Assist - ETAF.xlsx
ABCDE
1FilterLookup
2vessel1212
Sheet2
Cell Formulas
RangeFormula
C2C2=FILTER(Daily_Database!S3:S100,(Daily_Database!C3:C100="vessel")*(Daily_Database!B3:B100=TODAY()))
E2E2=INDEX(Daily_Database!S:S,MATCH(TODAY()&B2,Daily_Database!B:B&Daily_Database!C:C,0))


Daily Recap - Assist - ETAF.xlsx
ABCDEFGHIJKLMNOPQRST
1Daily Operations Database
2DateCategoryRail VisitCarsWellsUnitsWave 1Wave 2ICL UnitsVes RefServiceQCXMWXVes ETAVes Ops StartImportsExportsTotalsComments
3August 24, 2023Inbound RailEQC00752-ILM7254916IPRI-908EBFriday, August 25, 20238:00
4August 24, 2023Outbound RailMWE2023082444
5August 24, 2023Fumigation1
6August 25, 2023VesselABCD-0001NZFriday, August 25, 20238:00364314678
7August 25, 2023VesselABCD-0001NZFriday, August 25, 20238:00166168334
8August 25, 2023Inbound RailEQC00753-ILM7173515IPRI-908EB46Friday, August 25, 20238:00
9August 25, 2023Chiller(2%)2
10August 25, 2023Outbound RailWQC00761-ILM40+65IPRI-908EB65Friday, August 25, 20238:00
11August 25, 2023Vacis11
12August 26, 2023Inbound RailEQC00754-ILM2813
13August 28, 2023VesselABCD-0001NZMonday, August 28, 20238:0074074
14August 29, 2023Vacis2
15August 30, 2023VesselABCD-0001NZWednesday, August 30, 202313:0010067167
16August 30, 2023VesselABCD-0001NZWednesday, August 30, 202313:0012112124
17September 5, 2023Chiller(2%)2
18September 5, 2023Vacis4
19September 5, 2023Inbound RailEQC00760-ILM611
20September 5, 2023Inbound RailEQC00761-ILM317
21September 5, 2023Inbound RailEQC00762-ILM740
22September 5, 2023Inbound RailEQC00763-ILM48
23September 5, 2023Outbound RailWQC00772-ILM17170
24September 7, 2023VesselSAMR-335SSAESThursday, September 07, 20238:00781694
25September 7, 2023VesselSALB-335NSAENThursday, September 07, 20238:0095095
26September 7, 2023VesselCAHE-0013WUSAXThursday, September 07, 20238:0026026
27September 7, 2023Vacis2
28September 7, 2023VesselPBRA-336SNAE 200Thursday, September 07, 202319:00137386
29September 8, 2023VesselIFUT-910EBICL651Friday, September 08, 20238:00362300662
30September 8, 2023VesselTUCA-3123SNZ0Friday, September 08, 20238:00145137282
31September 8, 2023Inbound RailEQC00767-ILM10285428IFUT-910EBFriday, September 08, 20238:00
32September 8, 2023Outbound RailWQC00775-ILM0
33September 8, 2023Outbound RailMWE202309080
34September 9, 2023VesselSEXP-045WEC2Saturday, September 09, 202319:00 (Standby)3566691025
35September 11, 2023Inbound RailEQC00768-ILM180
36September 11, 2023Inbound RailEQC00769-ILM7310
37September 11, 2023Inbound RailEQC00770-ILM6370
38September 11, 2023Vacis40
39September 11, 2023Chiller(2%)10
40September 11, 2023Outbound RailWQC00778-ILM19190
41September 11, 2023Outbound RailMWE202309110440All inbound and outbound for 9/11 totals out to 139 units. Need to have all drivers focus on rail.
42September 13, 2023Outbound RailWQC00780-ILM88002 x 20's and 6 x 40's
43September 13, 2023PWCS6510
44September 13, 20230
45September 13, 2023Vesseltest1test2test3test4test5test6test7test812
46
47
48
49
Daily_Database
Cell Formulas
RangeFormula
S6:S7,S34:S44,S28:S30,S24:S26,S15:S16,S13S6=SUM(Daily_Database!$Q6+Daily_Database!$R6)
B45B45=TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:F46,J3:S46Expression=AND($C3="PWCS")textYES
D3:F46,H3:S46Expression=AND($C3="Chiller(2%)")textYES
M3:N5,S3:S10,M8:N9,N10,M11:N13,N14:N17,S18,M18:N22,N27:N44,S46Expression=AND($C3="Inbound Rail")textYES
M3:N5,S7:S10,M8:N9,N10,M11:N13,N14,S18,M18:N22,N27:N33,N35:N44,S46Expression=AND($C3="Vacis")textYES
M3:N5,S7:S8,M8:N9,N10,M11:N13,N14,S18,M18:N22,N27:N33,N35:N44,S46Expression=AND($C3="Vessel")textYES
D3:J46Expression=AND($C3="Vessel")textYES
H3:L46,O3:R44,M45:S45,O46:P46Expression=AND($C3="Outbound Rail")textYES
O3:P44,J3:K14,J18:K33,K34:K35,J36:K46Expression=AND($C3="Vacis")textYES
J3:K14,O3:O44,J18:K33,K34:K35,J36:K46Expression=AND($C3="Vessel")textYES
P3:P44Expression=NOT(ISERROR(SEARCH("Standby",P3)))textYES
P3:P44Expression=AND($C3="Vessel")textYES
D3:F46Expression=AND($C3="Vacis")textYES
D3:G46Expression=AND($C3="Vessel")textYES
C1:C2,C47:C1048576Expression=NOT(ISERROR(SEARCH("Vessel",C1)))textYES
H3:I46,L6:N7,L10:M10,L14:M17,Q16:Q17,R17:S17,S19:S45,L23:N26,Q23:Q44,L27:M44,M45:N45,S14:S16,N46Expression=AND($C3="Inbound Rail")textYES
L6:L7,L10,L23:L44,Q23:Q44Expression=AND($C6="Vacis")textYES
L6:L7,L10,L23:L44,Q23:Q44Expression=AND($C6="Vessel")textYES
H3:I46,M6:N7,M10,L14:M14,S14:S16,J15:N17,Q16:Q17,R17:S17,S19:S45,M23:N26,M27:M33,M34:N34,M35:M44,N46,J34:J35Expression=AND($C3="Vacis")textYES
H3:I46,M6:N7,M10,L14:M14,S14:S16,J15:N17,Q16:Q17,R17:S17,S19:S45,M23:N26,M27:M33,M34:N34,J34:J35,M35:M44,N46Expression=AND($C3="Vessel")textYES
M45:N45Expression=AND($C45="Vacis")textYES
M45:N45Expression=AND($C45="Vessel")textYES
Q45:R46Expression=AND($C45="Outbound Rail")textYES
Q45:R46,R16,R23:R44,L45:L46,M46Expression=AND($C16="Inbound Rail")textYES
R16,R23:R44,L45:L46,Q45:R46,M46,O46Expression=AND($C16="Vacis")textYES
L45,Q45:R46Expression=AND($C45="Vessel")textYES
R16,R23:R44,L46:M46,O46Expression=AND($C16="Vessel")textYES
C3:C46Expression=AND($C3="Vessel")textYES
C3:C46Expression=NOT(ISERROR(SEARCH("Vessel",C3)))textYES
O45Expression=AND($C45="Outbound Rail")textYES
O45,P46Expression=AND($C45="Vacis")textYES
S3:S4Expression=AND($C3="Vacis")textYES
O45Expression=AND($C45="Vessel")textYES
P46Expression=NOT(ISERROR(SEARCH("Standby",P46)))textYES
P46Expression=AND($C46="Vessel")textYES
P45Expression=AND($C45="Outbound Rail")textYES
P45Expression=AND($C45="Vacis")textYES
P45Expression=AND($C45="Vessel")textYES
P45Expression=NOT(ISERROR(SEARCH("Standby",P45)))textYES
P45Expression=AND($C45="Vessel")textYES
S3:S4Expression=AND($C3="Vessel")textYES
L3:L5,Q3:R10,L8:L9,L11:L13,Q11:S13,Q14:R15,L18:L22,Q18:R22Expression=AND($C3="Inbound Rail")textYES
Q3:R4Expression=AND($C3="Vacis")textYES
Q3:R4Expression=AND($C3="Vessel")textYES
L3:L5,Q5:S6,Q7:R10,L8:L9,L11:L13,Q11:S13,Q14:R15,L18:L22,Q18:R22Expression=AND($C3="Vacis")textYES
L3:L5,Q5:R6,Q7,L8:L9,Q8:R13,L11:L13,Q14:Q15,Q18:R18,L18:L22,Q19:Q22Expression=AND($C3="Vessel")textYES
S5:S6,R7,S9:S13,R14:R15,R19:R22Expression=AND($C5="Vessel")textYES
Cells with Data Validation
CellAllowCriteria
P3:P44List7:00,7:00 (Standby),8:00,8:00 (Standby),13:00,13:00 (Standby),19:00,19:00 (Standby)
P46List7:00,7:00 (Standby),8:00,8:00 (Standby),13:00,13:00 (Standby),19:00,19:00 (Standby)
C3:C46ListVessel,PWCS,Fumigation,Inbound Rail,Outbound Rail,Chiller(2%),Vacis,Tailgate,Steam Clean


Dropbox - File Deleted - Simplify your life

www.dropbox.com www.dropbox.com
Click to expand...
Etaf,

Thank you so much for the help with this.

I would ask one question while I may still have your attention.

Could you direct me to any good material covering formulas that would help me understand the basics and structure so that I may grow?
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question
Show date greater than today on schedule
Replies
5
Views
198
ulak
U
S
  • Solved
Return Value based on max date
Replies
6
Views
273
Fluff
Fluff
P
  • Solved
Conditional Formatting - Not blank, Date older than 15 days, Adjacent cell blank
Replies
3
Views
148
SanjayGMusafir
SanjayGMusafir
Keebler
  • Question
Find lastest date in range
Replies
5
Views
107
Keebler
Keebler
N
  • Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
180
Alex Blakenburg
A

Forum statistics

Threads
1,215,412
Messages
6,124,761
Members
449,187
Latest member
hermansoa

Share this page

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
Back
Top