Suppose you want to have this in B1
=Sum(A1:A100) and you want to change 100 to whatever you have in C1
then use this instead (in B1)
==SUM(INDIRECT("A1:A" & C1))
It will give you the same result as the first formula above if C1 has 100


Replace the protion
=IF($b$2:$b$8000 with
=IF(INDIRECT("$b$2:$b$" & C1) where C1 should contain 8000 or what you
want... you may have to play arond a little but the concept is simple as
explained above...

You can adjust your formula using the same method...

"Bud" wrote:

Hello

I have the following matching formula that works fine and than another
statement that does a fill down using br to know when to stop the fill down.

Is there someway to modify the matching formula or to use a Lookup formula
that I can tell it what row to stop at.

I count the number of rows in a previous worksheet to know how many rows
there are but I don't know how to substitute that variable in place of that
8000 count I have.

Each week we run this report we could have differing number of rows. I just
represent it with a higher number because I don't know what else to do.

Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"

Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k"))