Replace column letter with letter in other cell

[Okoth]

Hi,

I have 5 columns, 5 rows like this

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]a[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]3[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]e[/TD]
[TD]5[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'd like to use SUM.IF to get the value from the column that is determined by the value in cell E1. The value in E1 is the result of a formula.

I'm kind of looking for a formula like this =SOM.ALS(A1:A5,A1,E1&"1":E1&"5") but of course that doesn't work.

How can I do this?
Thanks

 

[jim may]

Will this work for you?

Excel Workbook
	A	B	C	D	E	F	G	H
1	a	1.00	11.00	a	c		13	<<<
2	b	2.00	12.00
3	c	3.00	13.00
4	d	4.00	14.00
5	e	5.00	15.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G1	=SUMIF(A1:A5,INDIRECT("E1"),C1:C5)

 

[jim may]

Woops, perhaps you mean to have it this way/......

Excel Workbook
	A	B	C	D	E	F	G	H
1	a	1.00	11.00	a	c		13	<<<
2	b	2.00	12.00
3	c	3.00	13.00
4	d	4.00	14.00
5	e	5.00	15.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G1	=SUMIF(A1:A5,INDIRECT("E1"),INDIRECT(E1&"1:"&E1&5))

 

[Okoth]

Thank you for the reply, Jim. It works indeed, if all data is on one page. I thought I would manage when I had this formula, but unfortunately not.

The actual situation is that there are 2 excel files. One file gets data from the other.

When I do just a simple SUMIF formula, I get the result. The formula is this

=SUMIF('[estimate stock june1.xls]25 june'!$B:$B,A6,'[estimate stock june1.xls]25 june'!$W:$W)

If I could change the $W:$W in the formula with the INDIRECT from your example, I would be good.

I would like to copy with SUMIF the values of column W from estimate stock june1.xls into a second sheet where I do some calculations.

The right column, in this case W, is calculated on the same sheet and tab where I want the date copied to. The problem is that when the date changes, the column in the estimate stock june1.xls sheet will be a different one. Every day, there will be a new calculation based on the current date.

I hope you'll understand what I mean.

Thanks for your help

Jeroen

 

[mikerickson]

How about
=SUMIF('[estimate stock june1.xls]25 june'!$B:$B, A6, INDEX('[estimate stock june1.xls]25 june'!$A:$Z,CODE(UPPER(E1))-64 ,0))
Which only works for A-Z.

replace

INDEX('[estimate stock june1.xls]25 june'!$A:$Z, MATCH(V11, LEFT(ADDRESS(1,COLUMN(1:1),4),1+(26<COLUMN(1:1))),0) , 0)

 

[mikerickson]

As I should have said,
Replace '[estimate stock june1.xls]25 june'!$W:$W

in that formula with

INDEX('[estimate stock june1.xls]25 june'!$1:$65536, MATCH(E1, LEFT(ADDRESS(1,COLUMN(1:1),4),1+(26<COLUMN(1:1))),0), 0)

and it will look to the column whose letter is in E1

 

[bigbankt00]

what types of this cell this is ! would you like to tell me somethings about this?
brand promotion delhi

 

[Okoth]

Hi Mike,

I've tried it but no luck yet. This is the formula I have so far:

=SUMIF('[estimasi stok juni1.xls]25 juni'!$B:$B,A6,INDEX('[estimasi stok juni1.xls]25 juni'!$1:$65536; MATCH(E1, LEFT(ADDRESS(1,COLUMN(1:1),4),1+(26)))))

but I get a VALUE? error.

Also when I look at the row colors, the part COLUMN(1:1) is colored in the calculation sheet and not in the data sheet. Shouldn't it be colored in the data sheet?
Could you check the ))) as well to see if they are at the right spot? I think they are, though.

Thanks for the help.

 

[mikerickson]

The formula got cut off by this editor.
Put this in [Shifts]Sheet1!C6

=SUMIF('[estimasi stok juni1.xls]25 juni'!$B:$B, A6, INDEX('[estimasi stok juni1.xls]25 juni'!$1:$1048576, 0,MATCH(C1, LEFT(ADDRESS(1,COLUMN(6:6),4),1+(26 < COLUMN(6:6)) ),0 ) ) )

It needs to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Okoth]

I really can't figure out why I get an error message with this formula, but still get one.

The error refers to $1 in the formula. What I also notice is that the C1 in the formula is not highlighted.

I entered it with Ctrl-Shift-Enter

Thanks for putting so much time in it Mike. I thought the answer would be something simpler than this.