HLOOKUP copied across rows with "row index number" changing

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,
 
P

PCLIVE

One way:

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

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


HTH,
Paul
 
R

RagDyer

Use Rows()

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

Will start at 4, no matter where it's entered.
 
K

KUMPFfrog

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top