Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My spreadsheet has rows with cells that contain either numerical values or formulas that use these values (they access only values from the same row). Each row corresponds to an event, when an event of this type happens there should be a new row. The formulas never change.

Now I insert a new row, copy the last one and paste it over the new one. It clones the formatting (which is good), the formulas with updated references (which is good) and the data (which is very bad). Then I type the new data over. It seems very error-prone.

What is the right way to do it? It has to be an idiom.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Caveats:

  • I’m not 100% sure that I understand your question.
  • I don’t have access to LibreOffice Calc; only Excel.

… but here’s my 2¢ worth:

  • Create a pristine copy of your event data row.  Fill in the formulas where they belong.  Leave the cells that are destined to hold variable values blank, or enter 0, or enter –1, or enter =1/0, so they will show up as errors –– whatever you prefer, to make it obvious that they need to have valid data entered into them.  This can be Row 1 –– maybe Row 1 of a separate (otherwise unused) sheet.  Then, whenever you need a new row, go to the pristine row, copy it, then go back to where you were working, and paste it.  This has the obvious disadvantage of requiring two or three more mouse actions each time you add a row.  (Optionally, you can protect this row, so you cannot modify it.)  Or,
  • Write a macro to move the cursor down one row and fill in the new row with the functions and null/invalid values, as described above.  Or,
  • Protect (lock) the columns of cells that contain the formulas.  Then write a macro to copy the current row, move the cursor down one row, unlock the sheet, paste the row, and reprotect the sheet.  You should then be able to Tab between the variable value cells.
share|improve this answer
    
Options 1 and 3 are exactly what I want to avoid. A macro would work, but I hoped there should be a simpler method. –  XXYXXY Jan 3 '13 at 7:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.