Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP copied across rows with "row index number" changing
I have a workbook that I use to keep track of individulas time worked on
specific jobs. One sheet per person. On anygiven sheet, I have a list of jobs in Column A and hours worked on these jobs entered in following columns with the "weeks end" date as the column heading. At the end of each month I need to invoice and wanted to see the totals for a selected month. Because of the nesting limits - I could not use the IF function. So here is how I went about it: I created a range off to the far right of my sheet (which will be hidden). Headings are the months - January thru December. Under those months - I sum the columns for the given month relative to the job in column A. I want to see month totals in Column B. B1 contains the drop down list where I choose the month I want to see. Starting in B5, I entered =HLOOKUP($B$1,$BV$2:$CG$65536,4) where 4 is the same row as B5 where I entered the formula. Now I want to copy the formula down the rows with that "4" (row index number) increaseing as I go. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP copied across rows with "row index number" changing
One way:
=HLOOKUP($B$1,$BV$2:$CG$65536,ROW()-1) or =HLOOKUP($B$1,$BV$2:$CG$65536,ROW(B5)-1) HTH, Paul -- "KUMPFfrog" wrote in message ... I have a workbook that I use to keep track of individulas time worked on specific jobs. One sheet per person. On anygiven sheet, I have a list of jobs in Column A and hours worked on these jobs entered in following columns with the "weeks end" date as the column heading. At the end of each month I need to invoice and wanted to see the totals for a selected month. Because of the nesting limits - I could not use the IF function. So here is how I went about it: I created a range off to the far right of my sheet (which will be hidden). Headings are the months - January thru December. Under those months - I sum the columns for the given month relative to the job in column A. I want to see month totals in Column B. B1 contains the drop down list where I choose the month I want to see. Starting in B5, I entered =HLOOKUP($B$1,$BV$2:$CG$65536,4) where 4 is the same row as B5 where I entered the formula. Now I want to copy the formula down the rows with that "4" (row index number) increaseing as I go. Thanks, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP copied across rows with "row index number" changing
Use Rows()
=HLOOKUP($B$1,$BV$2:$CG$65536,Rows($1:4)) Will start at 4, no matter where it's entered. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "KUMPFfrog" wrote in message ... I have a workbook that I use to keep track of individulas time worked on specific jobs. One sheet per person. On anygiven sheet, I have a list of jobs in Column A and hours worked on these jobs entered in following columns with the "weeks end" date as the column heading. At the end of each month I need to invoice and wanted to see the totals for a selected month. Because of the nesting limits - I could not use the IF function. So here is how I went about it: I created a range off to the far right of my sheet (which will be hidden). Headings are the months - January thru December. Under those months - I sum the columns for the given month relative to the job in column A. I want to see month totals in Column B. B1 contains the drop down list where I choose the month I want to see. Starting in B5, I entered =HLOOKUP($B$1,$BV$2:$CG$65536,4) where 4 is the same row as B5 where I entered the formula. Now I want to copy the formula down the rows with that "4" (row index number) increaseing as I go. Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP copied across rows with "row index number" changing
Thanks,
If you think there is a better way I could have gone about the whole thing - I love to learn new ways of doing things. "RagDyer" wrote: Use Rows() =HLOOKUP($B$1,$BV$2:$CG$65536,Rows($1:4)) Will start at 4, no matter where it's entered. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "KUMPFfrog" wrote in message ... I have a workbook that I use to keep track of individulas time worked on specific jobs. One sheet per person. On anygiven sheet, I have a list of jobs in Column A and hours worked on these jobs entered in following columns with the "weeks end" date as the column heading. At the end of each month I need to invoice and wanted to see the totals for a selected month. Because of the nesting limits - I could not use the IF function. So here is how I went about it: I created a range off to the far right of my sheet (which will be hidden). Headings are the months - January thru December. Under those months - I sum the columns for the given month relative to the job in column A. I want to see month totals in Column B. B1 contains the drop down list where I choose the month I want to see. Starting in B5, I entered =HLOOKUP($B$1,$BV$2:$CG$65536,4) where 4 is the same row as B5 where I entered the formula. Now I want to copy the formula down the rows with that "4" (row index number) increaseing as I go. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
How to keep "text" number from changing to date.... | New Users to Excel | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |