Can cell content changes automatically generate a lookup on another spreadsheet?

[Clelv]

Hi All,

I am have a working spread sheet I update daily.

My question is: If I manually change the content of a cell from a "?" to the word "yes", could this action then automatically prompt a completely different spread sheet to lookup the unique number from a column from the same row?

So basically when I change a word to "yes" I want another spread sheet to pick up the unique number that I've already given that row in another column, but only the rows with "yes" and as they happen if that makes sense!

I mainly want to know if this is possible and if the only way to achieve it is with VBA coding. I'm trying to learn it and think it will be a Private Sub Worksheet_change but don't want to waste time if this isn't something that's possible.

Much, much appreciated in advance.

 

[Krayons]

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

Yes, this is possible.

In THIS THREAD I help the user to perform an action when he changes a value (drop down box) to "YES".

In THIS THREAD I posted how to find a corresponding cell value in another worksheet.

I encourage you to read through both. I am sure you can find a solution by modifying to suite your needs.

Post back here if you have any questions.

The basics:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    
    ' range of cells to monitor for changes
    Set KeyCells = wsOpen.Range("B2:B50")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

        ' run your code here if any cell in that range has changed
       
    End If
    
End Sub

 

[mumps]

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

A Worksheet_change event should do what you want but we would need more information. In which column do you enter "yes" and in which column is the unique number? What is the name of the destination worksheet and where on that sheet do you want to place the unique number?

 

[Clelv]

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

Thank you both. I'm glad it can be done, now I can work at it!

On a spread sheet called "current16" I change column BM (or 65) to "yes" from a question mark. The unique identifying number is in column D (or 4) on the same row obviously.

The spread sheet I want the unique number to appear on is called "Postgraduates16" and for it to populate in column A would be fine. Then I hope to vlookup various other pieces of information using this unique number from other locations (which I can do) to complete the spread sheet.

 

[mumps]

ADVERTISEMENT

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet "current16" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change the ? to "yes" in column BM and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("BM:BM")) Is Nothing Then Exit Sub
    If Target = "yes" Then
        Range("D" & Target.Row).Copy Sheets("Postgraduates16").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub

 

[Clelv]

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

That's brilliant thank you Mumps, I will spend some time on this and let you know how I get on. Many Thanks

 

[mumps]

ADVERTISEMENT

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

My pleasure.

 

[Clelv]

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

I'm probably doing something wrong but on my first attempts I'm getting a run-time error '9': Subscript out of range. It's highlighting the fourth line "Range ("D".... " I will keep at it though. I'm probably too much of a novice to be attempting this! Thanks Clelv

 

[mumps]

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

It should work properly unless I misinterpreted how you data is organized. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Clelv]

Re: Brand New to VBA. Is this possible? Can cell content changes automatically generate a lookup on another spreadsheet?

Thanks again! I've taken your advice and have tried to explain my problem in two spreadsheets which I've placed in the dropbox and are hopefully shared for viewing, and sorry, I don't want to become a nuisance with this query!

https://www.dropbox.com/sh/29n0rv9q8bdy5p2/AABDa6peTqLspOqbJTbRPY6ja?dl=0