Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Avoiding Names in Formulas

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Bloomington, MN
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Bloomington, MN
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Bloomington, MN
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •