The second quick Excel question?

Question

Hello again guys! - as mentioned I had two excel questions so here is the second one.

I often am in a situation where I am creating a large excel list containing a bunch of columns. Generally these excel lists contains hundreds of rows in a ordered form.

My problem comes into play when I try to automatically fill cells without having to copy/past my data manually (which is what I currently do).

Envisions this situations:
* rows 1 - 500 have data split into 3 columns
* column A = State (ie. Alabama, Alaska, Arizona,....)
* column B = URL (ie. http://www.demo.com)
* column C = Description (ie. blabla...)

For this example, lets assuming rows 1-25 are represent entries that apply to the sate "Alabama" but only the first entry (A1) has an entry in the State column for "Alabama" and rows 2-25 are blank... now - row 26-75 should have the State column filled in with "Alaska" but only the first entry "A26" has this.... What formula would automatically fill in all the blanks correctly?

Answer 1

Create an extra column (say, column D):



D1 =A1

D2 = IF (A2 = "",A1, A2)



Copy the formula in D2 down as far as it needs to go. If there is nothing in the State column, it will put in whatever was last there.



--- edit ---

Your friends solution will result in a circular reference. I'm guessing you meant to write:

B2 would have formula =IF(A2<>"",A2,B1)

B3 would have formula =IF(A3<>"",A3,B2)

Which is the same as my own suggestion, but in reverse.

Answer 2

Its very simple. Just put the formula in A2 cell "=A1" then drag the formula up to 500(or as you wish). If you want change state name from A26 Alaska then type "Alaska" at A26. Similarly change the other state names .

Answer 3

As far as automatically, I'm not sure. But simply put, if it reads as so...



Alabama xxxxx Description

Washington 98xxx Description

98xxx Description



Select Washington and all the blank cells, not press CTRL-D and it will fill down all the selections below "Washington"...

Answer 4

place your mouse interior the decrease exact corner of the cellular. Mouse pointer substitute to + double click and the formula will reproduction down till the adjoining cellular (left or righ is empty) Plase vote :)

Answer 5

just drag the cell . or use fill option from edit menu