Formulas & Functions for Spread Sheets

0

 Variatus (Rep:229)

You asked me to submit another question.

How to stop grid lines in the sheet being removed when data is entered?

You used the term another thread! what do you mean.

Will you show the finished function?

I have loaded the XLSX again.

Answer
Discuss

Discussion

This is another thread. Please be nice to the onlookers. They have no knowledge of what went on before. Would you please rephrase your question to be independent from the previous thread? My understanding is that the grid lines are removed when you copy/paste formulas. If they are removed when you enter data, meaning you just type something in a cell, than I wouldn't know the answer.
Variatus (rep: 1143) Aug 22, '17 at 5:30 am
Sorry,.When the formula is load and highlighted and you drag it down to populate
the target cells in the column, the lines between the cells disappear, how would I overcome this problem.
  
Jemz (rep: 4) Aug 22, '17 at 6:21 am
Add to Discussion

Answers

0
Selected Answer

Excel has no way to paste only formulas. When you copy a formula and paste it to another cell all the original cell's formatting is copied as well.

But Excel has a way to copy only formats. This is done with the `Paint` function. So, this is what you do.

  • Copy the formula down (in the F column), thereby destroying your borders.
  • Select the cells in the E column whose format you want to paste to the F column
  • Click on the 'Format Painter' in the Ribbon's Home tab
  • Then immediately click on the first cell in the F column next to the first selected cell in the E column.
  • All the formats of the selected cells will be copied to the F column leaving the formulas untouiched.

Perhaps a better way is to make use of the fact that you have groups of 3 rows followed by a subtotal row which should have another formula. So,

  • Enter the formula in F3
  • Copy/paste to F4:F5
  • Restore the cell format in F4:F5 using the above technique
  • Select F3:F6 and copy down to the bottom of your sheet.
  • Bear in mind that you copy sets of 4 cells. Therefore when you select the destination you should also select sets of 4 cells before you hit Enter. If you select 19 or 21 rows instead of 20 the result will not be es expected.
Discuss

Discussion

I will have to work through you answer as it is some what obscure at first glance
Jemz (rep: 4) Aug 22, '17 at 6:21 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login