Adjusting formula for month in vlookup?
 

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 this:(cells 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 this:(we 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!!!!

Adjusting formula for month in vlookup?
 

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

"Tasha" wrote:

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 this:(cells 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 this:(we 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!!!!

Adjusting formula for month in vlookup?
 

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())

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tasha" wrote:

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 this:(cells 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 this:(we 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!!!!

Adjusting formula for month in vlookup?
 

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!!!

"Luke M" wrote:

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())

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tasha" wrote:

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 this:(cells 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 this:(we 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!!!!