Jeff Perkins
asked on
How to make data flow from one cell to next in Excel
I've been using Excel for years and this may not be possible. But I figured if it was, someone here would know how.
If it's not, I'd be interested in options.
I'm trying to create a simple receipt using Excel Invoice templates. We've tried several and even tried making one from scratch.
What we are wanting to do, is to have the description of an item drop down to the next line if it won't fit in the allotted space.
Example
Qty (column) Description (column) Price (column)
If I start typing here when it
gets to the next line I want it
to drop down to the next cell.
We are familiar with word wrap, and that creates unexpected and unwanted behavior. It will kick all the cells down a line and this causes the invoice/receipt to create a second unwanted page.
We are using Excel 2010, if that makes any difference, on Windows 7 Pro systems.
Any and all help is appreciated.
If it's not, I'd be interested in options.
I'm trying to create a simple receipt using Excel Invoice templates. We've tried several and even tried making one from scratch.
What we are wanting to do, is to have the description of an item drop down to the next line if it won't fit in the allotted space.
Example
Qty (column) Description (column) Price (column)
If I start typing here when it
gets to the next line I want it
to drop down to the next cell.
We are familiar with word wrap, and that creates unexpected and unwanted behavior. It will kick all the cells down a line and this causes the invoice/receipt to create a second unwanted page.
We are using Excel 2010, if that makes any difference, on Windows 7 Pro systems.
Any and all help is appreciated.
Can't be done. Wrap text is as good as it gets.
ASKER
Any suggestions as to something else we can use to do this?
You may be able to do something with the selection change event through VBA. Basically the idea would be to check the size of the string after the user hits Enter and cut the string if its too large. There are some limitations on this based on your invoice template, but it should not be too hard to code for.
Leon
Leon
ASKER
Leon,
Is this something you think you would like to undertake?
Is this something you think you would like to undertake?
Ok, lets give this a try. Place this code in the sheet module. I am assuming 100 character string length here. If you want bigger or smaller you can change the parameter.
Please note that I have not tested this in great detail and some adjustments will need to be made.
Leon
Private PreviousActiveCell As Range
Private Const MAX_STRING_LEN = 100
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static pPrevious As Range
Dim strPrimary As String, strNext As String
On Error Resume Next
If pPrevious.Address = "" Then
End If
If Err.Number <> 0 Then
Set pPrevious = Me.Cells(1, 1)
Err.Clear
End If
Set PreviousActiveCell = pPrevious
Set pPrevious = ActiveCell
strPrimary = PreviousActiveCell.Value
If Len(strPrimary) > MAX_STRING_LEN Then
strNext = Right(strPrimary, Len(strPrimary) - MAX_STRING_LEN)
strPrimary = Left(strPrimary, MAX_STRING_LEN)
PreviousActiveCell.Value = strPrimary
PreviousActiveCell.Offset(1).Value = strNext
End If
End Sub
Please note that I have not tested this in great detail and some adjustments will need to be made.
Leon
ASKER
Ok Leon, perhaps I should have explained before asking you to take this on, I'm an Excel user... and not much more than that. How do I "Place this code in the sheet module"? I understand basics of what a module is, or believe it's a snippet of code such as you have written that creates more functionality. But have no idea how to add or use a module in a template.
If that makes this task undesirable, I understand, but if not, I'm a quick study if you can tell me how to create a module, and utilize it.
If that makes this task undesirable, I understand, but if not, I'm a quick study if you can tell me how to create a module, and utilize it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Stick with Wrap Text but then set the Page settings to Fit to One Page.
Or if you want to limit the description to say 6 lines, merge 6 cells and then description can then fill the merged cells.
You can also set it to "Shrink to Fit", you have to untick Wrap text first as Shrink is greyed out when Wrap text is selected.
Thanks
Rob H
Or if you want to limit the description to say 6 lines, merge 6 cells and then description can then fill the merged cells.
You can also set it to "Shrink to Fit", you have to untick Wrap text first as Shrink is greyed out when Wrap text is selected.
Thanks
Rob H
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.