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,

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,

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,

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,