alternative column display

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Oct 4, 2014

• #2

Do your columns have headings, and are those headings the same for the columns you want?

And what exactly are you trying to do?

 

Upvote 0

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Oct 4, 2014

• #3

Maybe you can adapt this for your needs?

	A​	B​	C​	D​	E​
1​	10​		20​		30​
2​	10​	20​	30​

A2=INDIRECT(CHAR(96+COLUMN()*2-1)&ROW(A1))
copied across

 

Upvote 0

P

pmncivil

New Member

Joined

Oct 4, 2014

Messages

24

• Oct 4, 2014

• #4

FDibbins said:

Do your columns have headings, and are those headings the same for the columns you want?

And what exactly are you trying to do?

Click to expand...

tnx
no sir they have no heading. they are some vale (the average of other values) in a table which i need to show them in the other excel file. those number which i need are in columns like a11,c11,e11,...
but i want to use them in for example a6,b6,c6 in other excell file

 

Upvote 0

P

pmncivil

New Member

Joined

Oct 4, 2014

Messages

24

• Oct 4, 2014

• #5

FDibbins said:

Maybe you can adapt this for your needs?

	A​	B​	C​	D​	E​
1​	10​		20​		30​
2​	10​	20​	30​

<tbody>
</tbody>

A2=INDIRECT(CHAR(96+COLUMN()*2-1)&ROW(A1))
copied across

Click to expand...

thank u sir i think it works.
thank you very much

 

Upvote 0

P

pmncivil

New Member

Joined

Oct 4, 2014

Messages

24

• Oct 4, 2014

• #6

sorry i have another question can u help me?
how can i use this formula in other excell file
should i use this:
='[file1.xlsx]sheet1'!INDIRECT(CHAR(96+COLUMN()*2-1)&ROW(A14))


it doesn't work what should i do?

 

Upvote 0

P

pmncivil

New Member

Joined

Oct 4, 2014

Messages

24

• Oct 4, 2014

• #7

can anyone help me ?
should i use this:
='[file1.xlsx]sheet1'!INDIRECT(CHAR(96+COLUMN()*2-1)&ROW(A14))


it doesn't work what should i do?
how should i use the other file alternative column ?
my problem doen't fix yet plz help me?!!!!!!

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Oct 4, 2014

• #8

pmncivil said:

thank u sir i think it works.
thank you very much

Click to expand...

I would suggest caution with using the suggested solution.

1. INDIRECT is a volatile function so can cause your sheet performance to degrade if used a lot.

2. It only works as far as column Z. Look what happens when the data goes beyond that.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	10		20		30		40		50		60		70		80		90		100		110		120		130		140
2	10	20	30	40	50	60	70	80	90	100	110	120	130	#REF!
List

3. If you subsequently decided to insert one (or more) new columns to the left, say for headings, look what happens to the results.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Original	10		20		30		40		50		60		70		80		90		100		110		120		130		140
2	New	0	0	0	0	0	0	0	0	0	0	0	0	#REF!	#REF!
List

Here in a non-volatile & much more robust alternative.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	10		20		30		40		50		60		70		80		90		100		110		120		130		140
2	10	20	30	40	50	60	70	80	90	100	110	120	130	140
List

 

Upvote 0

FDibbins

Well-known Member

Joined

Feb 16, 2013

Messages

6,723

• Oct 5, 2014

• #9

Pete, thanks for the assist on this, you are correct on both counts - the volatility and only working as far as Z. Also to that, I can add that INDIRECT only works on open workbooks (in response to post #7)

 

Upvote 0

P

pmncivil

New Member

Joined

Oct 4, 2014

Messages

24

• Oct 5, 2014

• #10

tnx peter it does worked

 

Upvote 0