ExcelBanter - Cell Reference

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Cell Reference (https://www.excelbanter.com/excel-discussion-misc-queries/165093-cell-reference.html)

Hi, I have a worksheet with some rows that are linked to a different
worksheet within the same workbook and some rows that are linked to a
different workbook.

The worksheets all have unique names

Is there a way that I can highlight the cells that are linked to a different
workbook please? Alternatively I could have a column that says whether that
row is linked to an external workbook.

Thank you in advance

Doing a Find on the other workbook's name will 'highlight' (select) each
such link one at a time.

--
Jim
"Michael" wrote in message
...
| Hi, I have a worksheet with some rows that are linked to a different
| worksheet within the same workbook and some rows that are linked to a
| different workbook.
|
| The worksheets all have unique names
|
| Is there a way that I can highlight the cells that are linked to a
different
| workbook please? Alternatively I could have a column that says whether
that
| row is linked to an external workbook.
|
| Thank you in advance
|
|
|

Thanks, but that doesn't work,

If I can explain a little clearer.

The following example would be workable for me.

In cell a1 I have a formula =summary!a1
in b1 is have =summary!b1

You will see that these cells are in the same workbook but a different
worksheet

in a2 the formula is ='[Uploaded.xls]Sheet1'!$a$1 - (here the formula is
looking at a different workbook)

and so on

In cell c1 I would like the formula to be based on the formula in a1. If a1
is pointing to the summary sheet then I would like the c1 to say "not
uploaded"

However, if the formula is pointing to the uploaded spreadsheet then I would
like c1 to say "uploaded"

So if the formula in c1 is copied down to c2 then c2 would say "uploaded"
because A2 is looking at the uploaded workbook.

I tried the find function by putting the following formula in cell c1

=find("uploaded",a1) and the cell showed #VALUE!

Hope this clears up what I am after.

"Jim Rech" wrote:

Doing a Find on the other workbook's name will 'highlight' (select) each
such link one at a time.

--
Jim
"Michael" wrote in message
...
| Hi, I have a worksheet with some rows that are linked to a different
| worksheet within the same workbook and some rows that are linked to a
| different workbook.
|
| The worksheets all have unique names
|
| Is there a way that I can highlight the cells that are linked to a
different
| workbook please? Alternatively I could have a column that says whether
that
| row is linked to an external workbook.
|
| Thank you in advance
|
|
|

You can use VBA

http://www.mvps.org/dmcritchie/excel...htm#getformula

then use your find or search formula like

=IF(ISNUMBER(SEARCH("uploaded",GetFormula(A1))),"U ploaded","Not Uploaded")

finally you could use Excel 4 macro, insertnamedefine, in the name box
type

GET.CELL

in the refers to put

=GET.CELL(6,Sheet1!$A$1)

replace Sheet1 with the sheet name of the sheet where you want this formula

click OK

then use

=IF(ISNUMBER(SEARCH("uploaded",GET.CELL)),"Uploade d","Not Uploaded")

now for the latter if you have any excel version previous to 2003 or maybe
2002 and you copy this formula to another sheet it will crash Excel brutally
and all work that wasn't previously saved will be lost

--

Regards,

Peo Sjoblom

"Michael" wrote in message
...
Thanks, but that doesn't work,

If I can explain a little clearer.

The following example would be workable for me.

In cell a1 I have a formula =summary!a1
in b1 is have =summary!b1

You will see that these cells are in the same workbook but a different
worksheet

in a2 the formula is ='[Uploaded.xls]Sheet1'!$a$1 - (here the formula is
looking at a different workbook)

and so on

In cell c1 I would like the formula to be based on the formula in a1. If
a1
is pointing to the summary sheet then I would like the c1 to say "not
uploaded"

However, if the formula is pointing to the uploaded spreadsheet then I
would
like c1 to say "uploaded"

So if the formula in c1 is copied down to c2 then c2 would say "uploaded"
because A2 is looking at the uploaded workbook.

I tried the find function by putting the following formula in cell c1

=find("uploaded",a1) and the cell showed #VALUE!

Hope this clears up what I am after.

"Jim Rech" wrote:

Doing a Find on the other workbook's name will 'highlight' (select) each
such link one at a time.

--
Jim
"Michael" wrote in message
...
| Hi, I have a worksheet with some rows that are linked to a different
| worksheet within the same workbook and some rows that are linked to a
| different workbook.
|
| The worksheets all have unique names
|
| Is there a way that I can highlight the cells that are linked to a
different
| workbook please? Alternatively I could have a column that says whether
that
| row is linked to an external workbook.
|
| Thank you in advance
|
|
|

Thanks, works a treat

"Peo Sjoblom" wrote:

You can use VBA

http://www.mvps.org/dmcritchie/excel...htm#getformula

then use your find or search formula like

=IF(ISNUMBER(SEARCH("uploaded",GetFormula(A1))),"U ploaded","Not Uploaded")

finally you could use Excel 4 macro, insertnamedefine, in the name box
type

GET.CELL

in the refers to put

=GET.CELL(6,Sheet1!$A$1)

replace Sheet1 with the sheet name of the sheet where you want this formula

click OK

then use

=IF(ISNUMBER(SEARCH("uploaded",GET.CELL)),"Uploade d","Not Uploaded")

now for the latter if you have any excel version previous to 2003 or maybe
2002 and you copy this formula to another sheet it will crash Excel brutally
and all work that wasn't previously saved will be lost

--

Regards,

Peo Sjoblom

"Michael" wrote in message
...
Thanks, but that doesn't work,

If I can explain a little clearer.

The following example would be workable for me.

In cell a1 I have a formula =summary!a1
in b1 is have =summary!b1

You will see that these cells are in the same workbook but a different
worksheet

in a2 the formula is ='[Uploaded.xls]Sheet1'!$a$1 - (here the formula is
looking at a different workbook)

and so on

In cell c1 I would like the formula to be based on the formula in a1. If
a1
is pointing to the summary sheet then I would like the c1 to say "not
uploaded"

However, if the formula is pointing to the uploaded spreadsheet then I
would
like c1 to say "uploaded"

So if the formula in c1 is copied down to c2 then c2 would say "uploaded"
because A2 is looking at the uploaded workbook.

I tried the find function by putting the following formula in cell c1

=find("uploaded",a1) and the cell showed #VALUE!

Hope this clears up what I am after.

"Jim Rech" wrote:

Doing a Find on the other workbook's name will 'highlight' (select) each
such link one at a time.

--
Jim
"Michael" wrote in message
...
| Hi, I have a worksheet with some rows that are linked to a different
| worksheet within the same workbook and some rows that are linked to a
| different workbook.
|
| The worksheets all have unique names
|
| Is there a way that I can highlight the cells that are linked to a
different
| workbook please? Alternatively I could have a column that says whether
that
| row is linked to an external workbook.
|
| Thank you in advance
|
|
|