Feb 02 2021 11:44 AM
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:
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.
Feb 02 2021 12:17 PM
Feb 02 2021 12:24 PM
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.
Feb 02 2021 12:42 PM
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.
Feb 02 2021 12:55 PM
You should press Ctrl+Enter, as I wrote in my previous reply.
Feb 02 2021 01:04 PM
Feb 02 2021 01:07 PM
Feb 02 2021 01:35 PM
Did you enter =A2 by accident? =A1 should work:
Left: before entering formula, right:after entering formula and pressing Ctrl+Enter.
Feb 02 2021 03:34 PM
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!
Feb 02 2021 06:38 PM
If you'd like an automated solution my toolbar has a macro to do this automatically
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
Feb 03 2021 03:03 AM
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
Feb 03 2021 04:09 AM
@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.
Feb 04 2021 06:26 AM
SolutionSo 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.
Feb 04 2021 07:24 AM
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.
Feb 04 2021 08:04 AM
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.
Feb 04 2021 06:26 AM
SolutionSo 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.