Results 1 to 7 of 7

Thread: Cell and Column Referencing

  1. #1

    Cell and Column Referencing



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

    Ok, so I'm going to try and explain exactly what I need the cells to do.
    Is it possible to get the cells to automatically do the following?

    If for example;


    Cell L19 = 'A' (The Letter A)
    Cell L20 = '2' (The Number 2)

    Then in on sheet 2, column A row 2 must be filled in, either (preferably)
    a color or the number 1 or letter T, doesn't really matter what it's
    filled with so long as it is filled.

    If this is at all possible, can it recognize a range of numbers; e.g.

    L19 = 'A'
    L20 = 5,6,7,8

    How do I get it to fill in Sheet 2 Column A Cell 5 and 6 and 7 and 8 with
    whatever I can fill it with.



  2. #2
    Acolyte Jon von der Heyden's Avatar
    Join Date
    Mar 2011
    Location
    Stellenbosch,South Africa
    Posts
    24
    Articles
    0
    Welcome to the excelguru forums.

    I'm struggling to understand what exactly it is that you need, and why? So, in A5:A8, do these cells require manual input by a user or are you just wanting to flag them? I'm wondering if it's a formula solution (perhaps conditional formatting) required, or a VBA sheet change procedure required. If it is a formula solution needed here then inevitably the cell or range of cells you refer to in L19 and L20 will need to be populated with a formula in order to recognise that they are being flagged (unless you are using conditional formatting).

    Is there a maximum and minimum range that you might enter in L19 and L20, or might you refer to any cell or range of cells on the grid?
    Last edited by Jon von der Heyden; 2011-03-30 at 10:12 AM.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi Thespian, and welcome to the forum.

    Have a look at the attached and see if that's what you're after.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Hi Ken

    That's exactly it! Possible to do?

    T

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi Thespian,

    I'm curious if you tried changing the values in L19 and L20? If you do, you'll see that it is actually working in that workbook. (Providing you enable macros.) So yes, totally possible.

    I assume that you'll need help making it work in your workbook though. I'd prefer you play with it first though, to make sure it does exactly what you want. Then we can cut and paste.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6

    Cool

    Sorry blonde moment.

    Yes it works perfectly.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Cool.

    To use this in your own workbook, what you need to do is this.
    • Open your workbook in Excel
    • Find sheet that has your L19 & L20 ranges on it
    • Right click the sheet's tab and choose "View Code"
    • Paste the following in the window that opens up:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sRows() As String
    Dim lRow As Long
    Dim sCols() As String
    Dim lCol As Long
    Dim wsTarget
        Select Case Target.Address
            Case Is = "$L$19", "$L$20"
                Debug.Print Target.Address
                'Set target worksheet to mark up here
                Set wsTarget = ThisWorkbook.Worksheets("Sheet2")
                
                'Get array of values
                sCols = Split(ActiveSheet.Range("$L$19"), ",")
                sRows = Split(ActiveSheet.Range("$L$20"), ",")
                
                'Ignore errors (ignores invalid characters)
                On Error Resume Next
                
                'Mark worksheet
                With wsTarget
                    For lRow = LBound(sRows) To UBound(sRows)
                        For lCol = LBound(sCols) To UBound(sCols)
                            .Range(sCols(lCol) & sRows(lRow)).Value = 1
                        Next lCol
                    Next lRow
                End With
                
            Case Else
                'Do nothing
        End Select
    End Sub
    • (If you see a line that says Option Explicit, then put it under that line.)
    • Adjust the following line to refer to the worksheet you want to paste your stuff to. (Just change the name of the worksheet between the quotes.)
    Code:
    Set wsTarget = ThisWorkbook.Worksheets("Sheet2")
    • Go back to the Excel window and test it.
    • Save the file once you are happy it's working
    If you are using Excel 2007/2010, you will need to save the file in an xlsm file format, as xlsx cannot contain macros.

    HTH,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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