need to see where data is

s7y

New member
Joined
Aug 19, 2014
Messages
24
Reaction score
0
Points
0
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 helpView attachment Sample.xlsx
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Sorry, your question doesn't make sense to me. Can you clarify or elaborate further?
 

s7y

New member
Joined
Aug 19, 2014
Messages
24
Reaction score
0
Points
0
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.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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?
 

s7y

New member
Joined
Aug 19, 2014
Messages
24
Reaction score
0
Points
0
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:

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
 

Attachments

  • Sample.xlsb
    265.2 KB · Views: 6

s7y

New member
Joined
Aug 19, 2014
Messages
24
Reaction score
0
Points
0
oh, that will not be a problem. I can name them R and L... so 11L and 11R....
 

s7y

New member
Joined
Aug 19, 2014
Messages
24
Reaction score
0
Points
0
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?
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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.
View attachment 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/using-personal-macro-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/tables-pivottables-and-macros-music-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:

s7y

New member
Joined
Aug 19, 2014
Messages
24
Reaction score
0
Points
0
I have copied both sheets in your sample but they still don't work. but... I will wait till you say that the sample is ready to use before continuing...
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Okay. Try the attached sheet. It gives you a message if you try to add something into the container that isn't in your list on sheet1. You can easily turn that off if you want. Or change the language etc.View attachment Sample.xlsb

Note that I also added something for the BULK area. It gets triggered if the user enters something in cell U40. This is easy to change if you need it to be a cell somewhere else.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
It works fine for me. What version of Excel are you using? Try saving it and reopening it. You will need to enable macros.
 

s7y

New member
Joined
Aug 19, 2014
Messages
24
Reaction score
0
Points
0
Hello Jeffrey, is there a way I can send you the actual program for you to see? there are some macros and stuff that might be influencing the correct functioning of your program...
 
Top