Fill formula in column with data taken from diff columns

**SergSlim** · 08-31-2012, 08:10 AM

Hello.

Probably a bit confusing title...

I have a file with 3 sheets: Sheet 1, Sheet 2, Sheet3
In Sheet 1 I have a formula in D3 cell:

Please Login or Register  to view this content.

In D4 cell I want H:H to be changed to I:I, in D5 to J:J, etc.
So that in D4 I want to have:

Please Login or Register  to view this content.

If I drag formula down it will copy H:H to D4, D5, etc.
How do I make it increase letter?

**Special-K** · 08-31-2012, 08:21 AM

Duplicate thread

http://www.excelforum.com/excel-form...-criteria.html

**Special-K** · 08-31-2012, 08:32 AM

Try this

=SUMIFS(INDIRECT("Sheet 2!"&CHAR(70+ROW())&":"&CHAR(70+ROW())),Sheet 2!$A:$A,$I$1,Sheet 2!$E:$E,Sheet3!$I$4)

You didnt say you wanted this bit changed
...'Sheet 2'!$A:$A,$I$1...

so I've left it the same

**daddylonglegs** · 08-31-2012, 09:23 AM

Try this formula in D3 copied down

=SUMIFS(INDEX('Sheet 2'!H:IV,0,ROWS(D$3:D3)),'Sheet 2'!$A:$A,$I$1,'Sheet 2'!$E:$E,Sheet3!$I$4)

**SergSlim** · 08-31-2012, 09:24 AM

Thanks for helping.
This is a bit hard for me to understand what it does. Actually I guess that it takes character N70 which is probably the letter I want, and then it will increase it's value. Not sure if it will work for AA, AB...

I did a workaround
On a separate sheet I entered desired formula, filled it to the right, so it changed letters as I want, and in the table that this formula was originally I used: {=TRANSPOSE(Sheet3!O2:AR2)}

UPD:

Originally Posted by daddylonglegs

Try this formula in D3 copied down

=SUMIFS(INDEX('Sheet 2'!H:IV,0,ROWS(D$3:D3)),'Sheet 2'!$A:$A,$I$1,'Sheet 2'!$E:$E,Sheet3!$I$4)

Well, this one works. Now I have to figure out how...
H:IV - Why IV?

**daddylonglegs** · 08-31-2012, 09:44 AM

Originally Posted by SergSlim

Why IV?

Just habit.....

Excel 2003 and earlier versions have 256 columns so the last column is IV. It only really needs to go up to the last column you might need for your requirements, the equivalent of IV in Excel 2010 is XFD

**SergSlim** · 08-31-2012, 10:15 AM

After reading help for the past half an hour or more, I guess it works this way (correct me if I'm wrong):
ROWS(D$3:D3) - results in number 1. ROWS(D$3:D4) will result in number 2, etc. This way we define which column from H:IV range to pick. 1 will pick H, 2 will pick I, etc.
0 in Index defines that we take an array of all rows in picked column.

Sumifs will sum all rows from taken column, if conditions are met.

Thanks a lot for this solution. It's exactly what I was looking for.

**daddylonglegs** · 08-31-2012, 10:32 AM

Yes that's right, ROWS(D$3:D3) increments by 1 as you drag down (and unlike some other methods it won't give you incorrect results if you add or delete a row at the top of your worksheet, for instance).