Forum Discussion

jdogg29's avatar
jdogg29
Brass Contributor
Feb 21, 2023

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 on another tab.  The way I can see to trigger this is to go by the Ring #. 

 

 

The highlighted cells above, I need to pull that info to another sheet buy the Ring #

 

 

I think it might be possible to use the IF command, so that when the Ring # is typed in, whatever is input into the highlighted fields get recorded on the other tab.  Is this at all possible?

 

I am not very savvy with excel... I have a sample workbook if anyone can help me

 

Thanks

 

  • 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
    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
      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
        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 

    =IFERROR(INDEX($C$2:$C$18,MATCH(1,($A$2:$A$18=F2)*($B$2:$B$18=G2),0)),"")

    Is this similar to what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

Resources