Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: need to see where data is

  1. #1

    need to see where data is



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hello everybody,

    In sheet1 I input the data in rows. I then select and copy a row (macro shortcut crtl+p) and go to sheet 2 where I paste the data (in column) (macro shortcut crtl+m). This all kinda work.

    In sheet 2 each column (range where I paste the data) has a name. I need this name to be visible in sheet1.

    Please see attached sample, where I have inserted (in sheet1) the desired result manually.

    thanks in advance for your helpSample.xlsx

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Sorry, your question doesn't make sense to me. Can you clarify or elaborate further?

  3. #3
    Hello Jeffrey,

    if you open sheet2 you will see that, as example, PAG06404 is in position C. if you open sheet 1 you will see that in F11 I have C.

    on sheet2 PAG07460 is in position CEL. on sheet1 F22 I have CEL. These examples I have inserted manually. I need this to be done automatically. On sheet2 I need to see the position chosen on sheet1.

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    It's very tricky to do this without resorting to Macros (VBA) the way the input sheet (Sheet2) is laid out. Is there any chance you can change the layout of that sheet? Or does it have to be laid out like that?

  5. #5
    the layout must remain the same as it represents the inside of an airplane. Of course I don't mind if the solution is a VBA code....
    Last edited by s7y; 2014-08-20 at 08:54 AM.

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Okay, try the attached file.It works by named ranges and VBA code. A problem with the approach is that you have two of some 'positions' e.g. Positions 11-14, 21-23, 31-33, 41-43. My approach requires that these all be unique. So if you put something in say cell B41, my approach will work. But if you put something in cell B45 it won't. You need to rename those lower boxes to something unique.I probably need to explain this more to you but its late here and I'm tired. So will talk later.
    Attached Files Attached Files

  7. #7
    oh, that will not be a problem. I can name them R and L... so 11L and 11R....

  8. #8
    Ok, I have tried your sample and it works. then I have tried to copy the formulas and the ranges into my workbook but nothing works... any suggestions?

  9. #9
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    oh, that will not be a problem. I can name them R and L... so 11L and 11R....
    Cool. Which one will be which? i.e. will the top one be L or R? Let me know, and I'll amend the sample file accordingly. Don't use the sample file as is...I'm not quite finished with it.


    Ok, I have tried your sample and it works. then I have tried to copy the formulas and the ranges into my workbook but nothing works... any suggestions?

    Can't you just delete the existing sheet, and copy this sheet in to the workbook in it's place? (Right click the sheet tab, select Move or Copy, and select the name of the destination workbook from the dropdown at the top).

    Otherwise, you'll have to do quite a bit of setup.
    Sample.xlsb


    Let's take a look at how it works. Click on each cell below the headings (i.e. A, B, C, 12P, 13P etc) and you'll see in the name box that every one of them has been given a name e.g. Area_A, Area_B, Area_C, Area_12P, Area_13P.

    Also note that adding these names manually would be tedious, so I wrote some code that does it. Note - this code lives in my personal macro workbook i.e. it doesn't need to be in the actual sample file...it's code I will use again and again on different projects. If you're not familiar with what a personal macro workbook is, read http://chandoo.org/wp/2013/11/18/usi...acro-workbook/
    Code:
    Sub CreateNamesFromTopRow()
    
        Dim rCell As Range
        Dim sCol As String
        Dim sPrefix As String
        Dim strPrompt As String
    
        If TypeName(Selection) = "Range" Then
            strPrompt = "I'll use the headings in the top row to name each range." & vbNewLine & vbNewLine
            strPrompt = strPrompt & "OPTIONAL:  You can enter a prefix below if you want, and I'll use it to prefix each Named Range with." & vbNewLine & vbNewLine
            strPrompt = strPrompt & "Otherwise just push OK, and I'll u se the headings as is."
    
            sPrefix = Application.InputBox( _
                    Title:="Please input a prefix if you want one...", _
                    Prompt:=strPrompt, _
                   Type:=2)
                If sPrefix = "False" Then Exit Sub
    
            For Each rCell In Selection.Rows(1).Cells
                If rCell.Value <> "" Then ActiveWorkbook.Names.Add Fix_Name(sPrefix & rCell.Value), "=" & rCell.Offset(1).Address
            Next rCell
        End If
    
    End Sub
    To use this, you select a block of headings and one row of cells underneath - e.g. A4:R5 in your sample workbook - then run the code. It will prompt you to enter a Prefix - in this case we're using Area_ - and then when you push OK it will add names to the cells in the row below that heading. Note that you can't use this to set up names in those boxes where the headings are below the box...you'll have to do those ones manually


    Also, on Sheet 1 you'll see that I've turned that list into an Excel Table (using the Ctrl + T keyboard shortcut). If you're not familiar with Excel Tables, read http://chandoo.org/wp/2014/03/28/tab...-to-your-ears/

    Lastly, I have assigned names to a couple of the columns in that table. So I select the ULD column from B2 to B29 (i.e. the column excluding the heading) and typed ULD into the Name Box, and then did the same for the Position column, calling it Position. (I did this, because the VBA code uses these names to do things...and if I instead used the Table names in the VBA code, then if you were to rename those table column headings then the code wouldn't work)

    Then I added this code to the Sheet2 code module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        'Identifies any Named Ranges that map directly to rng
        Dim nm As Name
        Dim strName As String
        Dim varRow As Variant
        Const strPrefix = "Area_"
        
        If Target.Cells.Count > 1 Then Exit Sub
    
        For Each nm In ActiveWorkbook.Names
            If Replace(Replace(nm.RefersTo, "=", ""), "'", "") = ActiveSheet.Name & "!" & Target.Address And Left(nm.Name, Len(strPrefix)) = strPrefix Then
                strName = nm.Name
                Exit For
            End If
        Next
    
        If strName <> "" Then
            varRow = Application.Match(Target.Value, [ULD], 0)
            If Err.Number = 0 Then
                [Position].Cells(varRow).Value = Replace(nm.Name, strPrefix, "")
            Else: MsgBox "Item does not exist in list"
            End If
        End If
    
    
    End Sub
    That code keeps track of what cells you change. If you change a cell with a named range assigned to it - and if that named range is prefixed with "Area_" - then the code goes "Aha...the user is doing something to the cells we care about". It works out which heading is involved by simply reading the name of the named range...e.g. if it was Area_12p then the code says "Aha...the user has just put something in the box associated with 12P. I better see what that something was, and if it's in that table on sheet 1 I better write 12P in the associated row in the Position column".
    Last edited by JeffreyWeir; 2014-08-20 at 09:57 PM.

  10. #10
    R on top and L on the bottom

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •