I require help with a formula please.

I have the following:

=IF(ISERROR(MATCH(old!$C$1:$C$" & overallLastRow & ",new!$C$1:$C$" &
overallLastRow & " ,0)),old!$C$1:$C$" & overallLastRow & " ,"""")

This formula uses 2 lists:

old!$C$1:$C$" & overallLastRow & "
AND
new!$C$1:$C$" & overallLastRow & "

Obviously old and new are the names of the Worksheets on which these lists
are found. These are direct references to these sheets.

What I want to do is give a sheet a name and link to this in a similar way
that I have given a cell number a name of "overallLastRow".

Can anyone tell me how to do this?

Thanks
Dave

You can do it using INDIRECT - this allows you to build up cell or
range references as a string, eg:

INDIRECT(A1&"!$C$1:$C$" & overallLastRow)

where A1 will contain your sheet name - if you have spaces in the name
then you should use:

INDIRECT("'"&A1&"'!$C$1:$C$" & overallLastRow)

Note the apostrophes.

Hope this helps.

Pete

On Nov 2, 10:28 am, Dave wrote:
I require help with a formula please.

I have the following:

=IF(ISERROR(MATCH(old!$C$1:$C$" & overallLastRow & ",new!$C$1:$C$" &
overallLastRow & " ,0)),old!$C$1:$C$" & overallLastRow & " ,"""")

This formula uses 2 lists:

old!$C$1:$C$" & overallLastRow & "
AND
new!$C$1:$C$" & overallLastRow & "

Obviously old and new are the names of the Worksheets on which these lists
are found. These are direct references to these sheets.

What I want to do is give a sheet a name and link to this in a similar way
that I have given a cell number a name of "overallLastRow".

Can anyone tell me how to do this?

Thanks
Dave

Does this do it

=IF(ISERROR(MATCH(INDIRECT("'"&old_sheet&"'!$C$1:$ C$"&overallLastRow),
INDIRECT("'"&new_sheet&"'!$C$1:$C$"&overallLastRow ),0)),
INDIRECT("'"&old_sheet&"'!$C$1:$C$"&overallLastRow ),"""")


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave" wrote in message
...
I require help with a formula please.

I have the following:

=IF(ISERROR(MATCH(old!$C$1:$C$" & overallLastRow & ",new!$C$1:$C$" &
overallLastRow & " ,0)),old!$C$1:$C$" & overallLastRow & " ,"""")

This formula uses 2 lists:

old!$C$1:$C$" & overallLastRow & "
AND
new!$C$1:$C$" & overallLastRow & "

Obviously old and new are the names of the Worksheets on which these lists
are found. These are direct references to these sheets.

What I want to do is give a sheet a name and link to this in a similar way
that I have given a cell number a name of "overallLastRow".

Can anyone tell me how to do this?

Thanks
Dave

I have enterred my sheet names into the fomula as follows:

=IF(ISERROR(MATCH(INDIRECT("'"&oldWorkSheet&"'!$C$ 1:$C$"&overallLastRow),INDIRECT("'"&newWorkSheet&" '!$C$1:$C$"&overallLastRow),0)),INDIRECT("'"&oldWo rkSheet&"'!$C$1:$C$"&overallLastRow),"""")

When I put this into my module it comments everything out from the first
"&oldWorkSheet&"'!$C$1:$C$"&overall... onwards.

Dave

"Bob Phillips" wrote:

Does this do it

=IF(ISERROR(MATCH(INDIRECT("'"&old_sheet&"'!$C$1:$ C$"&overallLastRow),
INDIRECT("'"&new_sheet&"'!$C$1:$C$"&overallLastRow ),0)),
INDIRECT("'"&old_sheet&"'!$C$1:$C$"&overallLastRow ),"""")


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave" wrote in message
...
I require help with a formula please.

I have the following:

=IF(ISERROR(MATCH(old!$C$1:$C$" & overallLastRow & ",new!$C$1:$C$" &
overallLastRow & " ,0)),old!$C$1:$C$" & overallLastRow & " ,"""")

This formula uses 2 lists:

old!$C$1:$C$" & overallLastRow & "
AND
new!$C$1:$C$" & overallLastRow & "

Obviously old and new are the names of the Worksheets on which these lists
are found. These are direct references to these sheets.

What I want to do is give a sheet a name and link to this in a similar way
that I have given a cell number a name of "overallLastRow".

Can anyone tell me how to do this?

Thanks
Dave