Greetings,
I have a formula to extract data from a 3 column worksheet (SKED) into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the PORTTIME
column for the matching ROW. If no match, then cell remains blank.

My problem is I get a #REF error when there are identical entries (i.e. same
dates) in the DOA column in source sheet that have different values in the
queried PORTTIME column. I assume this is due to the INDEX ROW functions?
There are at most only two duplicate date entries in source DOA column for
any particular SHIPCODE value. I would like to tweak the formula so the cell
in the calendar sheet displays the first (or second) PORTTIME value instead
of #REF. What can I do to make this happen? I have gotten lost in VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads (correspond to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date of arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and ship names
(corresponding to values in SHIPCODE column in the source worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2 contains
"1". Formula starts in cell B3 in calendar sheet and is filled across the
day columns and down the ship column. This means the formula is in 2294 cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer to this,
and have learned much about SUMPRODUCT, but cannot figure this out. I would
appreciate some guidance or suggestions.

Seapilot

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance the
SUMPRODUCT is summing the multiple row numbers and the total is outside the
indexed range.

If you're interested in *either* the first instance or the last instance you
can replace SUMPRODUCT with MAX. This will make the formula an array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX function. This
saves from calculating an array of row offsets when you only need to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED) into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the PORTTIME
column for the matching ROW. If no match, then cell remains blank.

My problem is I get a #REF error when there are identical entries (i.e.
same
dates) in the DOA column in source sheet that have different values in the
queried PORTTIME column. I assume this is due to the INDEX ROW functions?
There are at most only two duplicate date entries in source DOA column for
any particular SHIPCODE value. I would like to tweak the formula so the
cell
in the calendar sheet displays the first (or second) PORTTIME value
instead
of #REF. What can I do to make this happen? I have gotten lost in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads (correspond to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and ship
names
(corresponding to values in SHIPCODE column in the source worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2 contains
"1". Formula starts in cell B3 in calendar sheet and is filled across the
day columns and down the ship column. This means the formula is in 2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer to this,
and have learned much about SUMPRODUCT, but cannot figure this out. I
would
appreciate some guidance or suggestions.

Seapilot

Thanks! I tried the second suggestion, with the row offset outside the MAX
function.
Can I amend the formula further to select which of the two instances I want
returned? MAX returns the first instance alphabetically, which was actually
the second occurance in the order the data in PORTTIME is presently sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance the
SUMPRODUCT is summing the multiple row numbers and the total is outside the
indexed range.

If you're interested in *either* the first instance or the last instance you
can replace SUMPRODUCT with MAX. This will make the formula an array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX function. This
saves from calculating an array of row offsets when you only need to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED) into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the PORTTIME
column for the matching ROW. If no match, then cell remains blank.

My problem is I get a #REF error when there are identical entries (i.e.
same
dates) in the DOA column in source sheet that have different values in the
queried PORTTIME column. I assume this is due to the INDEX ROW functions?
There are at most only two duplicate date entries in source DOA column for
any particular SHIPCODE value. I would like to tweak the formula so the
cell
in the calendar sheet displays the first (or second) PORTTIME value
instead
of #REF. What can I do to make this happen? I have gotten lost in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads (correspond to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and ship
names
(corresponding to values in SHIPCODE column in the source worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2 contains
"1". Formula starts in cell B3 in calendar sheet and is filled across the
day columns and down the ship column. This means the formula is in 2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer to this,
and have learned much about SUMPRODUCT, but cannot figure this out. I
would
appreciate some guidance or suggestions.

Seapilot

If there are only one or two instances then you can replace MAX with LARGE:

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(D OA)=B$2)*ROW(DOA))


LARGE((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY (DOA)=B$2)*ROW(DOA),n)

Where n = 1 or 2

For a lookup value that has only one instance using n=2 will return an
error.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks! I tried the second suggestion, with the row offset outside the
MAX
function.
Can I amend the formula further to select which of the two instances I
want
returned? MAX returns the first instance alphabetically, which was
actually
the second occurance in the order the data in PORTTIME is presently
sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance the
SUMPRODUCT is summing the multiple row numbers and the total is outside
the
indexed range.

If you're interested in *either* the first instance or the last instance
you
can replace SUMPRODUCT with MAX. This will make the formula an array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX function.
This
saves from calculating an array of row offsets when you only need to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED) into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the
PORTTIME
column for the matching ROW. If no match, then cell remains blank.

My problem is I get a #REF error when there are identical entries (i.e.
same
dates) in the DOA column in source sheet that have different values in
the
queried PORTTIME column. I assume this is due to the INDEX ROW
functions?
There are at most only two duplicate date entries in source DOA column
for
any particular SHIPCODE value. I would like to tweak the formula so
the
cell
in the calendar sheet displays the first (or second) PORTTIME value
instead
of #REF. What can I do to make this happen? I have gotten lost in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads (correspond
to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and ship
names
(corresponding to values in SHIPCODE column in the source worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2
contains
"1". Formula starts in cell B3 in calendar sheet and is filled across
the
day columns and down the ship column. This means the formula is in 2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer to
this,
and have learned much about SUMPRODUCT, but cannot figure this out. I
would
appreciate some guidance or suggestions.

Seapilot

Most of the lookups have only one instance and I would like a formula that I
can fill across and down that will still return valid info in those cases. I
need a formula that will work with both one instance and also with 2
instances, like the MAX flavor. Could I fold the IF statement into an
ISERROR function of some kind?
My thought is to insert a row in the calendar (output) sheet where I can
insert and fill the formula amended to display the correct "one instance"
text, and, in the "two instance" cases, display the other of the two
instances not shown in the cell above. I am trying to somehow, get all the
data to display with filled formulas, and avoid the #REF errors.

"T. Valko" wrote:

If there are only one or two instances then you can replace MAX with LARGE:

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(D OA)=B$2)*ROW(DOA))


LARGE((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY (DOA)=B$2)*ROW(DOA),n)

Where n = 1 or 2

For a lookup value that has only one instance using n=2 will return an
error.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks! I tried the second suggestion, with the row offset outside the
MAX
function.
Can I amend the formula further to select which of the two instances I
want
returned? MAX returns the first instance alphabetically, which was
actually
the second occurance in the order the data in PORTTIME is presently
sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance the
SUMPRODUCT is summing the multiple row numbers and the total is outside
the
indexed range.

If you're interested in *either* the first instance or the last instance
you
can replace SUMPRODUCT with MAX. This will make the formula an array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX function.
This
saves from calculating an array of row offsets when you only need to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED) into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the
PORTTIME
column for the matching ROW. If no match, then cell remains blank.

My problem is I get a #REF error when there are identical entries (i.e.
same
dates) in the DOA column in source sheet that have different values in
the
queried PORTTIME column. I assume this is due to the INDEX ROW
functions?
There are at most only two duplicate date entries in source DOA column
for
any particular SHIPCODE value. I would like to tweak the formula so
the
cell
in the calendar sheet displays the first (or second) PORTTIME value
instead
of #REF. What can I do to make this happen? I have gotten lost in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads (correspond
to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and ship
names
(corresponding to values in SHIPCODE column in the source worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2
contains
"1". Formula starts in cell B3 in calendar sheet and is filled across
the
day columns and down the ship column. This means the formula is in 2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer to
this,
and have learned much about SUMPRODUCT, but cannot figure this out. I
would
appreciate some guidance or suggestions.

Seapilot

Yeah, you can wrap the formula in ISERROR to trap any errors. One thing to
consider is that the formula is *already* fairly calculation intensive. What
version of Excel are you using?

You can replace the first SUMPRODUCT with the error test.

You can use the LARGE version to increment n but the problem with this is
that n needs to "reset" for each different lookup value as you copy the
formula down a column. You could do that in the formula but now the formula
is probably getting out of hand calculation-wise.

How many unique lookup values are there?

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Most of the lookups have only one instance and I would like a formula that
I
can fill across and down that will still return valid info in those cases.
I
need a formula that will work with both one instance and also with 2
instances, like the MAX flavor. Could I fold the IF statement into an
ISERROR function of some kind?
My thought is to insert a row in the calendar (output) sheet where I can
insert and fill the formula amended to display the correct "one instance"
text, and, in the "two instance" cases, display the other of the two
instances not shown in the cell above. I am trying to somehow, get all
the
data to display with filled formulas, and avoid the #REF errors.

"T. Valko" wrote:

If there are only one or two instances then you can replace MAX with
LARGE:

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(D OA)=B$2)*ROW(DOA))


LARGE((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY (DOA)=B$2)*ROW(DOA),n)

Where n = 1 or 2

For a lookup value that has only one instance using n=2 will return an
error.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks! I tried the second suggestion, with the row offset outside the
MAX
function.
Can I amend the formula further to select which of the two instances I
want
returned? MAX returns the first instance alphabetically, which was
actually
the second occurance in the order the data in PORTTIME is presently
sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance the
SUMPRODUCT is summing the multiple row numbers and the total is
outside
the
indexed range.

If you're interested in *either* the first instance or the last
instance
you
can replace SUMPRODUCT with MAX. This will make the formula an array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX function.
This
saves from calculating an array of row offsets when you only need to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED)
into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the
SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the
PORTTIME
column for the matching ROW. If no match, then cell remains blank.

My problem is I get a #REF error when there are identical entries
(i.e.
same
dates) in the DOA column in source sheet that have different values
in
the
queried PORTTIME column. I assume this is due to the INDEX ROW
functions?
There are at most only two duplicate date entries in source DOA
column
for
any particular SHIPCODE value. I would like to tweak the formula so
the
cell
in the calendar sheet displays the first (or second) PORTTIME value
instead
of #REF. What can I do to make this happen? I have gotten lost in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads
(correspond
to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and
ship
names
(corresponding to values in SHIPCODE column in the source worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2
contains
"1". Formula starts in cell B3 in calendar sheet and is filled
across
the
day columns and down the ship column. This means the formula is in
2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer to
this,
and have learned much about SUMPRODUCT, but cannot figure this out.
I
would
appreciate some guidance or suggestions.

Seapilot

Thank you for your extreme patience
I am using Excel 2007 on Vista 64bit with 4 GB ram and Intel Core 2 Duo p8400.
# of unique lookup values, on source sheet, there are 2250 rows of
data; there are 36 unique values in the SHIPCODE column, perhaps 16 unique
values in the PORTTIME column (although these values are concantenated from 3
other columns to get a value such as "GB 07:00-16:00" from SHIP, TOA, DOA)
and in cases of the #REF error, there are, as I said only 2 instances of
identical DOA values per unique SHIPCODE value with 2 PORTTIME values; ie at
most, 2 same dates (DOA) where a ship(SHIPCODE) visits more than one port
(PORTTIME) but never more than 2 ports, on a given day, which may occur for
that ship one day each week. There are at most, 6 different PORTTIME values
in the entire range that are at issue with the #REF error.
You are correct about the calc getting a bit out of hand. I wonder if
perhaps addressing the issue at the source table level might simplify things?
I could insert a new column of data if it would help, like to assign a
unique key to the DOA values? All I am really interested in is getting the
output to be the values in the PORTTIME range for the correct DOA and
SHIPCODE. I end up having 2 rows per 'ship' on the output sheet anyway in
the $A column anyway, so I have 72 rows of formulas for the 36 unique
SHIPCODE values. for, say, 4 contiguous cells with lookup values, only 1
would have the #REF error and if I could get the output sheet to display the
one LOOKUP value on the first row, and then the other value on the row below
it (with the other lookup values that did not return #REF unchanged from the
row above) I would have a product that meets my needs.
Is there no simpler combination of functions that allow for duplicate dates
and will allow the formula to display the first match on the top row and a
similar formula to display the second match on the row below? I've been
assuming that I am missing something elementary and it is due to my lack of
knowledge regarding functions.
Do I need to embark on a deeper level of learning to solve this? VB?
Access? Solver?
I am willing to go out and learn, I am just a bit ignorant at this point.
Seapilot


"T. Valko" wrote:

Yeah, you can wrap the formula in ISERROR to trap any errors. One thing to
consider is that the formula is *already* fairly calculation intensive. What
version of Excel are you using?

You can replace the first SUMPRODUCT with the error test.

You can use the LARGE version to increment n but the problem with this is
that n needs to "reset" for each different lookup value as you copy the
formula down a column. You could do that in the formula but now the formula
is probably getting out of hand calculation-wise.

How many unique lookup values are there?

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Most of the lookups have only one instance and I would like a formula that
I
can fill across and down that will still return valid info in those cases.
I
need a formula that will work with both one instance and also with 2
instances, like the MAX flavor. Could I fold the IF statement into an
ISERROR function of some kind?
My thought is to insert a row in the calendar (output) sheet where I can
insert and fill the formula amended to display the correct "one instance"
text, and, in the "two instance" cases, display the other of the two
instances not shown in the cell above. I am trying to somehow, get all
the
data to display with filled formulas, and avoid the #REF errors.

"T. Valko" wrote:

If there are only one or two instances then you can replace MAX with
LARGE:

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(D OA)=B$2)*ROW(DOA))


LARGE((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY (DOA)=B$2)*ROW(DOA),n)

Where n = 1 or 2

For a lookup value that has only one instance using n=2 will return an
error.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks! I tried the second suggestion, with the row offset outside the
MAX
function.
Can I amend the formula further to select which of the two instances I
want
returned? MAX returns the first instance alphabetically, which was
actually
the second occurance in the order the data in PORTTIME is presently
sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance the
SUMPRODUCT is summing the multiple row numbers and the total is
outside
the
indexed range.

If you're interested in *either* the first instance or the last
instance
you
can replace SUMPRODUCT with MAX. This will make the formula an array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX function.
This
saves from calculating an array of row offsets when you only need to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED)
into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the
SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the
PORTTIME
column for the matching ROW. If no match, then cell remains blank.

My problem is I get a #REF error when there are identical entries
(i.e.
same
dates) in the DOA column in source sheet that have different values
in
the
queried PORTTIME column. I assume this is due to the INDEX ROW
functions?
There are at most only two duplicate date entries in source DOA
column
for
any particular SHIPCODE value. I would like to tweak the formula so
the
cell
in the calendar sheet displays the first (or second) PORTTIME value
instead
of #REF. What can I do to make this happen? I have gotten lost in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads
(correspond
to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and
ship
names
(corresponding to values in SHIPCODE column in the source worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2
contains
"1". Formula starts in cell B3 in calendar sheet and is filled
across
the
day columns and down the ship column. This means the formula is in
2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer to
this,
and have learned much about SUMPRODUCT, but cannot figure this out.
I
would
appreciate some guidance or suggestions.

Seapilot

Can you send me a copy of your file so I can see what you're trying to do?
If you can do that I'd like to see an example of where you're getting the
#REF! error. This will help me to understand how the duplicate instances are
causing the problem.

If you want to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious. If the file is bigger than 1mb, zip it.

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thank you for your extreme patience
I am using Excel 2007 on Vista 64bit with 4 GB ram and Intel Core 2 Duo
p8400.
# of unique lookup values, on source sheet, there are 2250 rows of
data; there are 36 unique values in the SHIPCODE column, perhaps 16
unique
values in the PORTTIME column (although these values are concantenated
from 3
other columns to get a value such as "GB 07:00-16:00" from SHIP, TOA,
DOA)
and in cases of the #REF error, there are, as I said only 2 instances of
identical DOA values per unique SHIPCODE value with 2 PORTTIME values; ie
at
most, 2 same dates (DOA) where a ship(SHIPCODE) visits more than one port
(PORTTIME) but never more than 2 ports, on a given day, which may occur
for
that ship one day each week. There are at most, 6 different PORTTIME
values
in the entire range that are at issue with the #REF error.
You are correct about the calc getting a bit out of hand. I wonder if
perhaps addressing the issue at the source table level might simplify
things?
I could insert a new column of data if it would help, like to assign a
unique key to the DOA values? All I am really interested in is getting
the
output to be the values in the PORTTIME range for the correct DOA and
SHIPCODE. I end up having 2 rows per 'ship' on the output sheet anyway in
the $A column anyway, so I have 72 rows of formulas for the 36 unique
SHIPCODE values. for, say, 4 contiguous cells with lookup values, only 1
would have the #REF error and if I could get the output sheet to display
the
one LOOKUP value on the first row, and then the other value on the row
below
it (with the other lookup values that did not return #REF unchanged from
the
row above) I would have a product that meets my needs.
Is there no simpler combination of functions that allow for duplicate
dates
and will allow the formula to display the first match on the top row and a
similar formula to display the second match on the row below? I've been
assuming that I am missing something elementary and it is due to my lack
of
knowledge regarding functions.
Do I need to embark on a deeper level of learning to solve this? VB?
Access? Solver?
I am willing to go out and learn, I am just a bit ignorant at this point.
Seapilot


"T. Valko" wrote:

Yeah, you can wrap the formula in ISERROR to trap any errors. One thing
to
consider is that the formula is *already* fairly calculation intensive.
What
version of Excel are you using?

You can replace the first SUMPRODUCT with the error test.

You can use the LARGE version to increment n but the problem with this is
that n needs to "reset" for each different lookup value as you copy the
formula down a column. You could do that in the formula but now the
formula
is probably getting out of hand calculation-wise.

How many unique lookup values are there?

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Most of the lookups have only one instance and I would like a formula
that
I
can fill across and down that will still return valid info in those
cases.
I
need a formula that will work with both one instance and also with 2
instances, like the MAX flavor. Could I fold the IF statement into an
ISERROR function of some kind?
My thought is to insert a row in the calendar (output) sheet where I
can
insert and fill the formula amended to display the correct "one
instance"
text, and, in the "two instance" cases, display the other of the two
instances not shown in the cell above. I am trying to somehow, get all
the
data to display with filled formulas, and avoid the #REF errors.

"T. Valko" wrote:

If there are only one or two instances then you can replace MAX with
LARGE:

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(D OA)=B$2)*ROW(DOA))


LARGE((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY (DOA)=B$2)*ROW(DOA),n)

Where n = 1 or 2

For a lookup value that has only one instance using n=2 will return an
error.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks! I tried the second suggestion, with the row offset outside
the
MAX
function.
Can I amend the formula further to select which of the two instances
I
want
returned? MAX returns the first instance alphabetically, which was
actually
the second occurance in the order the data in PORTTIME is presently
sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance
the
SUMPRODUCT is summing the multiple row numbers and the total is
outside
the
indexed range.

If you're interested in *either* the first instance or the last
instance
you
can replace SUMPRODUCT with MAX. This will make the formula an
array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX
function.
This
saves from calculating an array of row offsets when you only need
to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED)
into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the
SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the
PORTTIME
column for the matching ROW. If no match, then cell remains
blank.

My problem is I get a #REF error when there are identical entries
(i.e.
same
dates) in the DOA column in source sheet that have different
values
in
the
queried PORTTIME column. I assume this is due to the INDEX ROW
functions?
There are at most only two duplicate date entries in source DOA
column
for
any particular SHIPCODE value. I would like to tweak the formula
so
the
cell
in the calendar sheet displays the first (or second) PORTTIME
value
instead
of #REF. What can I do to make this happen? I have gotten lost
in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads
(correspond
to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date
of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and
ship
names
(corresponding to values in SHIPCODE column in the source
worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2
contains
"1". Formula starts in cell B3 in calendar sheet and is filled
across
the
day columns and down the ship column. This means the formula is
in
2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer
to
this,
and have learned much about SUMPRODUCT, but cannot figure this
out.
I
would
appreciate some guidance or suggestions.

Seapilot

Thanks for looking over the file. The formula you sent worked perfectly! I
will be able to adjust it for other uses with the same style source worksheet
and tweaked for slightly different applications.
Thank you for your patience,
Mark this one SOLVED!
seapilot

"T. Valko" wrote:

Can you send me a copy of your file so I can see what you're trying to do?
If you can do that I'd like to see an example of where you're getting the
#REF! error. This will help me to understand how the duplicate instances are
causing the problem.

If you want to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious. If the file is bigger than 1mb, zip it.

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thank you for your extreme patience
I am using Excel 2007 on Vista 64bit with 4 GB ram and Intel Core 2 Duo
p8400.
# of unique lookup values, on source sheet, there are 2250 rows of
data; there are 36 unique values in the SHIPCODE column, perhaps 16
unique
values in the PORTTIME column (although these values are concantenated
from 3
other columns to get a value such as "GB 07:00-16:00" from SHIP, TOA,
DOA)
and in cases of the #REF error, there are, as I said only 2 instances of
identical DOA values per unique SHIPCODE value with 2 PORTTIME values; ie
at
most, 2 same dates (DOA) where a ship(SHIPCODE) visits more than one port
(PORTTIME) but never more than 2 ports, on a given day, which may occur
for
that ship one day each week. There are at most, 6 different PORTTIME
values
in the entire range that are at issue with the #REF error.
You are correct about the calc getting a bit out of hand. I wonder if
perhaps addressing the issue at the source table level might simplify
things?
I could insert a new column of data if it would help, like to assign a
unique key to the DOA values? All I am really interested in is getting
the
output to be the values in the PORTTIME range for the correct DOA and
SHIPCODE. I end up having 2 rows per 'ship' on the output sheet anyway in
the $A column anyway, so I have 72 rows of formulas for the 36 unique
SHIPCODE values. for, say, 4 contiguous cells with lookup values, only 1
would have the #REF error and if I could get the output sheet to display
the
one LOOKUP value on the first row, and then the other value on the row
below
it (with the other lookup values that did not return #REF unchanged from
the
row above) I would have a product that meets my needs.
Is there no simpler combination of functions that allow for duplicate
dates
and will allow the formula to display the first match on the top row and a
similar formula to display the second match on the row below? I've been
assuming that I am missing something elementary and it is due to my lack
of
knowledge regarding functions.
Do I need to embark on a deeper level of learning to solve this? VB?
Access? Solver?
I am willing to go out and learn, I am just a bit ignorant at this point.
Seapilot


"T. Valko" wrote:

Yeah, you can wrap the formula in ISERROR to trap any errors. One thing
to
consider is that the formula is *already* fairly calculation intensive.
What
version of Excel are you using?

You can replace the first SUMPRODUCT with the error test.

You can use the LARGE version to increment n but the problem with this is
that n needs to "reset" for each different lookup value as you copy the
formula down a column. You could do that in the formula but now the
formula
is probably getting out of hand calculation-wise.

How many unique lookup values are there?

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Most of the lookups have only one instance and I would like a formula
that
I
can fill across and down that will still return valid info in those
cases.
I
need a formula that will work with both one instance and also with 2
instances, like the MAX flavor. Could I fold the IF statement into an
ISERROR function of some kind?
My thought is to insert a row in the calendar (output) sheet where I
can
insert and fill the formula amended to display the correct "one
instance"
text, and, in the "two instance" cases, display the other of the two
instances not shown in the cell above. I am trying to somehow, get all
the
data to display with filled formulas, and avoid the #REF errors.

"T. Valko" wrote:

If there are only one or two instances then you can replace MAX with
LARGE:

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(D OA)=B$2)*ROW(DOA))


LARGE((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY (DOA)=B$2)*ROW(DOA),n)

Where n = 1 or 2

For a lookup value that has only one instance using n=2 will return an
error.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks! I tried the second suggestion, with the row offset outside
the
MAX
function.
Can I amend the formula further to select which of the two instances
I
want
returned? MAX returns the first instance alphabetically, which was
actually
the second occurance in the order the data in PORTTIME is presently
sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one instance
the
SUMPRODUCT is summing the multiple row numbers and the total is
outside
the
indexed range.

If you're interested in *either* the first instance or the last
instance
you
can replace SUMPRODUCT with MAX. This will make the formula an
array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX
function.
This
saves from calculating an array of row offsets when you only need
to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet (SKED)
into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the
SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in the
PORTTIME
column for the matching ROW. If no match, then cell remains
blank.

My problem is I get a #REF error when there are identical entries
(i.e.
same
dates) in the DOA column in source sheet that have different
values
in
the
queried PORTTIME column. I assume this is due to the INDEX ROW
functions?
There are at most only two duplicate date entries in source DOA
column
for
any particular SHIPCODE value. I would like to tweak the formula
so
the
cell
in the calendar sheet displays the first (or second) PORTTIME
value
instead
of #REF. What can I do to make this happen? I have gotten lost
in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads
(correspond
to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA (date
of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy), and
ship
names
(corresponding to values in SHIPCODE column in the source
worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell $B$2
contains
"1". Formula starts in cell B3 in calendar sheet and is filled
across
the
day columns and down the ship column. This means the formula is
in
2294
cells
per monthly sheet. Cell calculation takes time, but is done very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an answer
to
this,
and have learned much about SUMPRODUCT, but cannot figure this
out.
I
would
appreciate some guidance or suggestions.

Seapilot

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks for looking over the file. The formula you sent worked perfectly!
I
will be able to adjust it for other uses with the same style source
worksheet
and tweaked for slightly different applications.
Thank you for your patience,
Mark this one SOLVED!
seapilot

"T. Valko" wrote:

Can you send me a copy of your file so I can see what you're trying to
do?
If you can do that I'd like to see an example of where you're getting the
#REF! error. This will help me to understand how the duplicate instances
are
causing the problem.

If you want to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious. If the file is bigger than 1mb, zip
it.

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thank you for your extreme patience
I am using Excel 2007 on Vista 64bit with 4 GB ram and Intel Core 2 Duo
p8400.
# of unique lookup values, on source sheet, there are 2250 rows of
data; there are 36 unique values in the SHIPCODE column, perhaps 16
unique
values in the PORTTIME column (although these values are concantenated
from 3
other columns to get a value such as "GB 07:00-16:00" from SHIP,
TOA,
DOA)
and in cases of the #REF error, there are, as I said only 2 instances
of
identical DOA values per unique SHIPCODE value with 2 PORTTIME values;
ie
at
most, 2 same dates (DOA) where a ship(SHIPCODE) visits more than one
port
(PORTTIME) but never more than 2 ports, on a given day, which may occur
for
that ship one day each week. There are at most, 6 different PORTTIME
values
in the entire range that are at issue with the #REF error.
You are correct about the calc getting a bit out of hand. I wonder if
perhaps addressing the issue at the source table level might simplify
things?
I could insert a new column of data if it would help, like to assign a
unique key to the DOA values? All I am really interested in is getting
the
output to be the values in the PORTTIME range for the correct DOA and
SHIPCODE. I end up having 2 rows per 'ship' on the output sheet anyway
in
the $A column anyway, so I have 72 rows of formulas for the 36 unique
SHIPCODE values. for, say, 4 contiguous cells with lookup values, only
1
would have the #REF error and if I could get the output sheet to
display
the
one LOOKUP value on the first row, and then the other value on the row
below
it (with the other lookup values that did not return #REF unchanged
from
the
row above) I would have a product that meets my needs.
Is there no simpler combination of functions that allow for duplicate
dates
and will allow the formula to display the first match on the top row
and a
similar formula to display the second match on the row below? I've
been
assuming that I am missing something elementary and it is due to my
lack
of
knowledge regarding functions.
Do I need to embark on a deeper level of learning to solve this? VB?
Access? Solver?
I am willing to go out and learn, I am just a bit ignorant at this
point.
Seapilot


"T. Valko" wrote:

Yeah, you can wrap the formula in ISERROR to trap any errors. One
thing
to
consider is that the formula is *already* fairly calculation
intensive.
What
version of Excel are you using?

You can replace the first SUMPRODUCT with the error test.

You can use the LARGE version to increment n but the problem with this
is
that n needs to "reset" for each different lookup value as you copy
the
formula down a column. You could do that in the formula but now the
formula
is probably getting out of hand calculation-wise.

How many unique lookup values are there?

--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Most of the lookups have only one instance and I would like a
formula
that
I
can fill across and down that will still return valid info in those
cases.
I
need a formula that will work with both one instance and also with 2
instances, like the MAX flavor. Could I fold the IF statement into
an
ISERROR function of some kind?
My thought is to insert a row in the calendar (output) sheet where I
can
insert and fill the formula amended to display the correct "one
instance"
text, and, in the "two instance" cases, display the other of the two
instances not shown in the cell above. I am trying to somehow, get
all
the
data to display with filled formulas, and avoid the #REF errors.

"T. Valko" wrote:

If there are only one or two instances then you can replace MAX
with
LARGE:

MAX((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(D OA)=B$2)*ROW(DOA))


LARGE((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY (DOA)=B$2)*ROW(DOA),n)

Where n = 1 or 2

For a lookup value that has only one instance using n=2 will return
an
error.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Thanks! I tried the second suggestion, with the row offset
outside
the
MAX
function.
Can I amend the formula further to select which of the two
instances
I
want
returned? MAX returns the first instance alphabetically, which
was
actually
the second occurance in the order the data in PORTTIME is
presently
sorted.
I would like to see what function is flexible enough.
Seapilot

"T. Valko" wrote:

I get a #REF error when there are identical entries
I assume this is due to the INDEX ROW functions?

What's probably happening is when you have more than one
instance
the
SUMPRODUCT is summing the multiple row numbers and the total is
outside
the
indexed range.

If you're interested in *either* the first instance or the last
instance
you
can replace SUMPRODUCT with MAX. This will make the formula an
array
formula**.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* (ROW(DOA)-1))))

Also, you can put the row offset correction outside the MAX
function.
This
saves from calculating an array of row offsets when you only
need
to
calculate one row offset.

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,MAX((SHIP CODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA)=B$2)* ROW(DOA))-1))

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key
and
the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"seapilot" wrote in message
...
Greetings,
I have a formula to extract data from a 3 column worksheet
(SKED)
into
monthly calendar worksheets (MAY, JUNE, etc.).

=IF(SUMPRODUCT((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A $1))*(DAY(DOA)=B$2))=0,"",INDEX(PORTTIME,SUMPRODUC T((SHIPCODE=$A3)*(MONTH(DOA)=MONTH($A$1))*(DAY(DOA )=B$2)*(ROW(DOA)-1))))

Formula searches source worksheet for values that match the
SUMPRODUCT
conditions of SHIPCODE,MONTH, DAY and displays the value in
the
PORTTIME
column for the matching ROW. If no match, then cell remains
blank.

My problem is I get a #REF error when there are identical
entries
(i.e.
same
dates) in the DOA column in source sheet that have different
values
in
the
queried PORTTIME column. I assume this is due to the INDEX
ROW
functions?
There are at most only two duplicate date entries in source
DOA
column
for
any particular SHIPCODE value. I would like to tweak the
formula
so
the
cell
in the calendar sheet displays the first (or second) PORTTIME
value
instead
of #REF. What can I do to make this happen? I have gotten
lost
in
VLOOKUP,
MATCH, etc. and am at a loss.

Source worksheet has 2350 rows and 3 columns. Column heads
(correspond
to
the named ranges in the formula) are DOA,SHIPCODE,PORT. DOA
(date
of
arrival)
(mm/dd/yyyy), other two are text.
Calendar worksheets have the MONTH data in $A$1 (mm/dd/yyyy),
and
ship
names
(corresponding to values in SHIPCODE column in the source
worksheet)
beginning in $A$2. Days of month are in $B$2:$B$AF, so cell
$B$2
contains
"1". Formula starts in cell B3 in calendar sheet and is
filled
across
the
day columns and down the ship column. This means the formula
is
in
2294
cells
per monthly sheet. Cell calculation takes time, but is done
very
infrequently as there are rarely changes to source sheet.

I have spent a lot of time searching various sites for an
answer
to
this,
and have learned much about SUMPRODUCT, but cannot figure this
out.
I
would
appreciate some guidance or suggestions.

Seapilot