Hard return, NOT in the same cell

RonnieVDH

New Member
Joined
Oct 26, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hello, I'm new to the forum, so please bear with me. I'd like to use a formula for example =CONCATENATE(L4,CHAR(10)) where it has a hard return, but here's the catch - I need the return to be a true hard return LEAVING the cell, NOT something equal to an "alt-enter" within a cell. Does anyone have any ideas as to how to do something like that within a formula?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,831
Welcome to the forum!

Are you saying that you want the result of the formula to go into a different cell? Or part in one cell, part in another? As a rule, the result of a formula stays in one cell. There are some array formulas, where you select multiple cells, and it can fill multiple cells, but we'd need more information. Could you post a sample of your data and the expected results? If you can use the XL2BB tool, it would help.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

What exactly are you trying to accomplish? You usually don't have a value in a cell telling you to leave the cell (I have never heard of anything like that before).
So I wondering what the ultimate actual goal is here, and if you are possibly coming at it from the wrong direction.
 

RonnieVDH

New Member
Joined
Oct 26, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
I have VB code where a row is added when a hard return is entered in B35 (row A36 is automatically added.) I'd like to retrieve data from another tab into A25 and add a hard return so that row 36 automatically appears after I add the data in the other worksheet.

1603740150697.png

Where you see "FCR NO." "4" above, the "4" is the data I'd like to have go to B35 in another tab and have a "hard return" to force row 36 to appear.
1603740242323.png


If I manually hit an enter after the formula in B35, then row 36 appears. I'd prefer to have a hard return in B35 or in a helper cell that allows this row to appear automatically after entering the "4" in the original cell.
1603740342605.png

Does that make sense?
 

RonnieVDH

New Member
Joined
Oct 26, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I have VB code where a row is added when a hard return is entered in B35 (row A36 is automatically added.) I'd like to retrieve data from another tab into A25 and add a hard return so that row 36 automatically appears after I add the data in the other worksheet.

View attachment 24927
Where you see "FCR NO." "4" above, the "4" is the data I'd like to have go to B35 in another tab and have a "hard return" to force row 36 to appear.
View attachment 24928

If I manually hit an enter after the formula in B35, then row 36 appears. I'd prefer to have a hard return in B35 or in a helper cell that allows this row to appear automatically after entering the "4" in the original cell.
View attachment 24929
Does that make sense?
The first A25 should be B35 - sorry about that.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,831
So when you hit "return" in B35, it adds a new row? It sounds like you have either a Worksheet_Change or a Worksheet_SelectionChange event handler, not a formula. Given that, I'd expect that you could use an event handler on your first sheet. The logic would be something like "after the user presses enter after entering the FCR No., look on the other sheet for the row with the matching log number, then put the FCR No. in the cell next to it. If there is not a matching log number, add a row with it and put the FCR No. in the cell next to it. Then add a row after that."

Could you post your VB code so we can see what it does?
 

RonnieVDH

New Member
Joined
Oct 26, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Private Sub Worksheet_Change(ByVal Target As Range)

' Exit if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub

' Check to see if update is to column B with a value
If (Target.Column = 2) And (Target <> "") Then
' Unhide the next row
Rows(Target.Row + 1).EntireRow.Hidden = False
End If

End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
So I am still a little unclear.

Are you saying that you want to enter the values shown in your first image, and have it automatically add a new row to your other sheet populating columns A and B form "LOG NO" and "FCR NO" on the other sheet? If so, please provide the following information.

1. What is the name of the two sheets in play here?
2. What cells are "LOG NO" and "FCR NO" found in?
3. When exactly should this automation be triggered to run (after a certain cell is updated, or perhaps there is button you click somewhere)?
4. Do you ever need to check for duplicates first, or should it always add a new row of data?
 

RonnieVDH

New Member
Joined
Oct 26, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Are you saying that you want to enter the values shown in your first image, and have it automatically add a new row to your other sheet populating columns A and B form "LOG NO" and "FCR NO" on the other sheet? [[Close. I'm looking to have rows "unhidden" once I have data into the second image.]] If so, please provide the following information.

1. What is the name of the two sheets in play here? [[FCR LOG is the name of the sheet where I need the row(s) unhidden, and 025 is the name of the sheet where the data comes from]]
2. What cells are "LOG NO" and "FCR NO" found in? [[LOG NO is in Cell L3, and FCR NO is in cell L4 and they are in sheet 025 and additional sheets as we go on (026, 027, etc...]]
3. When exactly should this automation be triggered to run (after a certain cell is updated, or perhaps there is button you click somewhere)? [[This automation (the row unhide in FCR LOG) should be triggered when data is entered into L4 in sheet 025 (and subsequent rows should be unhidden when data is entered into L4 on 026, 027... Currently, it isn't triggered until someone hits enter in cell B35 in the FCR Log sheet.]]
4. Do you ever need to check for duplicates first, or should it always add a new row of data? [[No need to check for duplicates. The rows are actually already created, just hidden.]]
 

Watch MrExcel Video

Forum statistics

Threads
1,130,316
Messages
5,641,484
Members
417,211
Latest member
loadius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top