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
 

Try this.

H1 = some date

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

Biff

"Harry Stevens" wrote in message
...
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
 

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:D"

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

Roger Govier


"Harry Stevens" wrote in message
...
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