View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier Roger Govier is offline
external usenet poster
 
Posts: 2,886
Default 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