Moving on a Hlookup by a number of cells each time??

**turbofatty** · 11-02-2009, 09:20 AM

Hi Guys,

I have been given a spreadsheet to fix as the user says the charts/graphs aren't adding the new data.

But when I looked closer at the workbook the whole thing just seemed a little wrong to me

Please see the workbook attached.

All the data is entered into the sheet 'Data Entry'.

Then on the 'Data' sheet it should pull thru some key values but half of it isn't done and it isn't very smart.

I have created a 'TEST Data' sheet so I could start messing with it.

I was going to pull the data thru from 'Data Entry' into the 'TEST Data' sheet using a Hlookup to make sure it worked before i messd with the actual 'Data' sheet.

The problem I am having is moving/copying the Hlookup across.

On the 'TEST data' sheet the Hlookup is as follows and is correct:

=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,2,FALSE)

But the next set of (Week 2) is not in the 2nd or even 3rd row of the array, its in row 11......... thats 9 rows down from the original.

Moving on the data i want to bring back is always 9 more than the last so the Hlookups should look like:

=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,11,FALSE)
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,20,FALSE)
=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,29,FALSE)

etc etc

Can any of you guys out there help on how I can do this or it may not even be a Hlookup but something even smarter!!

Thanks in advance

**darkyam** · 11-02-2009, 10:23 AM

Try =HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,(ROW()-5)*9+2,FALSE)

**NBVC** · 11-02-2009, 10:25 AM

In C5 try:

=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,9*(COLUMNS($A$1:A1)-1)+2,FALSE)

copied across.

in C6:

=HLOOKUP($A$5,'Data Entry'!$C$4:$M$472,9*(COLUMNS($A$1:A1)-1)+9,FALSE)

and so on...adjusting the red number to meet the first row that you need to get and copy across.

**darkyam** · 11-02-2009, 10:28 AM

Sorry, I went too fast on this one. I always assume people have sheets set up to drag formulas down. NBVC is, as usual, right.

**turbofatty** · 11-02-2009, 10:49 AM

NBVC and darkyam, you are a pair of genius'!!!

I don't know how you do it but i'll bank this one and remember it!!

It could come in quite handy in the future!!

I'm not sure how this COLUMNS function works at the end of the hlookup, but it does!!

I'll try and figure it out!!

Cheers once more Guys!!!

**NBVC** · 11-02-2009, 10:52 AM

Hint:

Use the Formula Auditing|Evaluate tool found in Tools menu to step through the formula and see what is happening.

**darkyam** · 11-02-2009, 11:07 AM

Or you can highlight parts of the formula (making sure you get beginning and ending parentheses for a function) and hit F9.

**NBVC** · 11-02-2009, 11:48 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save