Working with MAX and IF
 

I'm working on a spreadsheet (Excel 2003) wherein I have one a series of
tables on one worksheet, each one named, and a second spreadsheet that will
be used to coalate the required information for printing. The printing sheet
will pick up the required information based upon a date that I enter into a
certain cell. I know that I can use a combination of MAX and IF statements
to make this work, and I've got it working in another file, but as soon as I
change something, it replies "#VALUE". I seem to remember there is a
specific set of key strokes you must use in order to make this formula work
properly. I'm giving you a sample of the formula below:

=MAX(IF(('EmployeeName'!A23:A49=$C$3)*('EmployeeNa me'!A23:A490),('EmployeeName'!B23:B49)))

(I have noticed that the formulas on the other spreadsheet are showing up as:
{=MAX(IF(('EmployeeName'!A23:A49=$C$3)*('EmployeeN ame'!A23:A490),('EmployeeName'!B23:B49)))}
with the {} showing - but if I type them into the new workbook, they make
the formula text, rather than a formula, so I believe that it has something
to do with the mystery keystrokes.)

wherein
$C$3 is the date that is the comparison call-up (Same sheet as the formula
is placed on)

and
'EmployeeName'!A23:A49 is the list of dates in the table

and
"EmployeeName'!B23:B49 contains the value that we want displayed.

Working with MAX and IF
 

You need to enter the formula as array formula that will show the {}
after edit formula in cell, press alt+shift+enter instead of enter


"Jennifer B" wrote:

I'm working on a spreadsheet (Excel 2003) wherein I have one a series of
tables on one worksheet, each one named, and a second spreadsheet that will
be used to coalate the required information for printing. The printing sheet
will pick up the required information based upon a date that I enter into a
certain cell. I know that I can use a combination of MAX and IF statements
to make this work, and I've got it working in another file, but as soon as I
change something, it replies "#VALUE". I seem to remember there is a
specific set of key strokes you must use in order to make this formula work
properly. I'm giving you a sample of the formula below:

=MAX(IF(('EmployeeName'!A23:A49=$C$3)*('EmployeeNa me'!A23:A490),('EmployeeName'!B23:B49)))

(I have noticed that the formulas on the other spreadsheet are showing up as:
{=MAX(IF(('EmployeeName'!A23:A49=$C$3)*('EmployeeN ame'!A23:A490),('EmployeeName'!B23:B49)))}
with the {} showing - but if I type them into the new workbook, they make
the formula text, rather than a formula, so I believe that it has something
to do with the mystery keystrokes.)

wherein
$C$3 is the date that is the comparison call-up (Same sheet as the formula
is placed on)

and
'EmployeeName'!A23:A49 is the list of dates in the table

and
"EmployeeName'!B23:B49 contains the value that we want displayed.

Working with MAX and IF
 

eksh meant ctrl-shift-enter (not alt-shift-enter).



eksh wrote:

You need to enter the formula as array formula that will show the {}
after edit formula in cell, press alt+shift+enter instead of enter

"Jennifer B" wrote:

I'm working on a spreadsheet (Excel 2003) wherein I have one a series of
tables on one worksheet, each one named, and a second spreadsheet that will
be used to coalate the required information for printing. The printing sheet
will pick up the required information based upon a date that I enter into a
certain cell. I know that I can use a combination of MAX and IF statements
to make this work, and I've got it working in another file, but as soon as I
change something, it replies "#VALUE". I seem to remember there is a
specific set of key strokes you must use in order to make this formula work
properly. I'm giving you a sample of the formula below:

=MAX(IF(('EmployeeName'!A23:A49=$C$3)*('EmployeeNa me'!A23:A490),('EmployeeName'!B23:B49)))

(I have noticed that the formulas on the other spreadsheet are showing up as:
{=MAX(IF(('EmployeeName'!A23:A49=$C$3)*('EmployeeN ame'!A23:A490),('EmployeeName'!B23:B49)))}
with the {} showing - but if I type them into the new workbook, they make
the formula text, rather than a formula, so I believe that it has something
to do with the mystery keystrokes.)

wherein
$C$3 is the date that is the comparison call-up (Same sheet as the formula
is placed on)

and
'EmployeeName'!A23:A49 is the list of dates in the table

and
"EmployeeName'!B23:B49 contains the value that we want displayed.


--

Dave Peterson