Taking specific values from one Excel file and copying them into another in a list

With both files open, and with a date in A2 of the Book1 file, you can use this formula in B2:

=IFERROR(INDEX('[Traffic Sheet - April 2019 edit.xlsx]Sheet1'!$D$6:$P$140,(ROWS($1:1)-1)*5+1,MATCH($A$2,'[Traffic Sheet - April 2019 edit.xlsx]Sheet1'!$D$3:$P$3,0)+2)&"","")

Copy this down as far as you need to. I've only set the range to suit the (two) days that you show in your file, but for your real file you will need to adjust the $P shown in red above.

If the Traffic Sheet file is not open, you will need to show the full path to the file before the sheet name.

Hope this helps.

Pete

Hi Pete,

Thanks! This seems to work great!

I just have a question? So my full file path is something like \\farp01\Traffic$\2019\04 Traffic Sheet 2019 - April 2019.xlsx - what would the formula look like for me to insert this correctly? I will definitely need to add in the file path because it won't always be open.

You need to include the full path inside the opening apostrophe and before the [ at the start of the filename - this occurs twice in the formula.

The easiest thing to do is once you have set up the formula that I gave you with both files open, then just close the Traffic file - Excel will adjust the formula for you and include the full path.

Hope this helps.

Pete