Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using named ranges in a formula
I have created a number of named ranges as follows:
Worksheet2 Column B:B named January Worksheet2 Column C:C named February Worksheet2 Column D:D named March etc etc I have a formula in a seperate worksheet as follows: =sumif(worksheet2!A:A,A1,B1) Therefore I would like to type in "March" in "B1" and the formula above recognises the named range "March". If I then type in "January" in B1 the formula will recalculate based on the data in named range "January". Can anyone advise if this is possible and if so how it can be done? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using named ranges in a formula
Try
=SUMIF(INDIRECT(B1),A1) Note that the workbook with the named ranges need to be open -- Regards, Peo Sjoblom "Steve_H" wrote in message ... I have created a number of named ranges as follows: Worksheet2 Column B:B named January Worksheet2 Column C:C named February Worksheet2 Column D:D named March etc etc I have a formula in a seperate worksheet as follows: =sumif(worksheet2!A:A,A1,B1) Therefore I would like to type in "March" in "B1" and the formula above recognises the named range "March". If I then type in "January" in B1 the formula will recalculate based on the data in named range "January". Can anyone advise if this is possible and if so how it can be done? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using named ranges in a formula
Yes it can be done using INDIRECT. But I'm not sure what you are
trying to do. You will be adding the range B1 if A1 matches the named range, but how can you add the word "March"? Pete On Apr 22, 3:16*pm, Steve_H wrote: I have created a number of named ranges as follows: Worksheet2 Column B:B named January Worksheet2 Column C:C named February Worksheet2 Column D:D named March etc etc I have a formula in a seperate worksheet as follows: =sumif(worksheet2!A:A,A1,B1) Therefore I would like to type in "March" in "B1" and the formula above recognises the named range "March". *If I then type in "January" in B1 the formula will recalculate based on the data in named range "January". *Can anyone advise if this is possible and if so how it can be done? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using named ranges in a formula
Hi Peo
Thanks for your help. I had to do a slight deviation from your suggestion but the function INDIRECT, this is what worked. =SUMIF(worksheet2!A:A,A1,INDIRECT($B$1)) Thanks again. "Peo Sjoblom" wrote: Try =SUMIF(INDIRECT(B1),A1) Note that the workbook with the named ranges need to be open -- Regards, Peo Sjoblom "Steve_H" wrote in message ... I have created a number of named ranges as follows: Worksheet2 Column B:B named January Worksheet2 Column C:C named February Worksheet2 Column D:D named March etc etc I have a formula in a seperate worksheet as follows: =sumif(worksheet2!A:A,A1,B1) Therefore I would like to type in "March" in "B1" and the formula above recognises the named range "March". If I then type in "January" in B1 the formula will recalculate based on the data in named range "January". Can anyone advise if this is possible and if so how it can be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula To Use A Dynamic Named Ranges With VLOOKUP On Different Workbooks | Excel Worksheet Functions | |||
named ranges in multiple criteria formula | Excel Discussion (Misc queries) | |||
Use named ranges in array formula | Excel Worksheet Functions | |||
using named ranges for formula abbreviations | Excel Worksheet Functions | |||
UPDATED - Referencing named Ranges within a Nested IF formula | Excel Worksheet Functions |