Avoiding Names in Formulas

[jdeitch]

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

[MT_Shanachie]

On 2002-05-03 03:51, jdeitch wrote:
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

Jay,
I don't have a direct answer for you, but I'm wondering if you might be able to solve your problem by changing your VBA code. Can you explain why you need to have the cell named in order to go to it in the procedure? What are you doing to B2 in the procedure once you go there?

[jdeitch]

Basically the VB code runs through a bunch of scenarios of cost/price/volume (% variance from plan, etc.). To do that it copies the original data to another place, does the sensitivity analysis, then puts the original info back where it found it. To put it back, it copies the data from the temp location then selects the starting cell (which would be like Sales Y1) to paste it.

As I mentioned earlier, I could make the named cell A2 instead of B2 and use "Offset" when I want to return the data. I just thought that I'd see if anyone had a better idea.

Thanks for your help.

Jay

[MT_Shanachie]

On 2002-05-03 10:08, jdeitch wrote:
Basically the VB code runs through a bunch of scenarios of cost/price/volume (% variance from plan, etc.). To do that it copies the original data to another place, does the sensitivity analysis, then puts the original info back where it found it. To put it back, it copies the data from the temp location then selects the starting cell (which would be like Sales Y1) to paste it.

As I mentioned earlier, I could make the named cell A2 instead of B2 and use "Offset" when I want to return the data. I just thought that I'd see if anyone had a better idea.

Thanks for your help.

Jay

Jay,
Any reason why you can't just select the cell without naming it? Assuming you want to paste to Cell B2 on Sheet1, you could use something like:

Sheets(1).Activate
Range("B2").Select
ActiveSheet.Paste

[jdeitch]

I prefer to use names for VB so it doesn't break when a row or column is added. This workbook
has several dozen named ranges, thousands of cells and about 30 printed pages of VB. With all that going on, adding a row or column without named ranges would be a real chore.

Thanks,
Jay

[s-o-s]

On 2002-05-03 03:51, jdeitch wrote:
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

Can you change the VB to pickup a value from a specific cell.

Eg Sheet1 cell B2 can be called from any active sheet in a macro by..

Sheets("Sheet1").Range("B2").Value

Instead of picking up a Named Range.

Would this be possible?