Pulling info from Row by dragging down on Column

**itsandyslife** · 10-30-2014, 05:40 PM

Okay so I know this formula is not going to be so simple but here it goes:

The best way to explain this is with a picture because it would be way too confusing to explain the whole situation.

http://i.imgur.com/qztVH14.jpg?1

Now that you're looking at the picture, I would like a formula where the x1-3 and y1-3 from SHEET1 reflect its answers on SHEET2 where x1-3 and y1-3 are shown. I would like it so that if I fill in the formula where x1 is on SHEET2 and I drag it down to copy into where x2 and x3 are, it would be able to pull the answers from SHEET1. I purposely made spaces between rows on SHEET2, so please take that into account. Of course, with a little adjustment, y1 on SHEET2 would have a similar formula.

I have scoured the internet trying to figure out the formula to this. I'm not an Excel savvy person but I have tried TRANSPOSE, INDEX, INDIRECT, etc. in various ways, but I have had no luck. Please help!

Thanks!

**boopathiraja** · 10-31-2014, 12:12 AM

Hi, Welcome to the forum, pl post a sample in excel file...

, it will help for us to manipulate rather than retyping the file

**Loganeb** · 10-31-2014, 12:57 AM

Besides the sample workbook, I don't exactly understand what you're asking. You want a formula you can drag down over where you already entered the x and y text?

**FDibbins** · 10-31-2014, 01:42 AM

Hi, welcome to the forum

2 things...
1. please dont upload pics here, we cannot edit them, and most members dont want to retype your data
2. Please upload your workbook to the forum

OK, having said that, it would probably be more efficient to copy to alternate rows, instead of dragging across empty rows.

	A	B	C	D	E	F	G
1		Apple day 1	Orange Day 1	Apple day 2	Orange Day 2	Apple day 3	Orange Day 3
2	Meg	2	7	5	3	6	4
3	Ryan	5	5	2	4	5	5
4		7	12	7	7	11	9
5
6		Total Apple	Total Orange
7	Day 1	7	12
8
9	Day 2	7	7
10
11	Day 3	11	9

Based on your sample data....
B7=SUMIF($B$1:$G$1,MID(B$6,FIND(" ",B$6,1)+1,10)&" "&$A7,$B$4:$G$4)
copied across and down to alternate rows

IF you must just copy down over rows that you wont be using, change that to...
=IF(A7="","",SUMIF($B$1:$G$1,MID(B$6,FIND(" ",B$6,1)+1,10)&" "&$A7,$B$4:$G$4))

Note, that because your adctual headings will differ from your sample headings, that formula will probablyneed adjusting