Select correct worksheet based on data entered into a cell
 

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

Select correct worksheet based on data entered into a cell
 

Please try
=INDIRECT(B2&"!C5")
where cell B2 contains the name of the sheet and C5 is the cell on that
sheet whose value you want

"Harry Stevens" wrote:

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