How to drag a formula while skipping every other cell reference

Hi,

I have a formula below that I want to drag and also skip every other cell reference.

A2:=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(90:$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(90:$1083),FALSE),0))/1000

So this formula basically looks up a value in row 90 from another her tab and tells me the value.

Now I want to drag this formula so that A3 and A4 and onwards look like this:
A3:=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(92:$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(92:$1083),FALSE),0))/1000

A4:=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(94:$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(94:$1083),FALSE),0))/1000

A5:=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(96:$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(96:$1083),FALSE),0))/1000

Please help! I also can't use a helper column because I am not allowed to change the format of the sheet.

Thanks,

Try...

Formula:

=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,Sheet2!$D$2:$O$1660,ROW(INDIRECT((86+2*ROW())&":$1083")),FALSE)),HLOOKUP(Sheet1!$C$10,Sheet2!$D$2:$O$1660,ROW(INDIRECT((86+2*ROW())&":$1083")),FALSE),0))/1000

No that didnt work

It looks as if you just want the 90 in the ROW function to increase by 2 for each successive row. You can do that like this in A2:

=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(88+2*ROWS($1:1):$1083),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(88+2*ROWS($1:1):$1083),FALSE),0))/1000

then copy down.

Hope this helps.

Pete

It didn't work. it says the formula you typed containts an error

Okay, try it like this in A2:

=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(INDIRECT(88+2*ROWS($1:1)&":$1083")),FALSE)),HLOOKUP(Sheet1!$C$10,'Sheet2'!$D$2:$O$1660,ROW(INDIRECT(88+2*ROWS($1:1)&":$1083")),FALSE),0))/1000

Then copy down.

Hope this helps - if not, then post an example workbook so we can see what you are trying to do.

Pete

Hmmm... now that I'm looking at the formula rather than how to adapt for skipping rows, you're using an array of numbers for the HLOOKUP row index argument. In a non-array formula, you can only use one number (positive integer) for the row index argument. 90 to 1083 is approaching 1000 numbers.

Try...

=(IF(ISNUMBER(HLOOKUP(Sheet1!$C$10,Sheet2!$D$2:$O$1660,88+2*ROW(1:1),FALSE)),HLOOKUP(Sheet1!$C$10,Sheet2!$D$2:$O$1660,88+2*ROW(1:1),FALSE),0))/1000