Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I make drop-down list select based on 1st letter entered? | Excel Discussion (Misc queries) | |||
How to select a range whose name is entered in a cell. | Excel Worksheet Functions | |||
Linking WorkBooks Based on Data Entered In One of Them | Excel Discussion (Misc queries) | |||
Can I select a worksheet based upon a cell criteria?(for printing) | Excel Worksheet Functions | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) |