copying formula horizontally so column reference changes

[samidodger]

HI there, I actuall y have two problems here 1) in a cell A1 ive got this reference ='A4115'!C16 (where A4115 is a worksheet name), when a copy and paste this into cells A2 and A3 and so on the column C in the formula changes to D, and then E, how do I change this so that the number changes instead, to A4115!C17, and the A4115!C18, when i copy to cells A2 and A3 and so on? Like wise I have the same problem copying vertically as well, this time the number changes, when I want the column to changes. Therefore, from A2 to A3, it should say A4115!D17, and then A4115!E17.
Also for something unrelated to the previous, I have in one cell A4115!C17, which is the result of a =A5&"!"A6, and in A3 I have the '= sign. Thebn in cell A2 I have this =CONCATENATE(A3,A4), which gives =A4115!C17, but this is a text, how do I convert this to a formula so Excel recognise the = sign and executes the reference?

I know this is alot,

But I do appreciate this,

regards amish

 

[Boller]

Re question 1, put in A1 :-

<TABLE style="WIDTH: 249pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=332 border=0><COLGROUP><COL style="WIDTH: 249pt; mso-width-source: userset; mso-width-alt: 12141" width=332><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 249pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=332 height=17>=OFFSET('A4115'!$C$16,ROW()-1,COLUMN()-1)

</TD></TR></TBODY></TABLE>

 

[Joe4]

1) in a cell A1 ive got this reference ='A4115'!C16 (where A4115 is a worksheet name), when a copy and paste this into cells A2 and A3 and so on the column C in the formula changes to D, and then E, how do I change this so that the number changes instead, to A4115!C17, and the A4115!C18, when i copy to cells A2 and A3 and so on?

Are you sure about that?

If I enter the formula: ='A4115'!C16 in cell A1, and copy to cells A2 and A3, I get the formulas: ='A4115'!C17 and ='A4115'!C18, respectively, like you want.

Are you sure you aren't trying to copy to B1 and C1 instead? Then I would expect the results you mentioned.

If you want the value from 'A4115'!C17 to appear in B1 and the value from 'A4115'!C18 to appear in C1, then you can use the INDIRECT function, in conjunction with the ROW function to get what you want.

So enter this function in cells A1:C1:
=INDIRECT("'A4115'!C" & 15+COLUMN())

 

[Boller]

Re question 2, in A2 put =INDIRECT(A4) <TABLE style="WIDTH: 249pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=332 border=0><COLGROUP><COL style="WIDTH: 249pt; mso-width-source: userset; mso-width-alt: 12141" width=332><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 id=td_post_2448972 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 249pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=332 height=17></TD></TR></TBODY></TABLE>

 

[Boller]

Re question 1, put in A1 :-

<TABLE style="WIDTH: 249pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=332 border=0><COLGROUP><COL style="WIDTH: 249pt; mso-width-source: userset; mso-width-alt: 12141" width=332><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 249pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=332 height=17>=OFFSET('A4115'!$C$16,ROW()-1,COLUMN()-1)


</TD></TR></TBODY></TABLE>

Sorry, should be :-

<TABLE style="WIDTH: 249pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=332 border=0><COLGROUP><COL style="WIDTH: 249pt; mso-width-source: userset; mso-width-alt: 12141" width=332><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 249pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=332 height=17>=OFFSET('A4115'!$C$16,COLUMN()-1,ROW()-1)

</TD></TR></TBODY></TABLE>

 

[samidodger]

Hi there, major thanks for this it works like a charm. Just wanted to ask though how does the formula work, Ive been trying to suss it out for the last few weeks spending my lunch break trying to understand the formula. I cant seem to find an answer to this question - that how come it only works in cell A1 and not any other cell?

I dont mind if you put this to the back of the queue as its not a problem question, more of an advice question.

merci beacoup

 

[Boller]

I'll assume that you understand how OFFSET works, so :

COLUMN()-1 evaluates to 0 since the Column number is 1 (column A) which means that the row offset is 0

ROW()-1 evaluates to the row number containing the formula minus 1 since the formala starts in row 1. This means that the column offset is 0 for the A1 formula, 1 for the A2 formula, etc.
If the formula needs to start in a row other than row one, let's say in A4, then the column offset would be ROW()-4 and the formula would need to be :-

=OFFSET('A4115'!$C$16,COLUMN()-1,ROW()-4)