Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring a cell
Hi,
1) I would like to know how can I get "='M:\Payroll\Expenses Report\[NON- GATE REPORT APR 2010.working.xls]APR 10'!$G$38 on a worksheet when referring the date on G38 of the file "NON-GATE REPORT APR 2010.working.xls". I can only get something like "='[Movement.0410.xls]Reconciliation HKAHQ'!$E$60" and need to open the data file for referring to the cell data to paste on the destination worksheet. 2) On sheet 2, if I want to type "= and point to a cell on sheet 1, the link will be like "=Sheet1!A1" can I make the cell to read "=Sheet1!$A$1" ? Please advise. Many Thanks Terry -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201005/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring a cell
For number 1, I'm afriad you're probably out of luck. The closest thing
would be the INDIRECT function, but it won't work on closed workbooks. You could use the HYPERLINK function, and concatenate a string together, but that would simply open the file, not retrieve the specific data. For number 2, did you try and type the dollar signs? Alternatively, after clicking on the cell, hit the F4 key. This cycles through the different options for relative/absolute referencing. -- Best Regards, Luke M "Terry0928 via OfficeKB.com" <u59143@uwe wrote in message news:a8320afacecbf@uwe... Hi, 1) I would like to know how can I get "='M:\Payroll\Expenses Report\[NON- GATE REPORT APR 2010.working.xls]APR 10'!$G$38 on a worksheet when referring the date on G38 of the file "NON-GATE REPORT APR 2010.working.xls". I can only get something like "='[Movement.0410.xls]Reconciliation HKAHQ'!$E$60" and need to open the data file for referring to the cell data to paste on the destination worksheet. 2) On sheet 2, if I want to type "= and point to a cell on sheet 1, the link will be like "=Sheet1!A1" can I make the cell to read "=Sheet1!$A$1" ? Please advise. Many Thanks Terry -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201005/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring a cell
The function you'd want to use is =indirect().
But =indirect() won't work if the sending file is closed. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. === If you have trouble getting to the site, then search google for indirect.ext. I found this alternative site: http://download.cnet.com/Morefunc/30...-10423159.html I didn't look to see if it was the most current version. I'd check the original site every so often to see if it's working. On 05/18/2010 11:23, Terry0928 via OfficeKB.com wrote: Hi, 1) I would like to know how can I get "='M:\Payroll\Expenses Report\[NON- GATE REPORT APR 2010.working.xls]APR 10'!$G$38 on a worksheet when referring the date on G38 of the file "NON-GATE REPORT APR 2010.working.xls". I can only get something like "='[Movement.0410.xls]Reconciliation HKAHQ'!$E$60" and need to open the data file for referring to the cell data to paste on the destination worksheet. 2) On sheet 2, if I want to type "= and point to a cell on sheet 1, the link will be like "=Sheet1!A1" can I make the cell to read "=Sheet1!$A$1" ? Please advise. Many Thanks Terry |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring a cell
Thanks Luke,
For number 1, does it mean I need to open the workbook and use indirect function to link the cell from workbook 1 to workbook 2, which will indicate like "='M:\Payroll\Expenses Report\[NON- GATE REPORT APR 2010.working.xls]APR 10'!$G$38"? For number 2, I did hit the F4 key on every cell, but if there any option that I don't need to hit the F4 cell by cell in case of over 100 cells to work on? Regards, Terry Luke M wrote: For number 1, I'm afriad you're probably out of luck. The closest thing would be the INDIRECT function, but it won't work on closed workbooks. You could use the HYPERLINK function, and concatenate a string together, but that would simply open the file, not retrieve the specific data. For number 2, did you try and type the dollar signs? Alternatively, after clicking on the cell, hit the F4 key. This cycles through the different options for relative/absolute referencing. Hi, [quoted text clipped - 16 lines] Many Thanks Terry -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/201005/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question regarding referring to different cell Value | Excel Discussion (Misc queries) | |||
Referring to first cell in a range | Excel Discussion (Misc queries) | |||
Sum values referring to a cell with same value | Excel Discussion (Misc queries) | |||
How to return a value Referring to the other cell. | Excel Worksheet Functions | |||
cell text referring to tab name | Excel Worksheet Functions |