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

RPantling

New Member
Joined
Jan 11, 2017
Messages
14
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!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

There are a few ways you can go about this, and which options you can use really depends on the design of your sheet and location of the data within the sheet (which we really do not have a good visual image of what your sheet structure looks like).

One way is to use an INDEX/MATCH formula to look up the data from one and paste it to the other.

Another option is to use INDIRECT to build the references, which we can use to build the range references dynamically.

Can you describe the structure of each sheet?
What rows/columns is your header/date range in on each sheet (looking for exact range addresses)?
What rows/columns does your data actually start in on each sheet (looking for exact range addresses)?
 

RPantling

New Member
Joined
Jan 11, 2017
Messages
14
Thanks for the reply!

Hopefully this makes sense...

Tab1 (Daily) has a date range from FJ to AII, date 03/01/17 is in cell FJ4 on the daily tab - The dates are for weekdays only, not including bank holidays.

Tab2 (Reporting) has a date range from B to DJ, date 03/01/17 is cell B2 on the reporting tab - The dates are for every Tuesday from 3rd Jan onwards.

I need data from the daily tab to be copied into the reporting tab, e.g. daily cell FJ4 for 03/01/17 into reporting cell B2 for 03/01/17. (I can do this step fine using =Daily!FJ4)

Since the reporting tab dates go up in intervals of 7 days, 03/01/17, 10/01/17, 17/01/17 etc... And the daily tab goes up in intervals of 1 day (not including weekends) I need a formula to skip cells that the reporting tab will copy data from the daily tab from.

So reporting cell b4=daily FJ4, reporting cell C4=daily FO4, reporting cell D4=daily FT4 and so on... It skips to every Tuesday.

Hope that makes sense!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Will there only be one row, or will there be multiple rows?
If multiple rows, how are the rows being matched up between the two sheets?
 

RPantling

New Member
Joined
Jan 11, 2017
Messages
14

ADVERTISEMENT

The data only needs to go into 1 row
 

RPantling

New Member
Joined
Jan 11, 2017
Messages
14

ADVERTISEMENT

Not having much luck with this unfortunately, displaying 0's :(

Please can you explain what the 2,0 is for at the end of the formula?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Did you take a look at the link I gave you? It explains the function in detail (including all the different arguments) and gives details.
Note that for the last argument, 0 is the same as FALSE (and 1 is the same as TRUE) - as boolean values have numerical equivalents. You can use either.

Note that in order for this to work, your date headers need to match EXACTLY. If one is date, and the other is Text, it will not work.
Also, if they are both text but one has extra spaces, that will not work also.

This should be a good test. Enter this formula anywhere on your Reporting sheet:
Code:
=B2=Daily!FJ4
Since both of these cell values should be 03/01/17, it should return TRUE.
If it does not, then Excel is not viewing those dates as being the same, and you have some clean-up work to do.
 

RPantling

New Member
Joined
Jan 11, 2017
Messages
14
Been playing around for a while now but still getting nowhere.

Not sure if I explained it properly, but I'm not looking to copy the date, I'm looking to copy the value for each date, which is in the cell below.

Reporting cell B2 has a value for the date of 03/01/17 in B1. So the value of reporting cell B2 needs to equal the value of daily cell value in FJ4. Then reporting cell C2 needs to equal daily cell FO4, which is 5 columns across.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Not sure if I explained it properly, but I'm not looking to copy the date, I'm looking to copy the value for each date, which is in the cell below.
No, you explained it clearly, and the solution I came up with does exactly that. It is returning the values from that date, not the date itself.
What I gave you to test was to check to see if your dates really match or not.
I just need you to trust me, and to look into and try the things I mentioned in my last post, and let me know the results.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,982
Members
414,489
Latest member
Xlambda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top