| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Named Ranges
Excel allows a defined name to represent an array range or array constant. It
allows a name to represent a formula which is then evaluated to return a value. Why can I not define conditional arrays like these: =IF(!$A$1=Q1,{Jan;Feb;Mar} ,IF(!$A$1=Q2, {Apr;May;Jun})) =IF(!$A$1="Q1",!$A$2:!$A$4,IF(!$A$1="Q2",!$A$5:!$A $7)) When I try to enter their names in a vertical range, the first value is returned in all cells. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Named Ranges
Try substituting commas for the semicolons. This works for me when used
in Insert/Define/Name: =IF(!$A$1="Q1",{"Jan","Feb","Mar"},IF(!$A$1="Q2", {"Apr","May","Jun"})) In article , Tevuna wrote: Excel allows a defined name to represent an array range or array constant. It allows a name to represent a formula which is then evaluated to return a value. Why can I not define conditional arrays like these: =IF(!$A$1=Q1,{Jan;Feb;Mar} ,IF(!$A$1=Q2, {Apr;May;Jun})) =IF(!$A$1="Q1",!$A$2:!$A$4,IF(!$A$1="Q2",!$A$5:!$A $7)) When I try to enter their names in a vertical range, the first value is returned in all cells. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Named Ranges
Doesn't work for me in a horizontal, and certainly not in vertical, range.
"JE McGimpsey" wrote: Try substituting commas for the semicolons. This works for me when used in Insert/Define/Name: =IF(!$A$1="Q1",{"Jan","Feb","Mar"},IF(!$A$1="Q2", {"Apr","May","Jun"})) In article , Tevuna wrote: Excel allows a defined name to represent an array range or array constant. It allows a name to represent a formula which is then evaluated to return a value. Why can I not define conditional arrays like these: =IF(!$A$1=Γ’¬ΒQ1Γ’¬Β,{Γ’¬ΕJanΓ’¬Β;Γ’ ¬ΒFebΓ’¬Β;Γ’¬ΒMarΓ’¬Β},IF(!$A$1=Γ’¬ΒQ 2Γ’¬Β, {Γ’¬ΕAprΓ’¬Β;Γ’¬ΒMayΓ’¬Β;Γ’¬ΒJunΓ’ ¬Β})) =IF(!$A$1="Q1",!$A$2:!$A$4,IF(!$A$1="Q2",!$A$5:!$A $7)) When I try to enter their names in a vertical range, the first value is returned in all cells. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum of named ranges conditional to date? | Excel Worksheet Functions | |||
| Named Ranges | Excel Discussion (Misc queries) | |||
| Conditional Sum Wizard with dynamic named ranges | Excel Worksheet Functions | |||
| Named Ranges in VBA | Excel Discussion (Misc queries) | |||
| Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Linear Mode
