If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup Question
I'm creating a workbook with multiple sheets, that is to be semi-interactive.
I have one sheet (CurrentYear) where I enter all of my information (P & L information for a restaurant). Across the first row the numbers 1-13, representing the 13 periods. Down the left hand side are all of the account numbers. Next to those numbers are the descriptions of the accounts. I then want all of the values to be entered into corresponding cells for each period. That is the easy part. It's just entering values. On another sheet (EndOfPeriod), I want to be able to compare the values. At the top of the sheet I have the user enter in the year and period number. I then want all the information from the previous period (CurrentYear) to fill in automatically. I know I need some type of lookup function, using both the period minus 1 and the account number, but I'm not sure how to do it. Does anyone have a suggestion? Does this make sense? I'm also going to have another sheet (PrevYear) with the previous years information on it. I would like that info to also fill in on the first sheet (EndOfPeriod). Any and all help would be appreciated. Thanks PS. I would try to give examples, but I've noticed that trying to simulate a worksheet never looks right. |
#2
|
|||
|
|||
Lookup Question
I figured out this current problem, but have run into another one I realized
that this has to be used for a long time to come, and tried to figure out what would happen when next year comes. To solve this, I changed the name of the CurrentYear worksheet to 2009 and the name of the PreviousYear to 2008. But what happens when we get to 2010? Here is what I have so far: (For the current period values) =VLOOKUP(A7,CurrentYear,MATCH($Q$4,CurrentYearPeri ods,0)) Where CurrentYear is the range of all the info on the CurrentYear (2009) worksheet and CurrentYearPeriods is the first row of the sheet with the numbers of the periods. Q4 is the cell that references the period. (For the previous period values) =VLOOKUP(A7,CurrentYear,MATCH($Q$4-1,CurrentYearPeriods,0)) Now my question is this: How can I set this up so that instead of using the name CurrentYear, it references whatever the value in cell Q3 (the current year) and uses that to look for the sheet with the corresponding value. That way when we get to 2010, it will look for a sheet with '2010'. Also, for previous year info, it will look for a sheet with '2009'-1. Any help would be appreciated. Thanks "Harlan" wrote: I'm creating a workbook with multiple sheets, that is to be semi-interactive. I have one sheet (CurrentYear) where I enter all of my information (P & L information for a restaurant). Across the first row the numbers 1-13, representing the 13 periods. Down the left hand side are all of the account numbers. Next to those numbers are the descriptions of the accounts. I then want all of the values to be entered into corresponding cells for each period. That is the easy part. It's just entering values. On another sheet (EndOfPeriod), I want to be able to compare the values. At the top of the sheet I have the user enter in the year and period number. I then want all the information from the previous period (CurrentYear) to fill in automatically. I know I need some type of lookup function, using both the period minus 1 and the account number, but I'm not sure how to do it. Does anyone have a suggestion? Does this make sense? I'm also going to have another sheet (PrevYear) with the previous years information on it. I would like that info to also fill in on the first sheet (EndOfPeriod). Any and all help would be appreciated. Thanks PS. I would try to give examples, but I've noticed that trying to simulate a worksheet never looks right. |
Thread Tools | |
Display Modes | |
|
|