| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Cells in another worksheet in same workbook
I have 3 spreadsheets in the same workbook. Cells in sheet3 all reference
cells in sheet1. When a row is moved from sheet1 to sheet2, I need for that row to be removed from sheet3 but instead, it remains and now has a reference to sheet2. How can I get cells in sheet3 to contain only those on sheet1? My formulas in sheet3 look like this: =Sheet1!A5 Thanks in advance... -- Robert Hill |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing Cells in another worksheet in same workbook
one way might be to use the INDIRECT and ADDRESS functions. Try replacing
your formulas in Sheet3 with this: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) If you put that formula for example in Sheet3 A1, it will reference Sheet1 A1. -- Hope that helps. Vergel Adriano "Robert" wrote: I have 3 spreadsheets in the same workbook. Cells in sheet3 all reference cells in sheet1. When a row is moved from sheet1 to sheet2, I need for that row to be removed from sheet3 but instead, it remains and now has a reference to sheet2. How can I get cells in sheet3 to contain only those on sheet1? My formulas in sheet3 look like this: =Sheet1!A5 Thanks in advance... -- Robert Hill |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing Cells in another worksheet in same workbook
Thanks. I believe this will work
=INDIRECT("Sheet!A5") =INDIRECT("Sheet1!" & ADDRESS(5,1)) -- Robert Hill "Vergel Adriano" wrote: one way might be to use the INDIRECT and ADDRESS functions. Try replacing your formulas in Sheet3 with this: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) If you put that formula for example in Sheet3 A1, it will reference Sheet1 A1. -- Hope that helps. Vergel Adriano "Robert" wrote: I have 3 spreadsheets in the same workbook. Cells in sheet3 all reference cells in sheet1. When a row is moved from sheet1 to sheet2, I need for that row to be removed from sheet3 but instead, it remains and now has a reference to sheet2. How can I get cells in sheet3 to contain only those on sheet1? My formulas in sheet3 look like this: =Sheet1!A5 Thanks in advance... -- Robert Hill |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing Cells in another worksheet in same workbook
Vergel,
I have a syntax question. Why does this work: =IF(INDIRECT("PARTTIME!" & ADDRESS(6,1)) = "","",INDIRECT("PARTTIME!" & ADDRESS(6,1))) and this not work =IF(INDIRECT("PART TIME!" & ADDRESS(6,1)) = "","",INDIRECT("PART TIME!" & ADDRESS(6,1))) I assume it is because of the space between PART and TIME. How can this be coded so that I can reference the sheet named "PART TIME"? -- Robert Hill "Vergel Adriano" wrote: one way might be to use the INDIRECT and ADDRESS functions. Try replacing your formulas in Sheet3 with this: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) If you put that formula for example in Sheet3 A1, it will reference Sheet1 A1. -- Hope that helps. Vergel Adriano "Robert" wrote: I have 3 spreadsheets in the same workbook. Cells in sheet3 all reference cells in sheet1. When a row is moved from sheet1 to sheet2, I need for that row to be removed from sheet3 but instead, it remains and now has a reference to sheet2. How can I get cells in sheet3 to contain only those on sheet1? My formulas in sheet3 look like this: =Sheet1!A5 Thanks in advance... -- Robert Hill |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing Cells in another worksheet in same workbook
Robert,
Yes, it's the space between PART and TIME. What you can do is enclose the sheet name in single quotes: =IF(INDIRECT("'PART TIME'!" & ADDRESS(6,1)) = "","",INDIRECT("'PART TIME'!" & ADDRESS(6,1))) -- Hope that helps. Vergel Adriano "Robert" wrote: Vergel, I have a syntax question. Why does this work: =IF(INDIRECT("PARTTIME!" & ADDRESS(6,1)) = "","",INDIRECT("PARTTIME!" & ADDRESS(6,1))) and this not work =IF(INDIRECT("PART TIME!" & ADDRESS(6,1)) = "","",INDIRECT("PART TIME!" & ADDRESS(6,1))) I assume it is because of the space between PART and TIME. How can this be coded so that I can reference the sheet named "PART TIME"? -- Robert Hill "Vergel Adriano" wrote: one way might be to use the INDIRECT and ADDRESS functions. Try replacing your formulas in Sheet3 with this: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) If you put that formula for example in Sheet3 A1, it will reference Sheet1 A1. -- Hope that helps. Vergel Adriano "Robert" wrote: I have 3 spreadsheets in the same workbook. Cells in sheet3 all reference cells in sheet1. When a row is moved from sheet1 to sheet2, I need for that row to be removed from sheet3 but instead, it remains and now has a reference to sheet2. How can I get cells in sheet3 to contain only those on sheet1? My formulas in sheet3 look like this: =Sheet1!A5 Thanks in advance... -- Robert Hill |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing Cells in another worksheet in same workbook
Thanks. Exactly what I needed.
-- Robert Hill "Vergel Adriano" wrote: Robert, Yes, it's the space between PART and TIME. What you can do is enclose the sheet name in single quotes: =IF(INDIRECT("'PART TIME'!" & ADDRESS(6,1)) = "","",INDIRECT("'PART TIME'!" & ADDRESS(6,1))) -- Hope that helps. Vergel Adriano "Robert" wrote: Vergel, I have a syntax question. Why does this work: =IF(INDIRECT("PARTTIME!" & ADDRESS(6,1)) = "","",INDIRECT("PARTTIME!" & ADDRESS(6,1))) and this not work =IF(INDIRECT("PART TIME!" & ADDRESS(6,1)) = "","",INDIRECT("PART TIME!" & ADDRESS(6,1))) I assume it is because of the space between PART and TIME. How can this be coded so that I can reference the sheet named "PART TIME"? -- Robert Hill "Vergel Adriano" wrote: one way might be to use the INDIRECT and ADDRESS functions. Try replacing your formulas in Sheet3 with this: =INDIRECT("Sheet1!" & ADDRESS(ROW(),COLUMN())) If you put that formula for example in Sheet3 A1, it will reference Sheet1 A1. -- Hope that helps. Vergel Adriano "Robert" wrote: I have 3 spreadsheets in the same workbook. Cells in sheet3 all reference cells in sheet1. When a row is moved from sheet1 to sheet2, I need for that row to be removed from sheet3 but instead, it remains and now has a reference to sheet2. How can I get cells in sheet3 to contain only those on sheet1? My formulas in sheet3 look like this: =Sheet1!A5 Thanks in advance... -- Robert Hill |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a cell in another worksheet within the same workbook | Excel Worksheet Functions | |||
| Referencing a cell in another worksheet within the same workbook | Excel Worksheet Functions | |||
| Referencing Cells of Different Workbook | Excel Programming | |||
| Referencing cells in another workbook | Excel Programming | |||
| Referencing cells in another workbook | Excel Programming | |||
![Robert[_32_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
