If a cell is empty then = the one above

[Mark Roberts]

Hi All,

I'm trying to copy a large amount of data in to a database. I have one problem though.

I am trying to copy company information. The company could have 10-20 people working there so would go from row 1-20 for example with peoples names and phone numbers.

I need to copy the Company Name, Contact Person, Title and Phone Number. Lets say Company Name is A1, Person B1, Title C1, Phone Number D1.

However the person's details I need in my database are in row 8. All I am missing is the company name. The rest of the information is there in Row 8. I know a simple answer is to say =A1, but I have thousands of rows to deal with.

However, the company name (which I need) is only in row 1. I would like to have a formula where I could say - speaking as cell A2..."If I am empty then = row above me that is populated with company name". So that I could formulate it down to.

I then have a new company name in row 21. So I guess I once get the formula I would have to filter on blank cells and use select visible cells to paste the formula. Anyone I need to get the first formula right first!

Thanks for any help
Mark

 

[steve the fish]

Highlight cells in question.
Press CNTL-G.
Press special.
Press blanks option button.
Press OK.
Type = then press up arrow key.
Finally CNTL-ENTER.
Copy paste special if required.

 

[vds1]

Lets consider column A,

1) Filter the column by Blanks cells
2) Highlight first blank cell ( Lets say A8)
3) Insert the Formula =A7 on that cell
4) Copy and paste formula across the blank rows keeping the filter ON.
5) Remove filter, select column A and copy paste as values.

 

[Mark Roberts]

That's great Steve. Thanks very much did exactly as I wanted. Thanks also to vds1.