Referencing Cells in another worksheet in same workbook

[Guest]

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...

 

[Guest]

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.

 

[Guest]

Thanks. I believe this will work
=INDIRECT("Sheet!A5")
=INDIRECT("Sheet1!" & ADDRESS(5,1))

 

[Guest]

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"?

 

[Guest]

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)))

 

[Guest]

Thanks. Exactly what I needed.
--
Robert Hill

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)))