Copy data from one sheet to another based on a certain criteria

[shina67]

Hi,
I have a workbook with several tabs within it.
I am wanting to copy into the tab 'Stafforce' the name, surname and hours from the 'Fire Reg' tab.
The criteria that has to be met is the clock/jw no matching the same number in the 'stafforce' tab. The relevant hours to be in the relevant days against the relevant name.
I hope this makes sense. I have put a link to my workbook if that helps.
Thanks in advance for any help.

https://www.dropbox.com/s/d60ozbk1yy...2023.xlsx?dl=0

[Pete_UK]

Many people are reluctant to download files from a third-party site, but you can attach a sample Excel workbook directly to one of your posts in this forum.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[shina67]

Hope this has worked. i have attached a sample workbook.

[Pete_UK]

You can put this formula in B4 of the Stafforce sheet:

=IFERROR(INDEX('Fire Register'!B:B,MATCH($A4,'Fire Register'!$D:$D,0)),"")

and copy across into C4:D4.

Then you can amend the formula in D4 to this:

=IFERROR(INDEX('Fire Register'!G:G,MATCH($A4,'Fire Register'!$D:$D,0)),"")

You can copy this across to J4, but you wil need to amend the column where you want to bring the data from in each cell, i.e. the G:G part (in red above) should reflect that you want to get the data from alternate columns, so change it in turn to I:I, K:K, M:M, and so on.

Then you can copy that row of formulae down.

Hope this helps.

Pete

P.S. Column S on the Fire Register sheet appears to be formatted as Text, so you should change this to Number with 2dp before you enter any data into it.

[shina67]

Thank you Pete. That has done exactly what I wanted it to do.

[Pete_UK]

Glad to hear it, and happy to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete