I have a spreadsheet with "Sales" for Year 1 in B2, Sales for Year 2 in C2, etc. I've also defined a Name for $B$2 (as an absolute reference) because I need to go to it in a Visual Basic Procedure (B2 is named 'SalesY1').
The problem is that the users will put an amount in B2, say 500, and want to increment that amount in subsequent years by a percentage per year. If, in Cell C2, they enter "1.2*" and then click on cell B2 (to indicate that the formula is 1.2 times the value in B2), the formula in C2=1.2*SalesY1. They would like to just copy the equation in C2 to D2 so that D2 =1.2*C2, but because it puts SalesY1 in the formula in C2, and SalesY1 is an absolute reference, the formulas for the subsequent years are all 1.2*SalesY1 (or 600 for each of the subsequent years instead of 600, 720, etc.).
Is there a way to tell Excel to use the cell address rather than the name of the cell?
The only other way I've thought to do it is have the named cell be A2 (maybe named "Sales") and then when I need to go to sales for year 1 in the procedure, I would go to "Sales" and then offset by 1 column. I'll do that unless someone has a better idea.
Thanks in advance for your help.
Jay
The problem is that the users will put an amount in B2, say 500, and want to increment that amount in subsequent years by a percentage per year. If, in Cell C2, they enter "1.2*" and then click on cell B2 (to indicate that the formula is 1.2 times the value in B2), the formula in C2=1.2*SalesY1. They would like to just copy the equation in C2 to D2 so that D2 =1.2*C2, but because it puts SalesY1 in the formula in C2, and SalesY1 is an absolute reference, the formulas for the subsequent years are all 1.2*SalesY1 (or 600 for each of the subsequent years instead of 600, 720, etc.).
Is there a way to tell Excel to use the cell address rather than the name of the cell?
The only other way I've thought to do it is have the named cell be A2 (maybe named "Sales") and then when I need to go to sales for year 1 in the procedure, I would go to "Sales" and then offset by 1 column. I'll do that unless someone has a better idea.
Thanks in advance for your help.
Jay