Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and match among sheets
Hi,
First of all, I know how to use Index and Match functions without any trouble, but this new task is really giving me the troubles. I have, for example, 12 sheets, each representing one month, and in each one 31 columns, representing each day of the month, and sum for each column. Dates of months are set in Column 2, from B2:AF2. Sum row is set in column 28, from B28:AF28. G1 cell in 13th sheet represents today(). Now, on the 13th sheet, I wanted to present sum for the present day, so I used this formula: =INDEX(January:Decembar!2:28,January:Decembar!28:2 8,MATCH(G1,January:Decembar!2:2,0)) But, it doesn't work. It shows #ref! in row part, and #value! in column part. What am I doing wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and match among sheets
As described this should work
=SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C12&"'!B2:AF2"), G1,INDIRECT("'"&C1:C12&"'! B28:AF28"))) where C1:C12 is a range housing the relevant sheetnames in separate cells. But I fear it may not as it would require a date on the month sheets in row 2 of 17th April, then it would need to be 18th April tomorrow, whereas I am sure you will have 1st April, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "umba-sr" wrote in message ... Hi, First of all, I know how to use Index and Match functions without any trouble, but this new task is really giving me the troubles. I have, for example, 12 sheets, each representing one month, and in each one 31 columns, representing each day of the month, and sum for each column. Dates of months are set in Column 2, from B2:AF2. Sum row is set in column 28, from B28:AF28. G1 cell in 13th sheet represents today(). Now, on the 13th sheet, I wanted to present sum for the present day, so I used this formula: =INDEX(January:Decembar!2:28,January:Decembar!28:2 8,MATCH(G1,January:Decemba r!2:2,0)) But, it doesn't work. It shows #ref! in row part, and #value! in column part. What am I doing wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and match among sheets
Here is a better way
=SUMPRODUCT(N(OFFSET(INDIRECT("'"&C1:C12&"'!B28"), ,MONTH(G1)-1))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "umba-sr" wrote in message ... Hi, First of all, I know how to use Index and Match functions without any trouble, but this new task is really giving me the troubles. I have, for example, 12 sheets, each representing one month, and in each one 31 columns, representing each day of the month, and sum for each column. Dates of months are set in Column 2, from B2:AF2. Sum row is set in column 28, from B28:AF28. G1 cell in 13th sheet represents today(). Now, on the 13th sheet, I wanted to present sum for the present day, so I used this formula: =INDEX(January:Decembar!2:28,January:Decembar!28:2 8,MATCH(G1,January:Decemba r!2:2,0)) But, it doesn't work. It shows #ref! in row part, and #value! in column part. What am I doing wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and match among sheets
Thanks a lot, it works.
But not both. Amazingly, only the first one works, second one seems to be working, but it's returning some other number, whcih is a lot larger than the correct one. "Bob Phillips" wrote: Here is a better way =SUMPRODUCT(N(OFFSET(INDIRECT("'"&C1:C12&"'!B28"), ,MONTH(G1)-1))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "umba-sr" wrote in message ... Hi, First of all, I know how to use Index and Match functions without any trouble, but this new task is really giving me the troubles. I have, for example, 12 sheets, each representing one month, and in each one 31 columns, representing each day of the month, and sum for each column. Dates of months are set in Column 2, from B2:AF2. Sum row is set in column 28, from B28:AF28. G1 cell in 13th sheet represents today(). Now, on the 13th sheet, I wanted to present sum for the present day, so I used this formula: =INDEX(January:Decembar!2:28,January:Decembar!28:2 8,MATCH(G1,January:Decemba r!2:2,0)) But, it doesn't work. It shows #ref! in row part, and #value! in column part. What am I doing wrong? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index and match among sheets
Hi,
I don't know anything about the solution for your problem and I'm not trying to be a smartass (believe me I'm not) but "december" is not spelt "decembar". It makes little difference to myself and most people but I think it might mean quite a lot to EXCEL. :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDEX and MATCH to find data in 2 different sheets | Excel Worksheet Functions | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
index & match with links for updating to other workbooks | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |