Cell reference for year in formula
 

The following formula works fine, but I want the year for the date within the
quotes to come from a cell reference. Does anyone know how I need to alter
the formula to make this happen? I've tried to replace the 09 with a cell
reference but have had no luck.

=SUMIFS('Check Register'!H2:H1000,'Check
Register'!B2:B1000,"3/31/09",'Check Register'!B2:B1000,"<4/15/09",'Check
Register'!D2:D1000,A43)

Cell reference for year in formula
 

"3/31/09"

becomes

"3/31/"& A1

where A1 contains 09, so it has to be text, not a number. You can achieve
that by formatting the cell as text before you enter 09
If the year is the result of a calculation, (so it is the number 9) and is
in B1, then in A1 enter:

=TEXT(B1,"00")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Cush" wrote in message
...
The following formula works fine, but I want the year for the date within
the
quotes to come from a cell reference. Does anyone know how I need to
alter
the formula to make this happen? I've tried to replace the 09 with a cell
reference but have had no luck.

=SUMIFS('Check Register'!H2:H1000,'Check
Register'!B2:B1000,"3/31/09",'Check Register'!B2:B1000,"<4/15/09",'Check
Register'!D2:D1000,A43)