HLOOKUP copied across rows with "row index number" changing

  • Thread starter Thread starter KUMPFfrog
  • Start date Start date
K

KUMPFfrog

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,
 
One way:

=HLOOKUP($B$1,$BV$2:$CG$65536,ROW()-1)

or
=HLOOKUP($B$1,$BV$2:$CG$65536,ROW(B5)-1)


HTH,
Paul
 
Use Rows()

=HLOOKUP($B$1,$BV$2:$CG$65536,Rows($1:4))

Will start at 4, no matter where it's entered.
 
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.
 
Back
Top