none
Auto fill table with info from a single cell RRS feed

  • Question

  • Good Morning

    I have the below table on a sheet called

     'Change'

    in a workbook used to record petty cash and other funds. Petty cash handed out is also recorded on a physical bit of paper called a PC2.

    In the workbook there is a sheet for each month of the year...

    and on each sheet are cells to record the PC2 number, the amount and the other columns noted above...

    What I need, is when the PC2 number is entered into the 'Ref to PC2' cell on the 'Change' sheet, the rest of the cells are auto populated by the corresponding information entered on the month sheet.

    Change Sheet

    Ref to PC2 - Begins at Cell C18

    Original Amount - E18

    Client - G18

    Ref Number - I18

    Employee - O18

    Admin - Q18

    Month Sheet

    Ref Number - Column D

    Client - Column E

    Cash - Column H - (Original Amount)

    PC2 - Column I

    Employee - Column Q

    Admin - Column P

    On a page called 'Page List' I have this table which is named 'Page List'

    Page List
    April_2017
    May_2017
    June_2017
    July_2017
    August_2017
    September_2017
    October_2017
    November_2017
    December_2017
    January_2018
    February_2018
    March_2018
    April_2018

    I have the following formula

    =VLOOKUP(C19,INDIRECT("'"&INDEX(PageList,MATCH(1,--(COUNTIF(INDIRECT("'"&PageList&"'!E19:F377"),E19)>0),0))&"'!h802:i15"),5,0)

    but it is only returning a 0 value, I was hoping I would be able to edit it slightly for each column but it's not really working that well.

    Please help

    Many thanks in advance

    Luke

    Monday, September 4, 2017 9:48 AM

Answers

  • Hi Luke Sykes,

    You need add the function code into a standard module. Then you could call the function in cells.

    You could refer to below link for help.

    https://support.office.com/en-us/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f#bmcreatingsimple 

    And here is my document.

    https://www.dropbox.com/s/1rca4azfdn1mtqx/Record%20if%20customer%20expenditure.xlsm?dl=0  

    Best Regards,

    Terry


    • Edited by Terry Xu - MSFT Friday, September 8, 2017 10:31 AM
    • Marked as answer by Luke Sykes Friday, September 8, 2017 1:21 PM
    Friday, September 8, 2017 10:31 AM

All replies

  • Hi Luke Sykes,

    It seems that you want to get data from other month sheet once you entered PC2 number in C18 in Change sheet. You could use WorkSheet.Change event to catch the entering event.

    The key point is how to get the Month Sheet name.

    How would you want to get the Month Sheet? Which sheet is the Page List table in? What do you want to get via the formula?

    I suggest you provide more details how to get the month sheet name and share us a simply excel file so we could try to reproduce your issue. Thanks for understanding.

    Best Regards,

    Terry

    Wednesday, September 6, 2017 12:58 AM
  • Hi Terry

    I looked at a few other forum posts and one suggested having a separate sheet with a list of sheet names, and give the data field a name 'PageList' which is noted in the formula I have...

    =VLOOKUP(C18,INDIRECT("'"&INDEX(PageList,MATCH(1,--(COUNTIF(INDIRECT("'"&PageList&"'!E19:F377"),E18)>0),0))&"'!h802:i15"),5,0)

    I have also tried to use 'April_2017:April_2018' in place of 'PageList' which also doesn't work.

    I have attached a basic example of the spread sheet, cell locations and sheet names all correspond properly to the actual database I have

    View file


    • Edited by Luke Sykes Wednesday, September 6, 2017 9:38 AM
    Wednesday, September 6, 2017 9:28 AM
  • Hi Luke Sykes,
    I would suggest you custom an UDF function to return the row there the pc2 number is.
    After getting the row range, you could use Index method to got other value like Client,Ref,Cash...
    Here is the UDF.
    Function ReturnRange(ByVal rng As Range)
    Dim pageRange As Range
    Dim tmpWorkSheet As Worksheet
    Dim rowIndex As Integer
    pc2 = 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)
                      If rowIndex <> 0 Then
                            ReturnRange = tmpWorkSheet.Rows(rowIndex + 14)
                            Exit Function
                       End If
                  End If
    Next cell
    End Function
    For instance, if you want to get the Original Amount(Cash), you could type formula like
    =INDEX(ReturnRange(C19),1,8)
    For Client
    =INDEX(ReturnRange(C19),1,5)....
    Best Regards,
    Terry
    Thursday, September 7, 2017 11:40 AM
  • Hi Terry,

    I'm sorry but I'm not understanding how this will work.

    I have pasted your code to the VBA project on the change sheet but when I add the PC2 number to the PC2 cell on the change page, nothing happens, I also cant see how =INDEX(ReturnRange(C19),1,8) looks at the other pages to return the info from the rows.

    Is there any way you can update the file I uploaded to drop box so I can look at your coding working in the example.

    I thought there would just be a VLOOKUP formula I could use.

    Sorry for being dumb.

    Many thanks

    Luke

    Thursday, September 7, 2017 2:18 PM
  • Hi Luke Sykes,

    You need add the function code into a standard module. Then you could call the function in cells.

    You could refer to below link for help.

    https://support.office.com/en-us/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f#bmcreatingsimple 

    And here is my document.

    https://www.dropbox.com/s/1rca4azfdn1mtqx/Record%20if%20customer%20expenditure.xlsm?dl=0  

    Best Regards,

    Terry


    • Edited by Terry Xu - MSFT Friday, September 8, 2017 10:31 AM
    • Marked as answer by Luke Sykes Friday, September 8, 2017 1:21 PM
    Friday, September 8, 2017 10:31 AM
  • FANTASTIC!!!!

    Thank you so much!!!!!

    Friday, September 8, 2017 1:21 PM