Avoiding Names in Formulas

jdeitch

New Member
Joined
Feb 21, 2002
Messages
16
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top