Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select value in table based on values entered in multiple cells | Excel Worksheet Functions | |||
Designating color in one cell based on data entered in another cel | Excel Worksheet Functions | |||
Select correct worksheet based on data entered into a cell | Excel Worksheet Functions | |||
Adding values to a cell based on entered data | Excel Programming | |||
locking and unlocking a row of data based on whats entered in a cell | Excel Programming |