| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Reference
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell Reference
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 | | | |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell Reference
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 | | | |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell Reference
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 | | | |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell Reference
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 | | | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing sheet reference to cell reference | Excel Worksheet Functions | |||
| Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
| absolute cell reference A spreadsheet cell reference that does no | Excel Discussion (Misc queries) | |||
| problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
| Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Linear Mode
