none
Auto fill a table with all entries, row by row, depending on a reference number entered into a cell RRS feed

  • Question

  • Dear All

    I have built a table which I need to populate with all cash, voucher, credit card etc assistances issues to a client over time.

    There is a sheet for each month, and each client has their own unique reference number.

    I am using this formula...

    =INDEX(ReturnRange(E10),1,5) E10 is the cell where the reference number is entered

    ...edited for the columns it drags the values from on each of the sheets, however, instead of returning every row on each of the sheets relating to the reference number it is just looking at one instance of the reference number and filling the table with the same instance over and over

    Here is the VBA I'm using

    Many Thanks

    Luke

    Function ReturnRange(ByVal rng As Range)
    Dim pageRange As Range
    Dim tmpWorkSheet As Worksheet
    Dim rowIndex As Integer
    pc2 = rng.Value
    K_Number = rng.Value
    Set pageRange = ThisWorkbook.Names("PageList").RefersToRange
    For Each cell In pageRange.Cells
    On Error Resume Next
    Set tmpWorkSheet = Worksheets(cell.Value)
                If Not tmpWorkSheet Is Nothing Then
                       rowIndex = WorksheetFunction.Match(pc2, tmpWorkSheet.Range("I15:I802"), 0)
                       rowIndex = WorksheetFunction.Match(K_Number, tmpWorkSheet.Range("d15:d802"), 0)
                      If rowIndex <> 0 Then
                            ReturnRange = tmpWorkSheet.Rows(rowIndex + 14)
                            Exit Function
                       End If
                  End If
    Next cell
    End Function

    Friday, September 8, 2017 3:40 PM

Answers

  • Hi Luke Sykes,

    If you want to run the macro form the ribbon, I think you need remove parameter of the function. In fact, the function does not use rng in its code. We get ref number from fixed cell (Family Totals!E10).

    If you want to run the macro once you enter in E10, you could use WorkSheet.Change event and Intersect function to check if you have edit in E10. Once you edited in E10, then call the function.

    Best Regards,

    Terry

    • Marked as answer by Luke Sykes Friday, September 15, 2017 11:04 AM
    Friday, September 15, 2017 8:37 AM

All replies

  • Hi Luke Sykes,

    I'm afraid you have several threads left unresolved, or unmarked.[

    It is expected for them to be resolved or marked before creating new threads.

    Regards,


    Ashidacchi



    • Edited by Ashidacchi Friday, September 8, 2017 4:05 PM
    Friday, September 8, 2017 4:03 PM
  • Hi Luke Sykes,

    >> instead of returning every row on each of the sheets relating to the reference number it is just looking at one instance of the reference number and filling the table with the same instance over and over

    What do you mean returning every row on each of the sheets? Could same PC2 number be related to data rows in multiple sheets? What data do you want to get in change sheet?

    Best Regards,

    Terry



    Monday, September 11, 2017 9:44 AM
  • Hi Terry

    I have adapted you code to also look at reference numbers to return other information to a new sheet called Family Totals hence the additional RowIndex line of code.

    So the sheet named 'Family Totals' has 9 columns all relating to columns on the month sheets 'April_2017' for example.

    When I enter the ref number for the client in Cell E10, that is supposed to look for every entry on every month page relating to that ref number and return it in its respective column row by row, however when I enter the ref number it is just repeating one single entry over and over in every row.


    • Edited by Luke Sykes Monday, September 11, 2017 10:51 AM further explanation added
    Monday, September 11, 2017 10:05 AM
  • Hi Luke Sykes,

    It seems you want to search for multiple data rows via same PC2 number and then enter these data rows row by row.

    I think you have to use a macro to do this work. Formula in cell could only return a value and then show in current cell. It could not change value in other cells.

    You could iterate all the sheets and look for the rows which match the PC2 number in specific cell and then do what you want to  handle the row data.

    Here is the example that gets pc2 number from E10.

    Function IterateThroughSheets(ByVal rng As Range)
    
    Sub IterateThroughSheets()
    
    Dim pageRange As Range
    
    Dim tmpWorkSheet As Worksheet
    
    Dim rowIndex As Integer
    
    pc2 = Sheets("Change").Range("E10") 'get pc2 number from specific cell, such as E10
    
    Set pageRange = ThisWorkbook.Names("PageList").RefersToRange
    
    For Each cell In pageRange.Cells
    
    On Error Resume Next
    
    Set tmpWorkSheet = Worksheets(cell.Value)
    
                If Not tmpWorkSheet Is Nothing Then
    
                      For rowIndex = 15 To 802
    
                      If tmpWorkSheet.Cells(rowIndex, 9) = pc2 Then
    
                                'do what you want to do to handle the row
    
                       End If
    
                     Next rowIndex
    
                  End If
    
              Set tmpWorkSheet = Nothing
    
    Next cell
    
    End Sub

    Best Regards,

    Terry

    Tuesday, September 12, 2017 10:37 AM
  • Hi Terry

    The search is by ref number, not PC2 number as the PC2 number would be unique to one single transaction however the ref number is the ID of the client so it would be used multiple times.

    The sheet the table is on is called Family Totals.

    The clients ref number would be entered in cell E10, then the table would return the results of each transaction throughout the work book, row by row.

    For your macro, does it require the table on the Family Totals sheet to match identically the table on the month sheets to return the information in the correct column and cell? 

    If you still have my original template I posted to dropbox, then you could compare that with the new table.


    • Edited by Luke Sykes Tuesday, September 12, 2017 12:08 PM
    Tuesday, September 12, 2017 12:07 PM
  • Hi Luke Sykes,

    You could easily adjust code for your need. Get Ref number from E10 and enter data from Row 18 one by one, etc.

    Here is a simply example.

    Dim pageRange As Range
    Dim tmpWorkSheet As Worksheet
    Dim rowIndex As Integer
    Dim ws As Worksheet
    Set ws = Sheets("Family Totals")
    ref = ws.Range("E10") 'get pc2 number from specific cell, such as E10
    lastRowIndex = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRowIndex < 17 Then lastRowIndex = 17
    Set pageRange = ThisWorkbook.Names("PageList").RefersToRange
    For Each cell In pageRange.Cells
    On Error Resume Next
    Set tmpWorkSheet = Worksheets(cell.Value)
                If Not tmpWorkSheet Is Nothing Then
                      For rowIndex = 15 To 802
                      If tmpWorkSheet.Cells(rowIndex, 4) = ref Then
                            ws.Cells(lastRowIndex + 1, 3) = tmpWorkSheet.Cells(rowIndex, 3) 'Date
                            ws.Cells(lastRowIndex + 1, 5) = tmpWorkSheet.Cells(rowIndex, 8) 'cash
                            ' add other column for your need
                           lastRowIndex = lastRowIndex + 1
                       End If
                     Next rowIndex
                  End If
              Set tmpWorkSheet = Nothing
    Next cell

    Best Regards,

    Terry

    Wednesday, September 13, 2017 11:13 AM
  • Thank you Terry

    I've adapted it to this but now nothing happens. Sorry to be a pain!

    Function IterateThroughSheets(ByVal rng As Range)
    Dim pageRange As Range
    Dim tmpWorkSheet As Worksheet
    Dim rowIndex As Integer
    Dim ws As Worksheet
    Set ws = Sheets("Family Totals")
    ref = ws.Range("E10")
    lastRowIndex = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRowIndex < 17 Then lastRowIndex = 17
    Set pageRange = ThisWorkbook.Names("PageList").RefersToRange
    For Each cell In pageRange.Cells
    On Error Resume Next
    Set tmpWorkSheet = Worksheets(cell.Value)
                If Not tmpWorkSheet Is Nothing Then
                      For rowIndex = 15 To 802
                      If tmpWorkSheet.Cells(rowIndex, 4) = ref Then
                            ws.Cells(lastRowIndex + 1, 3) = tmpWorkSheet.Cells(rowIndex, 3) 'Date
                            ws.Cells(lastRowIndex + 1, 5) = tmpWorkSheet.Cells(rowIndex, 8) 'cash
                            ws.Cells(lastRowIndex + 1, 7) = tmpWorkSheet.Cells(rowIndex, 10) 'vouchers
                            ws.Cells(lastRowIndex + 1, 9) = tmpWorkSheet.Cells(rowIndex, 15) 'cards
                            ws.Cells(lastRowIndex + 1, 11) = tmpWorkSheet.Cells(rowIndex, 12) 'Bus Tickets
                            ws.Cells(lastRowIndex + 1, 13) = tmpWorkSheet.Cells(rowIndex, 13) 'bus and train
                            ' add other column for your need
                           lastRowIndex = lastRowIndex + 1
                       End If
                     Next rowIndex
                  End If
              Set tmpWorkSheet = Nothing
    Next cell
    
    End Function



    • Edited by Luke Sykes Thursday, September 14, 2017 8:36 AM
    Thursday, September 14, 2017 8:23 AM
  • Hi Luke Sykes,

    Do you get any error message when running the macro? How do you call the macro?

    I would suggest you share a simple excel file so we could try to reproduce your issue.

    Best Regards,

    Terry

    Friday, September 15, 2017 6:45 AM
  • When I type the reference number into E10, nothing happens.

    When I debug the macro there are no errors.

    When I try to run the macro from the ribbon in the project (Green play button) it just brings up a window with all of the other macros, not this function.

    Friday, September 15, 2017 7:56 AM
  • Hi Luke Sykes,

    If you want to run the macro form the ribbon, I think you need remove parameter of the function. In fact, the function does not use rng in its code. We get ref number from fixed cell (Family Totals!E10).

    If you want to run the macro once you enter in E10, you could use WorkSheet.Change event and Intersect function to check if you have edit in E10. Once you edited in E10, then call the function.

    Best Regards,

    Terry

    • Marked as answer by Luke Sykes Friday, September 15, 2017 11:04 AM
    Friday, September 15, 2017 8:37 AM
  • I think I have figured it out.

    I have put the sheet change macro on the family totals vba project with the 'call' string which runs the function macro.

    That seems to have soreted it!

    Thank you :) 


    • Edited by Luke Sykes Friday, September 15, 2017 11:04 AM
    Friday, September 15, 2017 10:25 AM