Select correct worksheet based on data entered into a cell

[Harry Stevens]

To All,
I am a novice at programming and excel and have spent the better part
of today searching the Internet and newsgroups for an answer to my
problem. I have recently taken over the duties as treasurer for our
homeowners association and trying to set up an workbook to handle and
track our financial data. I took most of my current worksheet from
different templates on Office's website. I am trying to automate my
Income Statement so that I can switch between and/or compare different
budget years. I currently use and IF statement to do this but as I add
more Budget worksheet I would like the formula to select the correct
worksheet based on the last two digits of the year of the date I enter
on my Income Statement worksheet.

Here is the formula I currently use.
=IF(YEAR(TODAY())=YEAR($H$1),SUMIF(Budget07!$A:$A,"="&($A4),Budget07!$D:$D),SUMIF(Budget06!$A:$A,"="&($A4),Budget06!$D:$D))

I think I will need some VBA function since I have found a lot of
references to getting worksheets name. I am not sure of which way/one I
should use and how to use it in a formula. Any help would be appreciated.

Thanks
Harry

 

[T. Valko]

Try this.

H1 = some date

=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR(H1),2)&"'!A:A"),$A4,INDIRECT("'Budget"&RIGHT(YEAR(H1),2)&"'!D"))

Biff

 

[Roger Govier]

Hi Harry

Why not take all of the logic about which sheets and columns to use
outside of your main formula.
Just enter in H1, the Budget sheet you want to use, rather than entering
a date e.g. Budget07
Then in I1 enter =H1&"!A:A" and in J1 =H!&"&!D"

Your formula would then be
=SUMIF(INDIRECT(I1),$A4,INDIRECT(J1))