How can I copy data from a specified range of cells?

[RPantling]

I have 2 tabs within a spreadsheet and have been trying to find out how to copy the data from one tab to another.

See my issue..

Tab1
Date range: 01/01/17, 02/01/17, 03/01/17, 04/01/17, 05/01/17, 06/01/17, 07/01/17, etc...

Tab2
Date range: 01/01/17, 06/01/17, 11/01/17, 16/01/17, etc...

Tab1 has all of the data already populated, I am trying to copy the data into Tab2 for intervals of every 5 days.

How can I do a formula to copy the data from every 5 cells from Tab1 into Tab2? Rather than having to manually do a formula to equal each individual cell as I go along?

Any help appreciated, thanks!

 

[RPantling]

I have tried the formula you gave which initially showed FALSE, since there is data currently in cell B2 of the reporting sheet. If I enter the same value to match the daily cell FJ4 it then shows TRUE.

I also amended the formula to =B1=Daily!FJ3 to ensure the dates match which also returned TRUE.

 

[Joe4]

All we were trying to do with the formula is to see if the two date headers do actually match.

Perhaps there is some confusion as to wear the actual headers and data appear on each sheet.

As I read your problem, I understood it that this was the structure:

One the Daily Sheet
- Date headers appear in cells FJ4:AII4
- Data appears in cells FJ5:AII5

On the Reporting Sheet
- Date headers start in cell B2 and move across row 2
- Formulas to look up data start in cell B3 and move across row 3

Are all those assumptions correct? It sounds like the Daily sheet structure may actually be different, both things being up one row.
If that is the case, the formula will need to be adjusted, i.e.

=HLOOKUP(B$2,Daily!$FJ$3:$IV$4,2,0)

 

[RPantling]

The structure is slightly different..

Daily Sheet
- Date headers appear in cells FJ3:AII3
- Data appears in cells FJ4:AII4

Reporting Sheet
- Date headers start in cell B1 and move across row 1
- Formulas to look up data start in cell B2 and move across row 2

 

[Joe4]

OK, then adjust the formula accordingly, i.e.

=HLOOKUP(B$1,Daily!$FJ$3:$IV$4,2,0)

If you read up on that HLOOKUP function, you should be able to see what each argument is doing, and it should hopefully make sense.
Then, when you have a good understanding of it, you will be able to write and edit your own HLOOKUP formulas!

 

[RPantling]

Finally got the right formula!...

=HLOOKUP(B1,Daily!$FJ$3:$AAG$4,2,5)

Thank you very much for your help and patience on this, now I understand this will make my life a lot easier

 

[Joe4]

=HLOOKUP(B1,Daily!$FJ$3:$AAG$4,2,5)

Hmmm...
Where did the "5" come from in the last argument?
It should either be True, False, 0, or 1, as it is a boolean argument.

If it is 0 or False, it will only return exact matches.
If it is 1 or True, it will return approximate matches (meaning if you do not have a perfect match, it will return the lowest date closest to this date).
I believe that a 5 will act like 1 (True) in this instance, meaning it will return approximate matches if no exact match is found.

 

[RPantling]

I have ended the formula in 2,5.

2 is for the 2nd row in the data range I have selected, 5 for the 5th column from the data range, as I wanted to move across 5 days at a time.

 

[Joe4]

That is NOT what that argument does. It should be True or False.
Read here, specifically the last argument in that function: https://support.office.com/en-us/article/HLOOKUP-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f

Since you are matching on exact date value, it doesn't matter than you are moving across 5 columns at a time. That is inconsequential to how HLOOKUP works. It isn't "jumping" based on some value, it is matching.

 

[RPantling]

I did the formula based on the 4th argument on that site, which now does exactly as I required.

=HLOOKUP("Bolts", A1:C4, 4)

Looks up "Bolts" in row 1, and returns the value from row 4 that's in the same column (column C).

<tbody>
</tbody>

The 4 at the end of the above formula looks at the row, in my case row 2, I added the 5 to look at every 5th column.

This seems right to me, I did this formula many years ago at colleague however needed a refresher on how to do it.

 

[Joe4]

=HLOOKUP("Bolts", A1:C4, 4)

<tbody>
</tbody>

In the formula above, the 4 is the 3rd argument (count the commas). The 4th argument is optional. This is what it says about it:

Range_lookup Optional. A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

In your formula, the 5 is the 4th argument (2 is your 3rd argument):

=HLOOKUP(B1,Daily!$FJ$3:$AAG$4,2,5)

It is important to not mix these things up and to understand what each one does, or you might not always get your intended results.