Forum Discussion

jdogg29's avatar
jdogg29
Brass Contributor
Feb 21, 2023
Solved

Pulling data fields from reports (based on date) and compiling them into another tab.

Hello,   I have an inspector report that I am trying to pull data from certain fields based on a Ring #.  The inspector will fill out the main report page and then the selected fields will record o...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 23, 2023

    jdogg29 

    Hello, 

    instead of an IF command i've tried this formula

    =IFERROR(INDEX(CoverPage!$I$51:$I$53,MATCH([@Ring],CoverPage!$C$51:$C$53,0)),"")

    in cell B3 of sheet "Compiled Info" and it seems to return the expected result. The formula is spilled down within the dynamic table. However because of the merged cells in sheet "CoverPage" i find it difficult to help with this task.

Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 21, 2023
This is do-able with a formula but before proceeding, I have one concern:

The Cover Page includes the TODAY() function. When data is entered into the various areas and then moved to the Compiled Info sheet, would the cover page eventually be cleared out so a different day's data could be entered?
jdogg29's avatar
jdogg29
Brass Contributor
Feb 21, 2023
Hello, yes every shift the sheet is cleared or overwritten. I updated the original post to include that the data for most sections needs to be pulled from the ring number. the only fields that need to be pulled by the date are these ones in the shift information tab.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 21, 2023
    I think this changes the solution from array-based formulas to VBA. The Cover Page would serve as an input form with a submit button to write the data to the compiled sheet. After the data is written to the Compiled Sheet it would be cleared from the Cover Page.
    • jdogg29's avatar
      jdogg29
      Brass Contributor
      Feb 22, 2023

      Patrick2788 OliverScheurich 

       

      I have been playing around with this and actually for one field working on the compiled tab by using this formula - =IF('CoverPage'!$C$51=[@Ring],'CoverPage'!MuckWeight,"")

       

      But the issue is that when the ring number on the cover page changes, it just disappears from the compiled tab.  how can I get these to stay as a record?  See my updated file attached please.  Also, thank you both so much for the help with this!

    • jdogg29's avatar
      jdogg29
      Brass Contributor
      Feb 22, 2023
      ok, so how do I do that? I have never worked with VBA before.

      It is ok if the data that one inspector is still there, he can just delete it and update it with new info. as long as when they type in the ring number, the info on that report is tied to that ring # on the compiles sheet.

Resources