copying formula horizontally so column reference changes

samidodger

New Member
Joined
Sep 14, 2010
Messages
13
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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

New Member
Joined
Sep 14, 2010
Messages
13
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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)
 

Forum statistics

Threads
1,082,284
Messages
5,364,281
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top