Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate working days but change working week | Excel Discussion (Misc queries) | |||
Making weekend days working days - the system cuts the working tim | Excel Discussion (Misc queries) | |||
ZERO VALUE NOT WORKING | Excel Discussion (Misc queries) | |||
IF not working | Excel Worksheet Functions | |||
Add-ins not working | Links and Linking in Excel |