Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Adjusting | Excel Discussion (Misc queries) | |||
formula for adjusting price | Excel Worksheet Functions | |||
Adjusting Formula | Excel Worksheet Functions | |||
Adjusting a formula cell range | Excel Discussion (Misc queries) | |||
Adjusting Vlookup Values | Excel Worksheet Functions |