SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),--('Budget
Expense'!$E$3:$E$10393=D$2),--('Budget Expense'!$J$3:$J$10393=$A19),'Budget
Expense'!$F$3:$F$10393)

This formula works well right now, How can I put INDIRECT function so i can
referance in Cell $D$3 Actual if we want to look at actuals sheet and Budget
if I want to look in budget Sheet.

=SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),
--('Budget Expense'!$E$3:$E$10393=D$2),
--('Budget Expense'!$J$3:$J$10393=$A19),
'Budget Expense'!$F$3:$F$10393)

Becomes

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393")=$A$1),
--(indirect("'" & $d$3 & "'!$E$3:$E$10393")=D$2),
--(indirect("'" & $d$3 & "'!$J$3:$J$10393")=$A19),
indirect("'" & $d$3 & "'!$F$3:$F$10393")


MESTRELLA29 wrote:

SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),--('Budget
Expense'!$E$3:$E$10393=D$2),--('Budget Expense'!$J$3:$J$10393=$A19),'Budget
Expense'!$F$3:$F$10393)

This formula works well right now, How can I put INDIRECT function so i can
referance in Cell $D$3 Actual if we want to look at actuals sheet and Budget
if I want to look in budget Sheet.

--

Dave Peterson

=SUMPRODUCT(--(YEAR(INDIRECT("'"&D3&"'!$D$3:$D$10393"))=$A$1),
--(INDIRECT("'"&D3&"'!$E$3:$E$10393")=D$2),
--(INDIRECT("'"&D3&"'!$J$3:$J$10393")=$A19),INDIRECT ("'"&D3&"'!$F$3:$F$10393"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"MESTRELLA29" wrote in message
...
SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),--('Budget
Expense'!$E$3:$E$10393=D$2),--('Budget
Expense'!$J$3:$J$10393=$A19),'Budget
Expense'!$F$3:$F$10393)

This formula works well right now, How can I put INDIRECT function so i
can
referance in Cell $D$3 Actual if we want to look at actuals sheet and
Budget
if I want to look in budget Sheet.

Hi,

Try this:

=SUMPRODUCT(--(YEAR(INDIRECT("'"&D1&"
Expense'!$D$3:$D$10393"))=$A$1),--INDIRECT("'"&D1&"
Expense'!$E$3:$E$10393")=D$2,--(INDIRECT("'"&D1&"
Expense'!$J$3:$J$10393")=$A19),INDIRECT("'"&D1&" Expense'!$F$3:$F$10393"))

--
Cheers,
Shane Devenshire


"MESTRELLA29" wrote:

SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),--('Budget
Expense'!$E$3:$E$10393=D$2),--('Budget Expense'!$J$3:$J$10393=$A19),'Budget
Expense'!$F$3:$F$10393)

This formula works well right now, How can I put INDIRECT function so i can
referance in Cell $D$3 Actual if we want to look at actuals sheet and Budget
if I want to look in budget Sheet.

Hi,

I did not point out the difference between my formula and the prior
solutions - you stated you wanted to type Budget into cell D3 so since the
sheet is named Budget Expense you need to include the text Expense in your
formula.

--
Cheers,
Shane Devenshire


"MESTRELLA29" wrote:

SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),--('Budget
Expense'!$E$3:$E$10393=D$2),--('Budget Expense'!$J$3:$J$10393=$A19),'Budget
Expense'!$F$3:$F$10393)

This formula works well right now, How can I put INDIRECT function so i can
referance in Cell $D$3 Actual if we want to look at actuals sheet and Budget
if I want to look in budget Sheet.

For some reason this is not working, it is giving me a result but it is not
accurate,
Reult in This Formula is 586
=SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),
--('Budget Expense'!$E$3:$E$10393=D$2),
--('Budget Expense'!$J$3:$J$10393=$A19),
'Budget Expense'!$F$3:$F$10393)
Becomes
Result for this formula is $1,112,594
=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393")=$A$1),
--(indirect("'" & $d$3 & "'!$E$3:$E$10393")=D$2),
--(indirect("'" & $d$3 & "'!$J$3:$J$10393")=$A19),
indirect("'" & $d$3 & "'!$F$3:$F$10393")




MESTRELLA29 wrote:

SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),--('Budget
Expense'!$E$3:$E$10393=D$2),--('Budget Expense'!$J$3:$J$10393=$A19),'Budget
Expense'!$F$3:$F$10393)

This formula works well right now, How can I put INDIRECT function so i can
referance in Cell $D$3 Actual if we want to look at actuals sheet and Budget
if I want to look in budget Sheet.

--

Dave Peterson

And if you just hardcode the worksheet name into the formula, what's returned?

I'd bet that the formula is ok, but your data is either not numeric or not
matching the rows you think it should.

MESTRELLA29 wrote:

For some reason this is not working, it is giving me a result but it is not
accurate,
Reult in This Formula is 586
=SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),
--('Budget Expense'!$E$3:$E$10393=D$2),
--('Budget Expense'!$J$3:$J$10393=$A19),
'Budget Expense'!$F$3:$F$10393)
Becomes
Result for this formula is $1,112,594
=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393")=$A$1),
--(indirect("'" & $d$3 & "'!$E$3:$E$10393")=D$2),
--(indirect("'" & $d$3 & "'!$J$3:$J$10393")=$A19),
indirect("'" & $d$3 & "'!$F$3:$F$10393")


MESTRELLA29 wrote:

SUMPRODUCT(--(YEAR('Budget Expense'!$D$3:$D$10393)=$A$1),--('Budget
Expense'!$E$3:$E$10393=D$2),--('Budget Expense'!$J$3:$J$10393=$A19),'Budget
Expense'!$F$3:$F$10393)

This formula works well right now, How can I put INDIRECT function so i can
referance in Cell $D$3 Actual if we want to look at actuals sheet and Budget
if I want to look in budget Sheet.

--

Dave Peterson


--

Dave Peterson

Ok maybe this can help out.

I change the Name of the Sheet to Actual and Budget

=SUMPRODUCT(--(YEAR(Budget!$D$3:$D$10000)=$A$1),--(Budget!$E$3:$E$10000=D$2),--(Budget!$J$3:$J$10000=$A37),Budget!$F$3:$F$10000)
This Need to = 586
and equals = $1,112,594

=SUMPRODUCT(--(YEAR(Actual!$D$3:$D$10000)=$A$1),--(Actual!$E$3:$E$10000=D$2),--(Actual!$J$3:$J$10000=$A37),Actual!$F$3:$F$10000)

This Need to = 1.40
and equals = 2671

I see the formula and it should worke but I do not understand why it still
wrong, now the totals for $F3:$F10000 in Budget is 94500 only not 1,112,594
so I do not know where is this getting the data.

There's a missing ")" in the INDIRECT version for the YEAR function:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393")=$A$1),

Should be:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393"))=$A$1),

Also, you can save a few keystrokes by eliminating the $ signs in the array
reference of INDIRECT:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!D3:D10393"))=$A$1),

Since INDIRECT evaluates this as a TEXT string the references will not
change if copied and is in essence an absolute reference with needing the $
signs.



--
Biff
Microsoft Excel MVP


"MESTRELLA29" wrote in message
...
Ok maybe this can help out.

I change the Name of the Sheet to Actual and Budget

=SUMPRODUCT(--(YEAR(Budget!$D$3:$D$10000)=$A$1),--(Budget!$E$3:$E$10000=D$2),--(Budget!$J$3:$J$10000=$A37),Budget!$F$3:$F$10000)
This Need to = 586
and equals = $1,112,594

=SUMPRODUCT(--(YEAR(Actual!$D$3:$D$10000)=$A$1),--(Actual!$E$3:$E$10000=D$2),--(Actual!$J$3:$J$10000=$A37),Actual!$F$3:$F$10000)

This Need to = 1.40
and equals = 2671

I see the formula and it should worke but I do not understand why it still
wrong, now the totals for $F3:$F10000 in Budget is 94500 only not
1,112,594
so I do not know where is this getting the data.

Ooops!

Typo:

.....and is in essence an absolute reference with needing the $ signs.

Should be:

......and is in essence an absolute reference without needing the $ signs.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
There's a missing ")" in the INDIRECT version for the YEAR function:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393")=$A$1),

Should be:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393"))=$A$1),

Also, you can save a few keystrokes by eliminating the $ signs in the
array reference of INDIRECT:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!D3:D10393"))=$A$1),

Since INDIRECT evaluates this as a TEXT string the references will not
change if copied and is in essence an absolute reference with needing the
$ signs.



--
Biff
Microsoft Excel MVP


"MESTRELLA29" wrote in message
...
Ok maybe this can help out.

I change the Name of the Sheet to Actual and Budget

=SUMPRODUCT(--(YEAR(Budget!$D$3:$D$10000)=$A$1),--(Budget!$E$3:$E$10000=D$2),--(Budget!$J$3:$J$10000=$A37),Budget!$F$3:$F$10000)
This Need to = 586
and equals = $1,112,594

=SUMPRODUCT(--(YEAR(Actual!$D$3:$D$10000)=$A$1),--(Actual!$E$3:$E$10000=D$2),--(Actual!$J$3:$J$10000=$A37),Actual!$F$3:$F$10000)

This Need to = 1.40
and equals = 2671

I see the formula and it should worke but I do not understand why it
still
wrong, now the totals for $F3:$F10000 in Budget is 94500 only not
1,112,594
so I do not know where is this getting the data.

We need to compare the two results of the two different versions of the formula.

What do you get when you use the =indirect() version and Budget in D3?
What do you get when you use the =indirect() version and Actual in D3?

If those variations of the formulas return the same as when you use the name,
then it's your data. Check for strings that look like numbers but are really
text.


MESTRELLA29 wrote:

Ok maybe this can help out.

I change the Name of the Sheet to Actual and Budget

=SUMPRODUCT(--(YEAR(Budget!$D$3:$D$10000)=$A$1),--(Budget!$E$3:$E$10000=D$2),--(Budget!$J$3:$J$10000=$A37),Budget!$F$3:$F$10000)
This Need to = 586
and equals = $1,112,594

=SUMPRODUCT(--(YEAR(Actual!$D$3:$D$10000)=$A$1),--(Actual!$E$3:$E$10000=D$2),--(Actual!$J$3:$J$10000=$A37),Actual!$F$3:$F$10000)

This Need to = 1.40
and equals = 2671

I see the formula and it should worke but I do not understand why it still
wrong, now the totals for $F3:$F10000 in Budget is 94500 only not 1,112,594
so I do not know where is this getting the data.

--

Dave Peterson

Thanks for the correction, Biff.

"T. Valko" wrote:

There's a missing ")" in the INDIRECT version for the YEAR function:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393")=$A$1),

Should be:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!$D$3:$D$10393"))=$A$1),

Also, you can save a few keystrokes by eliminating the $ signs in the array
reference of INDIRECT:

=SUMPRODUCT(--(YEAR(indirect("'" & $d$3 & "'!D3:D10393"))=$A$1),

Since INDIRECT evaluates this as a TEXT string the references will not
change if copied and is in essence an absolute reference with needing the $
signs.

--
Biff
Microsoft Excel MVP

"MESTRELLA29" wrote in message
...
Ok maybe this can help out.

I change the Name of the Sheet to Actual and Budget

=SUMPRODUCT(--(YEAR(Budget!$D$3:$D$10000)=$A$1),--(Budget!$E$3:$E$10000=D$2),--(Budget!$J$3:$J$10000=$A37),Budget!$F$3:$F$10000)
This Need to = 586
and equals = $1,112,594

=SUMPRODUCT(--(YEAR(Actual!$D$3:$D$10000)=$A$1),--(Actual!$E$3:$E$10000=D$2),--(Actual!$J$3:$J$10000=$A37),Actual!$F$3:$F$10000)

This Need to = 1.40
and equals = 2671

I see the formula and it should worke but I do not understand why it still
wrong, now the totals for $F3:$F10000 in Budget is 94500 only not
1,112,594
so I do not know where is this getting the data.

--

Dave Peterson