+ Reply to Thread
Results 1 to 3 of 3

Move values into other cell and sheet with Data validation (index, offset...)

  1. #1
    Registered User
    Join Date
    01-07-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    10

    Move values into other cell and sheet with Data validation (index, offset...)

    Hello,

    I tried to make I useful file for myself to work but I'm confused.
    I would like created something dynamic with excel, but I don't knof if macro or data validation would be better?
    I discovered data validation this week and is it preferable to be able to do this with excel functionality or using macro with vba...

    I have a file with some data but I don't know how to manage it to do what i want!

    First, I would like to add value from colums B into 3 differents columns (F,G,H) but the value from column A will decide which one (F,G or H).

    Secondly, I would like with a drop down or combobox be able to see the data from the 3 differents columns (F,G,H from sheet scope) into a new sheet (version ...)

    This is files with some comments : test1.xlsm

    If anyone could give me an advice, for the best start?

    Do not hesitate to ask me to have more explaination about what I would like to do.

    Many thanks

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Move values into other cell and sheet with Data validation (index, offset...)

    Suggest the following.

    Update you worksheet_change code on Scope Sheet.

    Please Login or Register  to view this content.
    Define dynamic named ranges e.g.

    Range1A RefersTo =OFFSET(Scope!$F$2,0,0,COUNTA(Scope!$F:$F)-1,1)

    Set the input range for your list boxes e.g.

    Sheet Version1A test1.xlsm!Range1A
    Martin

  3. #3
    Registered User
    Join Date
    01-07-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Move values into other cell and sheet with Data validation (index, offset...)

    Thanks for your help, I test your code and that indicated an error. I got only column G filed, then I tried to add more condition in the loop "for N" but didn't work right now.
    I changed it like that:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R1 As Worksheet
    If Not Intersect(Target, Range("A3:A65000")) Is Nothing Then
    Set R1 = Sheets("Scope")
    Select Case MsgBox("Are you sure to modifie this cell?", vbYesNo + vbQuestion, "Checking")
    Case vbYes
    Application.EnableEvents = False
    For Each Cell In Target
    If Cell.Column <= 2 Then
    Range(Cells(2, 6), Cells(Rows.Count, 8)).Clear
    For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    TargetColumn = Rows(1).Find(Cells(N, 1), , xlValues, xlWhole).Column
    Cells(Rows.Count, TargetColumn).End(xlUp).Offset(1, 0) = Cells(N, 2)
    Next N
    End If
    Next Cell
    Application.EnableEvents = True
    Case vbNo
    'Target last value
    End Select
    Else
    End If
    End Sub


    And that will do what I expect I think, we won't be allow to change the column B, but we could change column A, and then that will change columns G, F and H.

    Thanks in advance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1