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

[jdogg29]

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

[#2]

@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.

 

[#3]

@OliverScheurich 

Here's the sample file @jdogg29 sent me. I may not get to this one today.

[#4]

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?

[#5]

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.

[#6]

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.

[#7]

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.

[#8]

@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!

[#9]

@Patrick2788 @OliverScheurich Can either of you help me out with this?

[#10 · Accepted solution]

@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.

[#11]

Thanks so much for this, it seems to work. I just need to do it for all the other cells. Sorry for the delay in response!

[#12]

I put that formula for a few fields and it populates on the combined tab. The only issue is that it does not save it there. When I delete the info, it deletes from the combined tab. This report is going to be held on sharepoint and edited by multiple people. so when the next shift comes in, they will edit the cover page and put in their own info. The result that I really need is that when they delete off of the cover page, it is still recorded on the combined tab. There will never be duplicated rings, so once a ring # is recorded, it will not need to change. Is there any way of doing this?

[#13]

@jdogg29 

I would make a copy of the combined tab (sheet "Compiled Info") and then copy and paste only the values in the copied sheet before the report is edited by the next shift. This way the formulas remain in the combined tab and the data in the copy of the combined sheet doesn't change.

The only other possible solution i can imagine is as shown in a simplified example in the attached file. Let's say the ring # are entered in range A2:A5 and the additional info in ranges B2:B5 and C2:C5. You can try what happens if you enter e.g. ring # 17 in cell A2 and additional info in B2 and C2. This could return the expected result but would be difficult to do with VBA. In addition if a ring # is entered twice along with it's additional info by mistake the original data would be overwritten.

[#14]

Thank you so much for the continued help with this.

Is there any way that there can be an unique ID for x6 and the date, so that when a person picks their shift the info is tied to that shift? somehow these people want this done the other way around, that they fill the info into Excavation Checklist tab and that gets populated onto the cover page. Is this way possible at all? or is there just a way that if they fill into onto the combined tab it will to over to the cover page.

[#15]

@jdogg29 

Unfortunately i can't help you with this. It's a different task that probably requires another approach to fill the data in the other direction. Maybe you want to start a new discussion and an expert of the community can help you with this.

[#16]

@jdogg29 

The result that I really need is that when they delete off of the cover page, it is still recorded on the combined tab. 

Do you mean that the CoverPage is the entry template form and you can input on CoverPage then the save data from CoverPage to shift info and clean relative input data in CoverPage ?