Adam G Baker
New Member
- Joined
- Jan 19, 2017
- Messages
- 1
First I'd like to start by saying when it comes to Excel, I'm pretty green. I can do a lot of basic things, and use basic formulas and functions. But once things start getting remotely complicated, I don't have any idea what to do.
Trying to put together a spreadsheet for tracking shipped orders at work. So far I've been able to figure out w/ the help of Google on how to make the various formulas work, but I've reached a point where I'm stumped.
Below is a screenshot of the spreadsheet I'm working with.
The green cells are user input fields
The yellow cells are calculated output fields using functions & formulas.
K-M use the following formula. The IF function is used to keep the cell blank if no data is supplied in the input fields.
=IF($G2="","",(NETWORKDAYS($E2,$G2,Holidays!$A$2:$A$20))-1)
N & O use the following formula. The first IF is used the same as above, keep the cell blank if no data is supplied, the nested IF is for displaying a zero if the IF statement isn't met as true.
=IF($I2="","",IF($I2>$G2,(NETWORKDAYS($G2,$I2,Holidays!$A$2:$A$20))-1,0))
Using the data above, I need to search by a date, to fill out the cells below
As shown on the left, I need to be able to search the first table within a given date range. If no range is given, I'd like to have it search by a default range. I don't have a default range in mind at the moment, but figured I'd just start with a year from the current date.
Orders over Confirm Date would search column N "Days Late to Confirmed Ship Date" and would come back with the number of cells with a value greater than zero.
Orders over Customer Request would search column O "Days Late to Customer Requested Date" and come back with the number of cells with a value greater than zero.
The above two I know I can get the total number w/o a range using a countif. Don't know if there's another function that would do this within a date range, or if the countif needs to be nested in another function
Orders over Customer Requested Days, needs to search column L "Customer Requested Days" and give the number of cells in this column, with a value greater than the value given in the corresponding row in Column J "Quoted Lead Time (Days)"
Orders Over Confirm Time would search Column M "Actual Days to Shipment" and look for cells with values greater than those in Column J "Quoted Lead Time (Days)"
Trying to put together a spreadsheet for tracking shipped orders at work. So far I've been able to figure out w/ the help of Google on how to make the various formulas work, but I've reached a point where I'm stumped.
Below is a screenshot of the spreadsheet I'm working with.
The green cells are user input fields
The yellow cells are calculated output fields using functions & formulas.
K-M use the following formula. The IF function is used to keep the cell blank if no data is supplied in the input fields.
=IF($G2="","",(NETWORKDAYS($E2,$G2,Holidays!$A$2:$A$20))-1)
N & O use the following formula. The first IF is used the same as above, keep the cell blank if no data is supplied, the nested IF is for displaying a zero if the IF statement isn't met as true.
=IF($I2="","",IF($I2>$G2,(NETWORKDAYS($G2,$I2,Holidays!$A$2:$A$20))-1,0))
Using the data above, I need to search by a date, to fill out the cells below
As shown on the left, I need to be able to search the first table within a given date range. If no range is given, I'd like to have it search by a default range. I don't have a default range in mind at the moment, but figured I'd just start with a year from the current date.
Orders over Confirm Date would search column N "Days Late to Confirmed Ship Date" and would come back with the number of cells with a value greater than zero.
Orders over Customer Request would search column O "Days Late to Customer Requested Date" and come back with the number of cells with a value greater than zero.
The above two I know I can get the total number w/o a range using a countif. Don't know if there's another function that would do this within a date range, or if the countif needs to be nested in another function
Orders over Customer Requested Days, needs to search column L "Customer Requested Days" and give the number of cells in this column, with a value greater than the value given in the corresponding row in Column J "Quoted Lead Time (Days)"
Orders Over Confirm Time would search Column M "Actual Days to Shipment" and look for cells with values greater than those in Column J "Quoted Lead Time (Days)"
