referencing current column in formula

[gazingdown]

I need to reference the current column. How do I do this? e.g I want to intersect a row with a value in the current column. e.g. the formula in cell C2 = the intersect of row 1 and column C. In cell D2 it'll need the result of row 1 intersected with column D and so forth.

Can I do this without changing to R1C1 notation?

 

[eliW]

Hi,

I cant understand what you really ment: I'm sure it is far more simple but I'll give this a start in a complicated way hoping that this is the direction you need.

Eli

Book1
	A	B	C	D	E	F
1	a	c	v	b	n
2			v	b
3
4
Sheet1

 

[gazingdown]

Thanks.

Problem is, I'm using a dates in row a and the formulas are array formulas. so Month(Row_a) is fine if not in array formula but in my case I need it to work somehow. Month(Row_A C:C) is OK but then I'm hardwiring column names in which I don't want to do. I need to somehow get the C:C into something generic.

 

[Aladin Akyurek]

Thanks.

Problem is, I'm using a dates in row a and the formulas are array formulas. so Month(Row_a) is fine if not in array formula but in my case I need it to work somehow. Month(Row_A C:C) is OK but then I'm hardwiring column names in which I don't want to do. I need to somehow get the C:C into something generic.

What is your current formula?

 

[gazingdown]

{=SUBSTITUTE(MCONCAT(IF((MONTH('AEX Sales Monitor 2004.xls'!On_sale_date)=MONTH($C$1))*(YEAR('AEX Sales Monitor 2004.xls'!On_sale_date)=YEAR($C$1)), ", " & TEXT('AEX Sales Monitor 2004.xls'!Cover_date, "dd-mmm-yy"),"")),", ","",1)}

Basically I want the month($C$1) and year($C$1) to be generic so it looks at row one to get the month/date for that column. Then I can use the same formula across as many columns as I want rather then having it $D$1, $E$1, $F$1 etc.

Row 1 is named Title_date so Month(Title_date) and Year(Title_date) work fine IF the array wasn't an array one (which it needs to be)

Month(Title_date C:C) works but then I'm no further away from where I was before as I'd need D:D, E:E etc. for the subsequent columns.

I believe what I need is for the formula to automatically get the C:C bit hence the need to get the 'current column'.

I hope I've explained this well

 

[Aladin Akyurek]

{=SUBSTITUTE(MCONCAT(IF((MONTH('AEX Sales Monitor 2004.xls'!On_sale_date)=MONTH($C$1))*(YEAR('AEX Sales Monitor 2004.xls'!On_sale_date)=YEAR($C$1)), ", " & TEXT('AEX Sales Monitor 2004.xls'!Cover_date, "dd-mmm-yy"),"")),", ","",1)}

Basically I want the month($C$1) and year($C$1) to be generic so it looks at row one to get the month/date for that column. Then I can use the same formula across as many columns as I want rather then having it $D$1, $E$1, $F$1 etc.

Row 1 is named Title_date so Month(Title_date) and Year(Title_date) work fine IF the array wasn't an array one (which it needs to be)

Month(Title_date C:C) works but then I'm no further away from where I was before as I'd need D:D, E:E etc. for the subsequent columns.

I believe what I need is for the formula to automatically get the C:C bit hence the need to get the 'current column'.

I hope I've explained this well

Not quite sure I've got the issue -- Is it not just C$1 instead of $C$1? If not, clarify a tad bit more?

 

[gazingdown]

Cell C3 =
{=SUBSTITUTE(MCONCAT(IF((MONTH('AEX Sales Monitor 2004.xls'!On_sale_date)=MONTH(C$1))*(YEAR('AEX Sales Monitor 2004.xls'!On_sale_date)=YEAR(C$1)), ", " & TEXT('AEX Sales Monitor 2004.xls'!Cover_date, "dd-mmm-yy"),"")),", ","",1)}

Cell D3 =
=SUBSTITUTE(MCONCAT(IF((MONTH('AEX Sales Monitor 2004.xls'!On_sale_date)=MONTH(D$1))*(YEAR('AEX Sales Monitor 2004.xls'!On_sale_date)=YEAR(D$1)), ", " & TEXT('AEX Sales Monitor 2004.xls'!Cover_date, "dd-mmm-yy"),"")),", ","",1)

Both these work fine, and I can replicate over the sheet. However, it would be preferable to replace the C$1, D$1 etc. with something a bit more 'intelligent' for when and if more columns are added or when I 'move' formulas to different parts of the workbook etc. Bascially, I'm trying to make the formulas as portable as possible.

 

[Aladin Akyurek]

Cell C3 =
{=SUBSTITUTE(MCONCAT(IF((MONTH('AEX Sales Monitor 2004.xls'!On_sale_date)=MONTH(C$1))*(YEAR('AEX Sales Monitor 2004.xls'!On_sale_date)=YEAR(C$1)), ", " & TEXT('AEX Sales Monitor 2004.xls'!Cover_date, "dd-mmm-yy"),"")),", ","",1)}

Cell D3 =
=SUBSTITUTE(MCONCAT(IF((MONTH('AEX Sales Monitor 2004.xls'!On_sale_date)=MONTH(D$1))*(YEAR('AEX Sales Monitor 2004.xls'!On_sale_date)=YEAR(D$1)), ", " & TEXT('AEX Sales Monitor 2004.xls'!Cover_date, "dd-mmm-yy"),"")),", ","",1)

Both these work fine, and I can replicate over the sheet. However, it would be preferable to replace the C$1, D$1 etc. with something a bit more 'intelligent' for when and if more columns are added or when I 'move' formulas to different parts of the workbook etc. Bascially, I'm trying to make the formulas as portable as possible.

Replace C$1 with

INDEX(1:1,COLUMN())

 

[gazingdown]

Thanks, think it works.