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.

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.