Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Column Number via Lookup
Hi All,
I have two sheets. 1) columns with days & a Month ending total column Similar to: 4/1/07 4/2/07 4/3/07...4/29/07 4/30/07 APRIL 07 TOTALS 2) Lookup totals by MONTH Simliar to: First, I pick the MONTH I want to look at APRIL MAY JUNE, etc Then, the columns look-up by MONTH name, what the totals are based on a list that gives the 'column numbers' in the array that it should look at. (i.e., column number 28 for April, 57 for May, etc) This is fine, for one month, however I want to display the CURRENT month (month selected above) and the PREVIOUS month (Month selected - 1 cell in the 'list') I was trying to find the formula to say "Look at the month from the drop-down & then go to the list & select the cell above the month I selected", in lay-man's terms. Also, I have to manually update the column numbers when I get more months (I haven't pre-set the column dates for every month in 2007), how do I find the column number in Sheet 1, by looking up the 'label' of the "APRIL 07 TOTALS" in sheet 1 & updating it on the list in Sheet 2? Let me know if this is unclear. Thanks for your help!!! Ryan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Column Number via Lookup
Let me know if this is unclear.
Does the number of replies you received mean anything? <bg I guess this is related to your other post from a few days ago? I know what you want to do but I can't tell where all this stuff is from your description. If I could actually see it I could figure it out (that's what I do!). Biff "Ryan" wrote in message oups.com... Hi All, I have two sheets. 1) columns with days & a Month ending total column Similar to: 4/1/07 4/2/07 4/3/07...4/29/07 4/30/07 APRIL 07 TOTALS 2) Lookup totals by MONTH Simliar to: First, I pick the MONTH I want to look at APRIL MAY JUNE, etc Then, the columns look-up by MONTH name, what the totals are based on a list that gives the 'column numbers' in the array that it should look at. (i.e., column number 28 for April, 57 for May, etc) This is fine, for one month, however I want to display the CURRENT month (month selected above) and the PREVIOUS month (Month selected - 1 cell in the 'list') I was trying to find the formula to say "Look at the month from the drop-down & then go to the list & select the cell above the month I selected", in lay-man's terms. Also, I have to manually update the column numbers when I get more months (I haven't pre-set the column dates for every month in 2007), how do I find the column number in Sheet 1, by looking up the 'label' of the "APRIL 07 TOTALS" in sheet 1 & updating it on the list in Sheet 2? Let me know if this is unclear. Thanks for your help!!! Ryan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Column Number via Lookup
Hi Ryan
Hopefully you haven't gone too far with this project to date. If you are trying to input data for each day of the month in a separate column, then have a column for the total for the month followed by columns for each day of the succeeding month, followed by a total column, then you will not be able to accommodate a full year of data - unless you are using XL2007. Earlier versions of XL have a limit of 256 columns. 365 (or 366) days plus 12 monthly totals = 377 Also, I cannot see how your April total could be column 28. If you start with 01 Apr 07 in column A, the 30 Apr 07 would be in column AD which is column 30, and therefore April Total would have to be in column 31 - not 28. I think you will need to undertake a fundamental re-design of your layout. If you post back with details of what you are trying to achieve, what data you have etc. then we may be able to offer you further advice. -- Regards Roger Govier "Ryan" wrote in message oups.com... Hi All, I have two sheets. 1) columns with days & a Month ending total column Similar to: 4/1/07 4/2/07 4/3/07...4/29/07 4/30/07 APRIL 07 TOTALS 2) Lookup totals by MONTH Simliar to: First, I pick the MONTH I want to look at APRIL MAY JUNE, etc Then, the columns look-up by MONTH name, what the totals are based on a list that gives the 'column numbers' in the array that it should look at. (i.e., column number 28 for April, 57 for May, etc) This is fine, for one month, however I want to display the CURRENT month (month selected above) and the PREVIOUS month (Month selected - 1 cell in the 'list') I was trying to find the formula to say "Look at the month from the drop-down & then go to the list & select the cell above the month I selected", in lay-man's terms. Also, I have to manually update the column numbers when I get more months (I haven't pre-set the column dates for every month in 2007), how do I find the column number in Sheet 1, by looking up the 'label' of the "APRIL 07 TOTALS" in sheet 1 & updating it on the list in Sheet 2? Let me know if this is unclear. Thanks for your help!!! Ryan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Column Number via Lookup
Hi All,
I appreciate your replies. I realize that the number of cells vs days in the year + weekly & monthly totals are more than the allotted amount in excel. We only do business certain months of the year, so it's less than A:HL (thank goodness!). Also, it's column number 28 due to the range I selected; I have a some labels in the first few columns of the spreadsheet which throws off my lookup if I select them. I figured out how to search for the text of the Month label (April 07, May 07, etc). My only outstanding question is: I choose a Month from my drop-down list (Validation) & I have a VLOOKUP looking up the month from the validation list & pulling the value from the selected column in a VLOOKUP (formula below). However, I want to show "previous month", "current month" and "ytd" -- Current Month & YTD are working fine, but I am having trouble directing the VLOOKUP to find "May 07" and then pull the value in column 2, -1 row. This is my current formula: =VLOOKUP(CONCATENATE($A17,C$16),'2007'!$B:$HL,(VLO OKUP($B$4,$BJ:$BK, 2,FALSE)),FALSE) The part that I'm confused about, is the 2nd VLOOKUP in the formula. The '2' returns the column numbers for the results I want, and this works wonderfully for current & ytd numbers. How do I direct the '2' to actually pull from column 2 & -1 row? Thanks again for all of your help -- I'm a beginner & am trying to teach myself! :) Thanks, Ryan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find Column Number via Lookup
Hi Ryan
Depending upon whether you are wanting Current or Previous, could you not use a formula in B4 which returned either May or April. Or, if B4 is your dropdown selecting the month, use cell C4 to make that April or May depending upon whether you have selected Previous? Then use C4 in your formula rather than B4. Better still, take the second Vlookup out of your formula altogether. In C4 =IF(xxx="Previous",OFFSET(VLOOKUP($B$4,$BJ:$BK,2,F ALSE),-1,0), VLOOKUP($B$4,$BJ:$BK,2,FALSE)) where xxx represents a cell determining Current or Previous Then your main formula becomes =VLOOKUP(CONCATENATE($A17,C$16),'2007'!$B:$HL,$C$4 ,FALSE) -- Regards Roger Govier "Ryan" wrote in message ups.com... Hi All, I appreciate your replies. I realize that the number of cells vs days in the year + weekly & monthly totals are more than the allotted amount in excel. We only do business certain months of the year, so it's less than A:HL (thank goodness!). Also, it's column number 28 due to the range I selected; I have a some labels in the first few columns of the spreadsheet which throws off my lookup if I select them. I figured out how to search for the text of the Month label (April 07, May 07, etc). My only outstanding question is: I choose a Month from my drop-down list (Validation) & I have a VLOOKUP looking up the month from the validation list & pulling the value from the selected column in a VLOOKUP (formula below). However, I want to show "previous month", "current month" and "ytd" -- Current Month & YTD are working fine, but I am having trouble directing the VLOOKUP to find "May 07" and then pull the value in column 2, -1 row. This is my current formula: =VLOOKUP(CONCATENATE($A17,C$16),'2007'!$B:$HL,(VLO OKUP($B$4,$BJ:$BK, 2,FALSE)),FALSE) The part that I'm confused about, is the 2nd VLOOKUP in the formula. The '2' returns the column numbers for the results I want, and this works wonderfully for current & ytd numbers. How do I direct the '2' to actually pull from column 2 & -1 row? Thanks again for all of your help -- I'm a beginner & am trying to teach myself! :) Thanks, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP OR INDEX/MATCH TO FIND NUMBER? | Excel Discussion (Misc queries) | |||
Lookup funtion: column index number (third parameter) | Excel Worksheet Functions | |||
Find max Row() number for a value in column | Excel Worksheet Functions | |||
Lookup Value and find Corresponding Value on another row same column | Excel Discussion (Misc queries) | |||
How do I find the first value in a column less than a number? | Excel Worksheet Functions |