Replicating a formula

[Jim Berglund]

I want to place the following formula into column C, starting in row 2.

=IF($CB$3="ALL","",IF(HLOOKUP($B$3,$D$3:$J$35,2)="","",HLOOKUP($B$3,$D$3:$J$35,2)))

1. How can I automatically increment the final number in the LOOKUP (in this case, "2") to the rows below?

Thanks,
Jim Berglund

 

[Guest]

Insert a column which will ultimately be hidden. Place the numbers 1,2,3,...
down the column. Now just reference the cells in the column you want. When it
is all working hide the column...

Hope this helps...

 

[Doug Glancy]

Jim,

You can use Row() - 1, which returns the row number the formula is in, less
1.
=IF($CB$3="ALL","",IF(HLOOKUP($B$3,$D$3:$J$35,row()-1)="","",HLOOKUP($B$3,$D
$3:$J$35,row()-1)))

If you are changing it in both instances, as I've done here, it seems like
the second HLOOKUP is repetitive, as it will return "" if that's the lookup
value, anyway. Also, I'm wondering if every row will return a different
lookup value, why not just hard code the value instead of using a formula?

hth,

Doug Glancy


I want to place the following formula into column C, starting in row 2.

=IF($CB$3="ALL","",IF(HLOOKUP($B$3,$D$3:$J$35,2)="","",HLOOKUP($B$3,$D$3:$J$
35,2)))

1. How can I automatically increment the final number in the LOOKUP (in this
case, "2") to the rows below?

Thanks,
Jim Berglund

 

[Gord Dibben]

Jim

I think you could use the ROW() function in place of the 2

=IF($CB$3="ALL","",IF(HLOOKUP($B$3,$D$3:$J$35,(ROW()-1))="","",HLOOKUP($B$3,$D$3:$J$35,(ROW()-1))))

Entered in A3.

Adjust the ROW()-1 for your start row.

Gord Dibben Excel MVP