Adjusting formula for month in vlookup?

[Tasha]

Sorry, this is going to be a little in depth....
I have a worksheet that has formulas looking up data from another worksheet
within the same workbook. The first worksheet is set up like thiscells not
included have other formulas in them)
E G L P
R
2009 2008 2008 Prior Month YTD
Prior Month YTD
MTD MTD YTD 2009
2008
docname docnum

column E current formula: =Admissions!K85
(K is August column on
Admissions Sheet)
column G current formula: =Admissions!K82
column L formula: =sum(Admissions!D82:K82)
column P formula: =sum(Admissions!D85:J85)
column R formula: =sum(Admissions!D82:J82)

each month I am adjusting the formulas over one column to change it to
lookup the current months data, so for instance, in column E, for September's
sheet I would change the formula to =Admissions!L85, column L's formula, I
would change to =SUM(Admissions!D82:L82), etc.

Is there a vlookup or sumproduct formula I can use to accomplish this? I am
spending so much time manually changing formulas for up to 200 lines each
month.

The second worksheet is set up like thiswe don't use 2007 #'s)
D E F G H I
J K etc
JAN FEB MAR APR MAY JUN JUL AUG
phyname 2007 1 5 2 4 1 3 6 2
phyno 2008 6 1 3 2 1 5 3 1

phyno 2009 4 4 2 1 2 4 1

I know this is a lot to look at, but I have tried to think of a way to do
this, but
haven't been able so far to find anything. Any help you can give me would be
terrific!!!!

 

[Tasha]

sorry, they didn't line up like I wanted them to

 

[Luke M]

Let say you have in cell A2 the value 9, representing current month (Jan =1,
Feb = 2). You can accomplish this via formula if you want, just need someway
of getting a value. Formulas then become:
E:
=OFFSET(Admissions!D85,0,A2-2)
Note the -2 is to get previous month, and allows Jan =1 to give you 0 offset.
G:
=OFFSET(Admissions!C82,0,A2-2)
L:
=SUM(OFFSET(Admissions!D82,0,0,1,A2-1))
P:
=SUM(OFFSET(Admissions!D85,0,0,1,A2))
R:
=SUM(OFFSET(Admissions!D82,0,0,1,A2))

Now you only need change the 1 cell to change which month you're looking at.
And again, you could set cell A2 up with a formula somehow if you want, like
if you want real-time updates:
=MONTH(TODAY())

 

[Tasha]

that seems to be working Thank you so much!!!! Will take some time to
change them all, but sure beats the devil out of doing it every month!!!