Help with a formula required please
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
|