|
formulas change when copied
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. |
formulas change when copied
either change the original formula to ='2009'!$H$9
or cut it (instead of copying) On 22 Mar, 09:22, Sean wrote: 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. |
formulas change when copied
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 -------------- Jacob Skaria "Sean" wrote: 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. |
formulas change when copied
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... "Jacob Skaria" wrote: 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 -------------- Jacob Skaria "Sean" wrote: 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. |
formulas change when copied
why don't you send me an example of data in yr workbook and I'll try
to write a macro for you? On 22 Mar, 16:18, Sean wrote: 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... "Jacob Skaria" wrote: 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 -------------- Jacob Skaria "Sean" wrote: 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.- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
formulas change when copied
Worksheet: 2009 Summary
A B C D E BA WEEK 1 2 3 4 52 USAGE ='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" wrote: why don't you send me an example of data in yr workbook and I'll try to write a macro for you? On 22 Mar, 16:18, Sean wrote: 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... "Jacob Skaria" wrote: 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 -------------- Jacob Skaria "Sean" wrote: 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.- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
formulas change when copied
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 On 22 Mar, 20:30, Sean wrote: Worksheet: *2009 Summary A * * * * * B * * * * * * * * * *C * * * * * * * * D * * * * * * * * E * * * * * * * * * * * * * * * *BA WEEK * * 1 * * * * * * * * * *2 * * * * * * * * 3 * * * * * * * * 4 * * * * * * * * * * * * 52 USAGE * ='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" wrote: why don't you send me an example of data in yr workbook and I'll try to write a macro for you? On 22 Mar, 16:18, Sean wrote: 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... "Jacob Skaria" wrote: 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 -------------- Jacob Skaria "Sean" wrote: 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.- Ukryj cytowany tekst - - Pokaż cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
| All times are GMT +1. The time now is 01:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com