Copy cell data from multiple sheets into a specific cells on a new sheet

**bhav123** · 11-05-2019, 01:07 PM

Hi all,

I currently have have 13 sheets with data from C6-R6 rows through 6-9 columns. The data is in the same cells throughout the 13 sheets and I need to copy the data from these cells into a new sheet. Each column represents a different subject, and I would like to make each subject their own sheet. If I can't do that, at least have all the same data into one master sheet.

The 13 sheets have all the same subjects in the same order and the same cell corresponding data. I also want to change the format of the cells into the new sheet. I want to change the data from two columns to one row for each subject.

For example, this is what the data looks like right now:

Sheet 1
C D
Subject 125 Subject 265
Subject #1 Subject #2
Type 1 1800 (C6) 1600 (D6)
1400 1200
Type 2 1700 1500
1800 1600

This what I want the data to look like in the new sheet:

Subject 125

Type 1 1800 1400
Type 2 1700 1800

And have each subject on a new sheet.

I've tried using the indirect function but wasn't really sure how to proceed with that.

Thanks y'all!!!

**DavidBowman** · 11-05-2019, 05:25 PM

Reading the question I am a little confused about what you are actually doing. Can you attach the workbook you are working with?

**JeteMc** · 11-07-2019, 07:23 PM

As to changing the format, perhaps this will be of some help.
The subject is typed into cell A1 on sheets 2 and 3.
Cells C2:D3 are populated using: =INDEX(Sheet1!$C$6:$D$9,SUM(COLUMNS($A$1:A$1),(ROWS($A$1:$A1)-1)*2),MATCH($A$1,Sheet1!$C$4:$D$4,0))
As to copying data from the 13 sheets to one sheet:
1. Consider the use of Power Query (Get & Transform) OR/AND
2. Upload* a samples of 2 or 3 of the 13 sheets along with manually produced sample of the "new sheet" so that we have something to go on.
*Instructions are in the fourth banner at the top of the page.
Let us know if you have any questions.