Solved

How to make data flow from one cell to next in Excel

Posted on 2014-12-10
10
442 Views
Last Modified: 2015-01-15
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.
0
Comment
Question by:Jeff Perkins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 70

Expert Comment

by:KCTS
ID: 40491974
Can't be done. Wrap text is as good as it gets.
0
 
LVL 15

Author Comment

by:Jeff Perkins
ID: 40492179
Any suggestions as to something else we can use to do this?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40492235
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
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 15

Author Comment

by:Jeff Perkins
ID: 40492284
Leon,
Is this something you think you would like to undertake?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40492455
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.
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

Open in new window


Please note that I have not tested this in great detail and some adjustments will need to be made.

Leon
0
 
LVL 15

Author Comment

by:Jeff Perkins
ID: 40492479
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.
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 40492529
Ok, here is a primer http://msdn.microsoft.com/en-us/library/office/ee814737%28v=office.14%29.aspx

To summarize you will need to do the following:
1. Open your Invoice template
2. Press Alt - F11 keys - This will open the VB Editor. You should see VBAProject  on the left side
3. Double click on the sheet name containing your invoice in the VBAProject window. You should get a blank sheet on the right side. It will say (General) and (Declarations) in the two drop down boxes above the sheet
4. Copy/Paste the code I gave you above into this sheet.
5. Go back to the Excel sheet of your invoice and save. Make sure you are saving the file with a .xls or .xlsm extension depending on your Excel version.
6. Try typing more than 100 characters into your invoice sheet.

If everything goes fine, you will want to adjust that number of characters. To do that, use Alt-F11 to go back to the editor and change the value of MAX_STRING_LEN from 100, to the one you want. Save and try it out.

Let me know if you are having issues,
Leon
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40493549
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
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40550853
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question