Can't add formula linking cell to matching entry in other sheet with variable name

[Ochimus]

Apologies if this reads as complicated as I fear!

Can't work out the correct formula to find the matching cell on a worksheet with a variable name matching cell on another sheet with a variable name.

Please Login or Register  to view this content.

Sure it'll be a simple error, but spent all day trying numerous permutations and failing.

All solutions, suggestions and alternatives welcome as ever

Ochimus

[Pete_UK]

As you are writing a formula which include a sheet reference and the sheet name includes spaces, then you need to enclose the full sheet name within apostrophes, so this line would become:

Please Login or Register  to view this content.

Hope this helps.

Pete

EDIT: hope you can see those two red apostrophes.

Pete

[Ochimus]

Sorry, the apostrophes haven't solved it. Line throws a RunTime Error 13 Type Mismatch:

Please Login or Register  to view this content.

Galling to be so near, yet so far. . .

Ochimus

[Ochimus]

SOLVED. And a salutory lesson to even the experienced amongst us.

ws3.Cells(z, 10).Formula = "='payroll totals " & ws.Cells(2, 1) & "'!" & .Cells(Found.Rows, 10).Address(0, 0)

Fails.

ws3.Cells(z, 10).Formula = "='payroll totals " & ws.Cells(2, 1) & "'!" & .Cells(Found.Row, 10).Address(0, 0)

Drop the plural and works perfectly. Would have saved me days if Excel Help had pointed that out instead of the generic Error message?

Ochimus