SOLVED

Is there a formula to auto fill down text?

Copper Contributor

I have Client names in Column A. In column B, I have various projects that take up multiple cells below. The client name is only on the line of the first project but then is blank until it gets to the next client. 

Example:

KimLabenz365_0-1612294894287.png

 

Is there a way to auto fill down Client A until it gets to Client B, Client B until Client C, etc. without having to do this one client at a time?  I have 500 + clients I need to do this for.

 

Thanks.

 

15 Replies

@KimLabenz365 

That could be as

image.png

@KimLabenz365 

Select column A.

Press Ctrl+G to activate the Go To dialog.

Click Special...

Select Blanks, then click OK.

Enter the formula =A1 and confirm it with Ctrl+Enter to populate all selected cells.

@Hans Vogelaar 

 

Each of the steps seem to lead me in the right direction until I get to the last step of enter '=A1 and press enter.'  All of the blank cells are highlighted but the data is not filling.

 

I do have column headings in Row 5 so my client data starts in A6.  Rows 1 - 4 include a title/description of the report I am trying to create.

@KimLabenz365 

You should press Ctrl+Enter, as I wrote in my previous reply.

@Hans Vogelaar 

I get this error message

KimLabenz365_0-1612299844908.png

 

I went ahead an pressed ok, but all the blanks were filled in with a '0'.

@KimLabenz365 

Did you enter =A2 by accident? =A1 should work:

 

S0088.png

Left: before entering formula, right:after entering formula and pressing Ctrl+Enter. 

@Hans Vogelaar 

 

I did have =A1 in the cell.

 

My first client entered is in A6.  Would that impact the result I am getting?

 

I keep getting the error message and when I say ok, the blank cells are filled in with '0's still.

 

Thanks for your help!  I'm determined to get it figured out!

 

Hi @KimLabenz365 

 

If you'd like an automated solution my toolbar has a macro to do this automatically

 

Fill Down.gif

 

It's free to download here https://aasolutions.sharepoint.com/:f:/g/AA_CPort/EtpJN66fRtVEg88ERyLSE9EBadMrjN79rT2bWPDoXbLUHA?e=y...

 

 

 

Wyn

MVP 

UTC+8

My Linkedin Articles...https://www.linkedin.com/today/author/wynhopkins

 

@KimLabenz365 

If cell A1 is blank, the formula cannot fill in a name. You should select a range from the first non-blank cell down.

After the blanks have been selected, look at the address box on the left hand side of the formula bar.

You'll see the address of the active cell.

The formula should refer to the cell above that. So if the active cell is A7, the formula should be

 

=A6

@KimLabenz365 You can also apply an IF function that can fill in the gaps as a helper column. Once you have the final result, just replace the original column as a text of the solution -- see example attached.

adversi_0-1612354171718.png

 

best response confirmed by KimLabenz365 (Copper Contributor)
Solution

@Hans Vogelaar 

So I selected the range and did as you suggested.  All of the correct blank cells were highlighted.  I used the formula =A6.

 

Now, instead of '0' appearing in all the blanks, 'A6' is what appears.  

 

I tried the IF function in the helper column, as suggested by adversi.  The same thing happened and 'A6' is what appeared as a result.

 

Are there any settings I need to adjust for the cells?

 

Thanks.

@KimLabenz365 

Make sure that the number format is not set to Text and that 'Show Formulas' on the Formulas tab of the ribbon is not highlighted.

@Hans Vogelaar 

 

IT WORKED!!!  Thanks so much for sticking with me to help me get this figured out!

 

The issue was the number format was changing to text so I set it to general prior to going through the steps.

Wonderful
1 best response

Accepted Solutions
best response confirmed by KimLabenz365 (Copper Contributor)
Solution

@Hans Vogelaar 

So I selected the range and did as you suggested.  All of the correct blank cells were highlighted.  I used the formula =A6.

 

Now, instead of '0' appearing in all the blanks, 'A6' is what appears.  

 

I tried the IF function in the helper column, as suggested by adversi.  The same thing happened and 'A6' is what appeared as a result.

 

Are there any settings I need to adjust for the cells?

 

Thanks.

View solution in original post