Index and match among sheets

[umba-sr]

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:28,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?

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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:28,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?

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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:28,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?

[umba-sr]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:28,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?
>
>
>

[MartinW]

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. :-)