Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dynamic
I have an excel workbook with multiple work sheets (Summary, January,
february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dynamic
Having your formula would help. The INDIRECT function will accomplish what
you want. But without your formula, I cannot tell what is wrong. Tyro "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
Thanks for you reply tyro.
F7=G7 G7=September Formula is: =SUM(january:indirect($F$7)!B9) I also tried =SUM(january:indirect($G$7&!B9)) which creates an actual cell reference for the indirect function I can type =SUM(january:September!B9) and it works just fine When I watched the execution of the first or second formula I get a name error for January. The rest parses out fine but the name error overrides. Steve "Tyro" wrote: Having your formula would help. The INDIRECT function will accomplish what you want. But without your formula, I cannot tell what is wrong. Tyro "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
What is cell B9?
Tyro "SmilingSteve" wrote in message ... Thanks for you reply tyro. F7=G7 G7=September Formula is: =SUM(january:indirect($F$7)!B9) I also tried =SUM(january:indirect($G$7&!B9)) which creates an actual cell reference for the indirect function I can type =SUM(january:September!B9) and it works just fine When I watched the execution of the first or second formula I get a name error for January. The rest parses out fine but the name error overrides. Steve "Tyro" wrote: Having your formula would help. The INDIRECT function will accomplish what you want. But without your formula, I cannot tell what is wrong. Tyro "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
You appear to be wanting to do double indirection. That is to say, go to
cell F7 to get the address G7 then go to cell G7 and get the worksheet name, September. Is that correct? Tyro "SmilingSteve" wrote in message ... Thanks for you reply tyro. F7=G7 G7=September Formula is: =SUM(january:indirect($F$7)!B9) I also tried =SUM(january:indirect($G$7&!B9)) which creates an actual cell reference for the indirect function I can type =SUM(january:September!B9) and it works just fine When I watched the execution of the first or second formula I get a name error for January. The rest parses out fine but the name error overrides. Steve "Tyro" wrote: Having your formula would help. The INDIRECT function will accomplish what you want. But without your formula, I cannot tell what is wrong. Tyro "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dynamic
G7=September
Assuming you will *always* want the sum from January to ??? Try this: =SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100")) If that's too "obfuscated" then: Enter this formula in J1: =TEXT(ROWS(J$1:J1)*28,"mmmm") Enter this formula in K1: =INDIRECT(J1&"!B9") Then your sum formula would be: =SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0))) -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dynamic
Ooops!
Forgot something: Enter this formula in J1: Enter this formula in K1: Now, select both J1 and K1 and copy down to J12:K12 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... G7=September Assuming you will *always* want the sum from January to ??? Try this: =SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100")) If that's too "obfuscated" then: Enter this formula in J1: =TEXT(ROWS(J$1:J1)*28,"mmmm") Enter this formula in K1: =INDIRECT(J1&"!B9") Then your sum formula would be: =SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0))) -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
Tyro
I do not want to do the double indirect. It was the only way excel was letting me get the function to work. a single indirect would be great. B9 is the cell to be summed "Tyro" wrote: You appear to be wanting to do double indirection. That is to say, go to cell F7 to get the address G7 then go to cell G7 and get the worksheet name, September. Is that correct? Tyro "SmilingSteve" wrote in message ... Thanks for you reply tyro. F7=G7 G7=September Formula is: =SUM(january:indirect($F$7)!B9) I also tried =SUM(january:indirect($G$7&!B9)) which creates an actual cell reference for the indirect function I can type =SUM(january:September!B9) and it works just fine When I watched the execution of the first or second formula I get a name error for January. The rest parses out fine but the name error overrides. Steve "Tyro" wrote: Having your formula would help. The INDIRECT function will accomplish what you want. But without your formula, I cannot tell what is wrong. Tyro "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
Biff
Thanks for your input. I am not intimately familiar with a lot of excel formula so I will need time to understand what you suggest. I will get back to you. Is there a good reference for how excel processes its functions? Help gives the format and explanations of variables but not the how and why it works. "T. Valko" wrote: Ooops! Forgot something: Enter this formula in J1: Enter this formula in K1: Now, select both J1 and K1 and copy down to J12:K12 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... G7=September Assuming you will *always* want the sum from January to ??? Try this: =SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100")) If that's too "obfuscated" then: Enter this formula in J1: =TEXT(ROWS(J$1:J1)*28,"mmmm") Enter this formula in K1: =INDIRECT(J1&"!B9") Then your sum formula would be: =SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0))) -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
Excel's functions are just small programs written in a computer language
such as C++. Microsoft does not usually supply the source code for its software so you'll probably never know "how and why" it works. You could get some ideas on how things might work such as LOG which computes logarithms and SIN which computes sines by looking at books on mathematics that show formulas to do such things. In some cases, the hardware might have a single instruction to do the function. Tyro "SmilingSteve" wrote in message ... Biff Thanks for your input. I am not intimately familiar with a lot of excel formula so I will need time to understand what you suggest. I will get back to you. Is there a good reference for how excel processes its functions? Help gives the format and explanations of variables but not the how and why it works. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
If you'd like, I can put together a sample file that demonstrates both
methods I suggested. -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Biff Thanks for your input. I am not intimately familiar with a lot of excel formula so I will need time to understand what you suggest. I will get back to you. Is there a good reference for how excel processes its functions? Help gives the format and explanations of variables but not the how and why it works. "T. Valko" wrote: Ooops! Forgot something: Enter this formula in J1: Enter this formula in K1: Now, select both J1 and K1 and copy down to J12:K12 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... G7=September Assuming you will *always* want the sum from January to ??? Try this: =SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100")) If that's too "obfuscated" then: Enter this formula in J1: =TEXT(ROWS(J$1:J1)*28,"mmmm") Enter this formula in K1: =INDIRECT(J1&"!B9") Then your sum formula would be: =SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0))) -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
Biff
I have reviewed you equation and I have some questions. the month function reduces to "01-september" and returns a value of 9. How does "01-september" become a serial number equal to september? The inner indirect function reduces to "1:9" which becomes $1:$9. Why does this happen? The rows function reduces to 9 which I think I understand. The text function reduces to 252,"mmmm" which returns september. Why? The outer indirect function reduces to "september!B9". Why do you use the sumif function and the sumproduct function? Thanks "T. Valko" wrote: If you'd like, I can put together a sample file that demonstrates both methods I suggested. -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Biff Thanks for your input. I am not intimately familiar with a lot of excel formula so I will need time to understand what you suggest. I will get back to you. Is there a good reference for how excel processes its functions? Help gives the format and explanations of variables but not the how and why it works. "T. Valko" wrote: Ooops! Forgot something: Enter this formula in J1: Enter this formula in K1: Now, select both J1 and K1 and copy down to J12:K12 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... G7=September Assuming you will *always* want the sum from January to ??? Try this: =SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100")) If that's too "obfuscated" then: Enter this formula in J1: =TEXT(ROWS(J$1:J1)*28,"mmmm") Enter this formula in K1: =INDIRECT(J1&"!B9") Then your sum formula would be: =SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0))) -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
This is a copy of what's stated for the MONTH function in Excel help:
"Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December)." If you enter a date such as 01-September, Excel converts that to a date serial number of 39692 which is September 1, 2008. Thus the MONTH function applied to that date returns 9 as September is the 9th month of the year. As for the TEXT function returning September, the 252 is the date serial number for September 8, 1900 and when that is formatted as "mmmm", it becomes September. As for your other questions, a good book on Excel such as Excel 2003 Bible or Excel 2007 Bible by John Walkenbach would help you. Tyro "SmilingSteve" wrote in message ... Biff I have reviewed you equation and I have some questions. the month function reduces to "01-september" and returns a value of 9. How does "01-september" become a serial number equal to september? The inner indirect function reduces to "1:9" which becomes $1:$9. Why does this happen? The rows function reduces to 9 which I think I understand. The text function reduces to 252,"mmmm" which returns september. Why? The outer indirect function reduces to "september!B9". Why do you use the sumif function and the sumproduct function? Thanks "T. Valko" wrote: If you'd like, I can put together a sample file that demonstrates both methods I suggested. -- Biff Microsoft Excel MVP |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
How does "01-september" become a serial number equal to september?
That's a "quirk" of Excel. Excel is very eager to indentify dates and times. MONTH("01-Septemnber") That is clearly not a date serial number, it's a text string, but in its eagerness to indentify dates/times Excel will "accept" that text string in certain DATE related functions and evaluate it as a legitimate date string. In the above, since the year number has been left out it defaults to the current year. So: MONTH("01-Septemnber") Evaluates to the date September 1 2008 which is month number 9. The inner indirect function reduces to "1:9" which becomes $1:$9. Why does this happen It starts out as a text string within INDIRECT as "1:9". The INDIRECT function converts TEXT representations of references to usable references that can be passed to other functions. In this case we use INDIRECT to "build" a text representation of the reference "1:9" which is in turn passed to the ROW function which evaluates it as ROW($1:$9). ROW($1:$9) is then passed to the TEXT function as an array that is comprised of: {1,2,3,4,5,6,7,8,9} Each element of that array is then multiplied by 28: 1*28 = 28 2*28 = 56 3*28 = 84 etc etc 9*28 = 252 These multiplied values are evaluated as *date serial numbers for the year of 1900*. We're using the TEXT function to return the month names based on these date serial numbers: Date serial number: 28 = January 28 1900 56 = February 25 1900 84 = March 24 1900 etc etc 252 = Septemeber 8 1900 So the TEXT function evalautes those date serial numbers and returns the name of the month as a text string. These month names are then concatenated along with the text string "!B9" and passed to the outer INDIRECT function as an array: January!B9 February!B9 March!B9 etc etc September!B9 This array of text references is then converted by the outer INDIRECT function into usable references that are in turn passed to the SUMIF function. Why do you use the sumif function and the sumproduct function? Excel doesn't handle "3D" references very well. In fact, very few functions can work with 3D references and those that do have a very strict syntax. So, we use the combination of SUMPRODUCT and SUMIF to "trick" Excel into calculating a "3D" formula. In essence, what's happening with this formula is that it's calculating an array of SUMIF functions in which we had to use all of the INDIRECTS to "build" the references. =SUMPRODUCT ( SUMIF(January!B9,"<1E100") SUMIF(February!B9,"<1E100") SUMIF(March!B9,"<1E100") SUMIF(April!B9,"<1E100") SUMIF(May!B9,"<1E100") SUMIF(June!B9,"<1E100") SUMIF(July!B9,"<1E100") SUMIF(August!B9,"<1E100") SUMIF(September!B9,"<1E100") ) We use SUMPRODUCT because it can process arrays without having to be array entered (CTRL, SHIFT, ENTER). -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Biff I have reviewed you equation and I have some questions. the month function reduces to "01-september" and returns a value of 9. How does "01-september" become a serial number equal to september? The inner indirect function reduces to "1:9" which becomes $1:$9. Why does this happen? The rows function reduces to 9 which I think I understand. The text function reduces to 252,"mmmm" which returns september. Why? The outer indirect function reduces to "september!B9". Why do you use the sumif function and the sumproduct function? Thanks "T. Valko" wrote: If you'd like, I can put together a sample file that demonstrates both methods I suggested. -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Biff Thanks for your input. I am not intimately familiar with a lot of excel formula so I will need time to understand what you suggest. I will get back to you. Is there a good reference for how excel processes its functions? Help gives the format and explanations of variables but not the how and why it works. "T. Valko" wrote: Ooops! Forgot something: Enter this formula in J1: Enter this formula in K1: Now, select both J1 and K1 and copy down to J12:K12 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... G7=September Assuming you will *always* want the sum from January to ??? Try this: =SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100")) If that's too "obfuscated" then: Enter this formula in J1: =TEXT(ROWS(J$1:J1)*28,"mmmm") Enter this formula in K1: =INDIRECT(J1&"!B9") Then your sum formula would be: =SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0))) -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
Thanks so much gentlemen. Very informative. Excel is not intuitively
obvious in respect to 3d refereces. "T. Valko" wrote: How does "01-september" become a serial number equal to september? That's a "quirk" of Excel. Excel is very eager to indentify dates and times. MONTH("01-Septemnber") That is clearly not a date serial number, it's a text string, but in its eagerness to indentify dates/times Excel will "accept" that text string in certain DATE related functions and evaluate it as a legitimate date string. In the above, since the year number has been left out it defaults to the current year. So: MONTH("01-Septemnber") Evaluates to the date September 1 2008 which is month number 9. The inner indirect function reduces to "1:9" which becomes $1:$9. Why does this happen It starts out as a text string within INDIRECT as "1:9". The INDIRECT function converts TEXT representations of references to usable references that can be passed to other functions. In this case we use INDIRECT to "build" a text representation of the reference "1:9" which is in turn passed to the ROW function which evaluates it as ROW($1:$9). ROW($1:$9) is then passed to the TEXT function as an array that is comprised of: {1,2,3,4,5,6,7,8,9} Each element of that array is then multiplied by 28: 1*28 = 28 2*28 = 56 3*28 = 84 etc etc 9*28 = 252 These multiplied values are evaluated as *date serial numbers for the year of 1900*. We're using the TEXT function to return the month names based on these date serial numbers: Date serial number: 28 = January 28 1900 56 = February 25 1900 84 = March 24 1900 etc etc 252 = Septemeber 8 1900 So the TEXT function evalautes those date serial numbers and returns the name of the month as a text string. These month names are then concatenated along with the text string "!B9" and passed to the outer INDIRECT function as an array: January!B9 February!B9 March!B9 etc etc September!B9 This array of text references is then converted by the outer INDIRECT function into usable references that are in turn passed to the SUMIF function. Why do you use the sumif function and the sumproduct function? Excel doesn't handle "3D" references very well. In fact, very few functions can work with 3D references and those that do have a very strict syntax. So, we use the combination of SUMPRODUCT and SUMIF to "trick" Excel into calculating a "3D" formula. In essence, what's happening with this formula is that it's calculating an array of SUMIF functions in which we had to use all of the INDIRECTS to "build" the references. =SUMPRODUCT ( SUMIF(January!B9,"<1E100") SUMIF(February!B9,"<1E100") SUMIF(March!B9,"<1E100") SUMIF(April!B9,"<1E100") SUMIF(May!B9,"<1E100") SUMIF(June!B9,"<1E100") SUMIF(July!B9,"<1E100") SUMIF(August!B9,"<1E100") SUMIF(September!B9,"<1E100") ) We use SUMPRODUCT because it can process arrays without having to be array entered (CTRL, SHIFT, ENTER). -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Biff I have reviewed you equation and I have some questions. the month function reduces to "01-september" and returns a value of 9. How does "01-september" become a serial number equal to september? The inner indirect function reduces to "1:9" which becomes $1:$9. Why does this happen? The rows function reduces to 9 which I think I understand. The text function reduces to 252,"mmmm" which returns september. Why? The outer indirect function reduces to "september!B9". Why do you use the sumif function and the sumproduct function? Thanks "T. Valko" wrote: If you'd like, I can put together a sample file that demonstrates both methods I suggested. -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Biff Thanks for your input. I am not intimately familiar with a lot of excel formula so I will need time to understand what you suggest. I will get back to you. Is there a good reference for how excel processes its functions? Help gives the format and explanations of variables but not the how and why it works. "T. Valko" wrote: Ooops! Forgot something: Enter this formula in J1: Enter this formula in K1: Now, select both J1 and K1 and copy down to J12:K12 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... G7=September Assuming you will *always* want the sum from January to ??? Try this: =SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100")) If that's too "obfuscated" then: Enter this formula in J1: =TEXT(ROWS(J$1:J1)*28,"mmmm") Enter this formula in K1: =INDIRECT(J1&"!B9") Then your sum formula would be: =SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0))) -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do i set up a 3d reference where one cell reference is dyn
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Thanks so much gentlemen. Very informative. Excel is not intuitively obvious in respect to 3d refereces. "T. Valko" wrote: How does "01-september" become a serial number equal to september? That's a "quirk" of Excel. Excel is very eager to indentify dates and times. MONTH("01-Septemnber") That is clearly not a date serial number, it's a text string, but in its eagerness to indentify dates/times Excel will "accept" that text string in certain DATE related functions and evaluate it as a legitimate date string. In the above, since the year number has been left out it defaults to the current year. So: MONTH("01-Septemnber") Evaluates to the date September 1 2008 which is month number 9. The inner indirect function reduces to "1:9" which becomes $1:$9. Why does this happen It starts out as a text string within INDIRECT as "1:9". The INDIRECT function converts TEXT representations of references to usable references that can be passed to other functions. In this case we use INDIRECT to "build" a text representation of the reference "1:9" which is in turn passed to the ROW function which evaluates it as ROW($1:$9). ROW($1:$9) is then passed to the TEXT function as an array that is comprised of: {1,2,3,4,5,6,7,8,9} Each element of that array is then multiplied by 28: 1*28 = 28 2*28 = 56 3*28 = 84 etc etc 9*28 = 252 These multiplied values are evaluated as *date serial numbers for the year of 1900*. We're using the TEXT function to return the month names based on these date serial numbers: Date serial number: 28 = January 28 1900 56 = February 25 1900 84 = March 24 1900 etc etc 252 = Septemeber 8 1900 So the TEXT function evalautes those date serial numbers and returns the name of the month as a text string. These month names are then concatenated along with the text string "!B9" and passed to the outer INDIRECT function as an array: January!B9 February!B9 March!B9 etc etc September!B9 This array of text references is then converted by the outer INDIRECT function into usable references that are in turn passed to the SUMIF function. Why do you use the sumif function and the sumproduct function? Excel doesn't handle "3D" references very well. In fact, very few functions can work with 3D references and those that do have a very strict syntax. So, we use the combination of SUMPRODUCT and SUMIF to "trick" Excel into calculating a "3D" formula. In essence, what's happening with this formula is that it's calculating an array of SUMIF functions in which we had to use all of the INDIRECTS to "build" the references. =SUMPRODUCT ( SUMIF(January!B9,"<1E100") SUMIF(February!B9,"<1E100") SUMIF(March!B9,"<1E100") SUMIF(April!B9,"<1E100") SUMIF(May!B9,"<1E100") SUMIF(June!B9,"<1E100") SUMIF(July!B9,"<1E100") SUMIF(August!B9,"<1E100") SUMIF(September!B9,"<1E100") ) We use SUMPRODUCT because it can process arrays without having to be array entered (CTRL, SHIFT, ENTER). -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Biff I have reviewed you equation and I have some questions. the month function reduces to "01-september" and returns a value of 9. How does "01-september" become a serial number equal to september? The inner indirect function reduces to "1:9" which becomes $1:$9. Why does this happen? The rows function reduces to 9 which I think I understand. The text function reduces to 252,"mmmm" which returns september. Why? The outer indirect function reduces to "september!B9". Why do you use the sumif function and the sumproduct function? Thanks "T. Valko" wrote: If you'd like, I can put together a sample file that demonstrates both methods I suggested. -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... Biff Thanks for your input. I am not intimately familiar with a lot of excel formula so I will need time to understand what you suggest. I will get back to you. Is there a good reference for how excel processes its functions? Help gives the format and explanations of variables but not the how and why it works. "T. Valko" wrote: Ooops! Forgot something: Enter this formula in J1: Enter this formula in K1: Now, select both J1 and K1 and copy down to J12:K12 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... G7=September Assuming you will *always* want the sum from January to ??? Try this: =SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100")) If that's too "obfuscated" then: Enter this formula in J1: =TEXT(ROWS(J$1:J1)*28,"mmmm") Enter this formula in K1: =INDIRECT(J1&"!B9") Then your sum formula would be: =SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0))) -- Biff Microsoft Excel MVP "SmilingSteve" wrote in message ... I have an excel workbook with multiple work sheets (Summary, January, february..., December) On the Summary worksheet I want to create a 3d reference summing all B9 cells across the monthly worksheets. I can do this for a specified range of months, like =sum(january:December!B9. I want the second reference, December, to be variable, like february or september. I tried indirect but it evaluates the string left to right and consequently gives me a name error. Any suggestions as to how I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic cell reference using INDIRECT.. | Excel Worksheet Functions | |||
dynamic reference to cell?? | Excel Worksheet Functions | |||
Dynamic cell reference | Excel Discussion (Misc queries) | |||
Dynamic cell reference? | Excel Discussion (Misc queries) | |||
dynamic external cell reference | Excel Worksheet Functions |