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
|