formulas change when copied

[Sean]

I have a worksheet that is looking at another worksheet, but when I copy this
data into a third sheet it automatically changes the range for a example one
cell has the formula ='2009'!H9

I want to copy this into another worksheet, it is not going in the same
position on the second worksheet as the first worksheet.

Because I am pasting this a few rows further down the second worksheet I get
the following ='2009'!H15
Where as I need it to be the same.

I could change it manually but I have rows of this for the 52 weeks of the
year.

 

[Jarek Kujawa]

either change the original formula to ='2009'!$H$9
or cut it (instead of copying)

 

[Jacob Skaria]

Dear Sean

You can include dollar signs in cell references. The dollar sign does not
change the the formatting of the number, or the resulting value of the
formula. For example, both A1+B2 and $A$1 + $B$2 display exactly the same
output.

The dollar sign in a cell reference affects what happens when the formula is
copied into another cell. The part of the cell reference following the $
will not change.

Cell references with dollar signs in front of just the number, C$3 or in
front of just the letter, $C3, are called “mixed cell references.†Cell
references with two dollar signs, $C$3 are called an “absolute cell
references.â€

If you want to create a set of formulas that will always refer to either one
specific row: A$1, or one specific column: $B2, use a dollar sign in front of
the letter or row number

If this post helps click Yes

 

[Sean]

This doesn't help, I have rows of data for each year in seperate summary
worksheets.

each of these worksheets looks up totals in another working sheet for each
year.


I want to collate the information already gathered and put it side by side
in a total summary sheet, so that I can show the yearly pattern in chart
form.

I was going to copy the data for a row, and put it in the total summary and
do the same for each year, without a lot of fuss.

The original formula ='2009'!H9 (for just one cell) the H isn't thje
problem, but the original looks at for example row 9, 14, 19, 24 etc

When I copy it it add the number of rows because where I copy it is so many
row lower down in the new page compared to the old page for example
='2009'!H15 then rows 20, 25, 30 etc.

I can do it the long way but just wondered if anyone new a easy way to do it
as there are many rows, per year x 52 weeks

Sean...

 

[Jarek Kujawa]

why don't you send me an example of data in yr workbook and I'll try
to write a macro for you?

 

[Sean]

Worksheet: 2009 Summary
A B C D E
BA
WEEK 1 2 3 4USAGE ='2009'!H9 ='2009'!H13 ='2009'!H17 ='2009'!H21 >>>> ='2009'!H213


When I copy this in to Y-o-Y Summary on to row 5
as the row is 2 rows lower it add 2 on to the formula above
USAGE ='2009'!H11 ='2009'!H15 ='2009'!H19 ='2009'!H23 >>>> ='2009'!H215

in the 2009 worksheet there are daily usage and sales figures sepetated
daily in weekly blocks going down the worksheet
the 2009 summary is just pulling the weekly totals across.
I want to put 2007 / 2008 /2009 all together so I can produce a chart for
each category

Many thanks if you can help

 

[Jarek Kujawa]

with A5=2009, A6=2008, A7=2007 etc. try the following (in B5):

=INDIRECT($A$5&"!H"&(4*(COLUMN()-2)+9))
replace $A$5 with $A$6 and $A$7 respectively

then drag/copy right as needed

if this formula is NOT what you're looking for let me know