+ Reply to Thread
Results 1 to 5 of 5

Name assigned in workbook refers to range problem!!!

  1. #1
    Registered User
    Join Date
    03-27-2006
    Posts
    70

    Name assigned in workbook refers to range problem!!!

    This will be "fun" to describe so it makes sense...

    At the spreadsheet level (no VBA on this part) I have used Insert: Name: Create to create names for several rows and columns of a table. For instance, row names dog, cat, skunk; column names smellsgood, smells, smellsbad.

    Then I used Insert: Name: Define and created two new names, let's call them condition_1 and condition_2.

    Condition_1 and Condition_2 have multiple embedded IF statements, Condition_1 to determine the Row name and Condition_2 for the Column name.

    Now here is my problem: Using the intersection operator (a space), if I assign a cell:
    =dog smells
    I get the item that is at the intersection of the range named dog and the range named smells without any problem, but if Condition_1 and Condition_2 both properly evaluate out to dog and smells respectively and I try:
    =condition_1 condition_2
    I get a #VALUE! error.

    I suspect I might have to tell Excel that these named variables are for ranges, but as they were created using Insert: Name: Define (i.e.: not in VBA), I do not know how (If that indeed is the problem), for I can't assign either a specific range as they change depending on the multiple embeded IF statements assigned to each condition, which go something like:
    =IF(<condition exists>, "dog", IF (<another condition>,"Cat", IF (<another condition>,"Skunk","")))

    Well, because I know what I am trying to do, all that makes sense. I REALLY hope it makes sense to you folks, as I gotta find out how to do this!

    Thank-you,
    -brucemc

    p.s. Where's that head-banging smiley...?
    Last edited by brucemc; 06-25-2006 at 02:51 PM. Reason: Correct the title.

  2. #2
    Toppers
    Guest

    RE: Name assigned in workbook refers to range problem!!!

    Remove the quotes round you named ranges i.e "dog" should be dog


    =IF(<condition exists>, dog, IF (<another condition>,Cat, IF
    <another condition>,Skunk,"")))

    "brucemc" wrote:

    >
    > This will be "fun" to describe so it makes sense...
    >
    > At the spreadsheet level (no VBA on this part) I have used Insert:
    > Name: Create to create names for several rows and columns of a table.
    > For instance, row names dog, cat, skunk; column names smellsgood,
    > smells, smellsbad.
    >
    > Then I used Insert: Name: Define and created two new names, let's call
    > them condition_1 and condition_2.
    >
    > Condition_1 and Condition_2 have multiple embedded IF statements,
    > Condition_1 to determine the Row name and Condition_2 for the Column
    > name.
    >
    > Now here is my problem: Using the intersection operator (a space), if I
    > assign a cell:
    > =dog smells
    > I get the item that is at the intersection of the range named dog and
    > the range named smells without any problem, but if Condition_1 and
    > Condition_2 both properly evaluate out to dog and smells respectively
    > and I try:
    > =condition_1 condition_2
    > I get a name error (I think).
    >
    > I suspect I might have to tell Excel that these named variables are for
    > ranges, but as they were created using Insert: Name: Define (i.e.: not
    > in VBA), I do not know how (If that indeed is the problem), for I can't
    > assign either a specific range as they change depending on the multiple
    > embeded IF statements assigned to each condition, which go something
    > like:
    > =IF(<condition exists>, "dog", IF (<another condition>,"Cat", IF
    > (<another condition>,"Skunk","")))
    >
    > Well, because I know what I am trying to do, all that makes sense. I
    > REALLY hope it makes sense to you folks, as I gotta find out how to do
    > this!
    >
    > Thank-you,
    > -brucemc
    >
    > p.s. Where's that head-banging smiley...?
    >
    >
    > --
    > brucemc
    > ------------------------------------------------------------------------
    > brucemc's Profile: http://www.excelforum.com/member.php...o&userid=32871
    > View this thread: http://www.excelforum.com/showthread...hreadid=555376
    >
    >


  3. #3
    Registered User
    Join Date
    03-27-2006
    Posts
    70
    Toppers: Thanks, "big-time". I was beating my head against every other expression and never thought to examine the root of this; I was mentally stuck in the mode of trying to change the string variable back into a range rather than never having set it up as a string in the first place. I think it is time to cut the lawn, as my head seems to be stuffed with too much old grease. I appreciate your help immensly.

  4. #4
    Toppers
    Guest

    RE: Name assigned in workbook refers to range problem!!!

    Enjoy mowing the lawn .... and thanks for the feedback.

    "Toppers" wrote:

    > Remove the quotes round you named ranges i.e "dog" should be dog
    >
    >
    > =IF(<condition exists>, dog, IF (<another condition>,Cat, IF
    > <another condition>,Skunk,"")))
    >
    > "brucemc" wrote:
    >
    > >
    > > This will be "fun" to describe so it makes sense...
    > >
    > > At the spreadsheet level (no VBA on this part) I have used Insert:
    > > Name: Create to create names for several rows and columns of a table.
    > > For instance, row names dog, cat, skunk; column names smellsgood,
    > > smells, smellsbad.
    > >
    > > Then I used Insert: Name: Define and created two new names, let's call
    > > them condition_1 and condition_2.
    > >
    > > Condition_1 and Condition_2 have multiple embedded IF statements,
    > > Condition_1 to determine the Row name and Condition_2 for the Column
    > > name.
    > >
    > > Now here is my problem: Using the intersection operator (a space), if I
    > > assign a cell:
    > > =dog smells
    > > I get the item that is at the intersection of the range named dog and
    > > the range named smells without any problem, but if Condition_1 and
    > > Condition_2 both properly evaluate out to dog and smells respectively
    > > and I try:
    > > =condition_1 condition_2
    > > I get a name error (I think).
    > >
    > > I suspect I might have to tell Excel that these named variables are for
    > > ranges, but as they were created using Insert: Name: Define (i.e.: not
    > > in VBA), I do not know how (If that indeed is the problem), for I can't
    > > assign either a specific range as they change depending on the multiple
    > > embeded IF statements assigned to each condition, which go something
    > > like:
    > > =IF(<condition exists>, "dog", IF (<another condition>,"Cat", IF
    > > (<another condition>,"Skunk","")))
    > >
    > > Well, because I know what I am trying to do, all that makes sense. I
    > > REALLY hope it makes sense to you folks, as I gotta find out how to do
    > > this!
    > >
    > > Thank-you,
    > > -brucemc
    > >
    > > p.s. Where's that head-banging smiley...?
    > >
    > >
    > > --
    > > brucemc
    > > ------------------------------------------------------------------------
    > > brucemc's Profile: http://www.excelforum.com/member.php...o&userid=32871
    > > View this thread: http://www.excelforum.com/showthread...hreadid=555376
    > >
    > >


  5. #5
    Registered User
    Join Date
    03-27-2006
    Posts
    70
    If you happen to drop back by and have a moment, perhaps you might be able to tell me if & how to make this thing three dimensional, along the same lines of a three dimensional array - the same two conditions will be used to look up a second value whose table "coordinates" are a duplicate of the first. If this can be done, how is the next "layer" table set up, and is it addressed

    =condition_1 condition_2 condition_3

    ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1