Copy Conundrum?

[andrewk73]

I have created a spreadsheet to track shipping containers. People ente
information on one sheet, and then on a second sheet it take
information from sheet 1 and another user fills in more information.

I have set it up so users only have to enter information in once. O
the second sheet only some of the information flows through from th
first sheet and the rest has to be entered on the second sheet.

I have used formulas that basicaly say
=if(cell in sheet 1="","",cell in sheet 1)

These formulas work perfectly till someone copies and pastes or insert
cells into the first spreadsheet and then this corrupts the formulas i
the second spreadsheet. I then have to copy the formulas down th
columns again to fix the problem. Is there a way to stop the formula
corrupting.

Cheers

And

 

[Bob Phillips]

Andrew,

Is this what you mean?

=IF(INDIRECT("Sheet1!A1"),"",INDIRECT("Sheet1!A1"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[DDM]

Andrew, if you want to make it possible for your users to enter information
while preventing them from corrupting or overwriting your formulas, protect
your worksheets.

Plan your worksheets so it will be unnecessary for users to insert or delete
columns or rows. Then on your first worksheet, select the cells in which
users will input information, and Format > Cells > Protection. Uncheck the
Locked checkbox, and OK. Then Tools > Protection > Protect Sheet.

Later versions of Excel give you the most options here but the idea is the
same in all versions -- it prevents users from making changes to the
workbook or even making entries in locked cells.

Do this for both worksheets and you shouldn't have to worry about your users
corrupting your formulas.

 

[andrewk73]

Thanks Bob Great advice. Perfect!

*Andrew,

Is this what you mean?

=IF(INDIRECT("Sheet1!A1"),"",INDIRECT("Sheet1!A1"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

message