Matching formula - Can we have a variable field for the row number

[Bud]

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"))

 

[Sheeloo]

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]

I just can't get this to work...I tried so many combinations with using the
INDIRECT

Can you write the formula that will work using the INDIRECT...The following
formula is what is working that I want to substitute out the 8000
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))))"

 

[Sheeloo]

Can you send me the file as it will be easier to test too...

 

[Dave Peterson]

I think your formula is wrong.

I think you'd want something like:
=IF($B2="","",INDEX(Personnel!$B$1:$B$99,MATCH($B2,Personnel!$A$1:$A$99,0)))

(single cell to start the =if(), a single cell to match (B2, not B2:B8000) and
$b$2 is changed to $b2 in both spots.)

If you can pick out a column on the personnel worksheet that always has data in
it so you can use it to find the last row, then you can use something like:

Dim LastRow As Long
With Worksheets("SomeNameHere") 'or With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("K2").Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B$1:$B$" & LastRow & ",MATCH($b2," _
& "Personnel!$A$1:$A$" & LastRow & ",0)))"
End With

Personally, I'd just use the entire column:

With Worksheets("SomeNameHere") 'or With Activesheet
.Range("K2").Formula _
= "=IF($b$2="""",""""," _
& "INDEX(Personnel!$B:$B,MATCH($b$2," _
& "Personnel!$A:$A,0)))"
End With

And instead of using .autofill, I like to fill the entire range at once.

Dim LastRow As Long
With Worksheets("SomeNameHere") 'or With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("K2").Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B$1:$B$" & LastRow & ",MATCH($b2," _
& "Personnel!$A$1:$A$" & LastRow & ",0)))"
End With

Or using the entire column:

Dim LastRow As Long
With Worksheets("SomeNameHere") 'or With Activesheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("K2:K" & LastRow).Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B:$B,MATCH($b2," _
& "Personnel!$A:$A,0)))"
End With

 

[Dave Peterson]

Ignore that last post.

I was using the lastrow variable for the lastrow in both worksheets. That won't
work for you.

I think your formula is wrong.

I think you'd want something like:
=IF($B2="","",INDEX(Personnel!$B$1:$B$99,MATCH($B2,Personnel!$A$1:$A$99,0)))

(single cell to start the =if(), a single cell to match (B2, not B2:B8000) and
$b$2 is changed to $b2 in both spots.)

If you can pick out a column on the personnel worksheet that always has data in
it so you can use it to find the last row (and instead of using .autofill, I
like to fill the entire range at once), then you can do something like:

Dim LastRowP As Long 'Personnel sheet's last row
Dim LastRowS as Long 'SomeNameHere sheet's last row

With worksheets("Personnel")
lastrowP = .cells(.rows.count,"A").end(xlup).row
end with
With Worksheets("SomeNameHere") 'or With Activesheet
lastrowS = .cells(.rows.count,"A").end(xlup).row
.Range("K2:K" & lastrowS).Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B$1:$B$" & LastRowP & ",MATCH($b2," _
& "Personnel!$A$1:$A$" & LastRowP & ",0)))"
End With

Or using the entire column:

Dim LastRowS as long 'no need for the personnel last row in this!
With Worksheets("SomeNameHere") 'or With Activesheet
LastRowS = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("K2:K" & LastRowS).Formula _
= "=IF($b2="""",""""," _
& "INDEX(Personnel!$B:$B,MATCH($b2," _
& "Personnel!$A:$A,0)))"
End With

(I deleted the single cell formula. When I was changing my post, I realized
that that was just adding confusion.)