The reason why I have constructed the data list as I have is that I want it
to be easy for people to overview it and to continue to update it in a
structured way. When I read abut the lookup formula I saw that I could do the
same thing as I'm doing now but at my first glans it looked like it would
take equal amount of space. This is why I decided to stick with the first
code since this were allready written.

Maybe I have missed something though? It would be perfect to have a formula
that looked upp an array instead, e.g. B3:Z99999 and then gave back the
column number where it finds a match.

Pontus

"RagDyer" wrote:

The standard approach, and easiest solution to alleviate nesting problems is
to use one of the Lookup functions, as I suggested at the outset.

You're sacrificing functionality in the way you've constructed your datalist
of associations.

That datalist should/could be hidden, where it's appearance contributes
nothing to efficiency and should not be a factor in it's design.

Is there an actual reason behind your configuration choice?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Pontus" wrote in message
...
Hey Ragdyer,

Thank you very much for all your help, I got your trick with the relative
formula to work. This was better then the other one since the code became
shorter and easier to overview.

I got in to one problem though and that is that I use to many levels of
nesting then whats allowed in my file formate. Do you know any way so that
one wount be limited by this restriction. Or could one write the code in
macro or something similar?

Pontus

"Ragdyer" wrote:


<<<"2. I gave the cell a name and set it to refer to the cell to the
right
"F1""

The above is *not* what I directed you to do!

We're *not* naming cells!

I could retype my instructions here, but there wouldn't be a single
change
from what I said the first time.

Go back and re-read my instructions ... and follow them *exactly*!

Remember, I said this was a *relative* named formula.
The formula is
=F1 (*NO* $ signs) when the cell in focus at the time of invoking the
<Insert <Name <Define is E1.

The name of the *formula* is
rt

Try it again ... or just keep using that Indirect formula ... it's your
WB,
so it's your choice.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

You didn't
"Pontus" wrote in message
...
Hey,

I couldn't get your option with setting a reference to work. I did as
you
said:
1. Clicked on a cell, e.g. E1 and then <Define Name
2. I gave the cell a name and set it to refer to the cell to the right
"F1"

What seemed to happen when I did this was basically that I gave a name
to
cell F1 so every time I refered to this name (no matter which cell I
were
in)
I got back the value in cell F1. Did I miss something?

It would have been nice to be able to use your code since it gets much
shorter than the other one which I got to function, thanks for that btw
=)
I
also got the referens to another worksheet to function by typing
Sheet1!
infront of everywhere in the code I needed to refere to sheet1, hehe.
My
code
now looks like this:



=IF(INDIRECT(ADDRESS(ROW();COLUMN()+1))=0;"";IF(IS NUMBER(MATCH(INDIRECT(ADDR
ESS(ROW();COLUMN()+1));Sheet1!$B:$B;0));Sheet1!$B$ 2;IF(ISNUMBER(MATCH(INDIRE
CT(ADDRESS(ROW();COLUMN()+1));Sheet1!$C:$C;0));She et1!$C$2;IF(ISNUMBER(MATCH
(INDIRECT(ADDRESS(ROW();COLUMN()+1));Sheet1!$D:$D; 0));Sheet1!$D$2;INDIRECT(A
DDRESS(ROW();COLUMN()+1))))))

As you can see its pretty damn long and it will get much longer since
I'm
gonna add many mor columns that the code are supposed to try to match
with.

Thanks for all your help so far =)
Pontus



"Ragdyer" wrote:

Since you insist on using your original procedure, after checking
your
example, I see you have a couple of ways to go.

My original thought of using a "relative named formula" would work
for
you.

BTW, my delimiters are commas, so you'll have to replace them.

For example, say you click in E1.
Then, from the Menu Bar:
<Insert <Name <Define

In the "Names In Workbook" box, key in a short name, say
rt
to signify "right".

Then, in the "Refers To" box, change whatever's there to:
=F1
(Which is the cell to the right of the cell you selected before you
started).
Then <OK

Now, if you enter
=rt
in *any* cell, you'll display the contents of the cell to the
immediate
right.

SO, your formula might look something like this:


=IF(rt=0,"",IF(ISNUMBER(MATCH(rt,B:B,0)),$B$2,IF(I SNUMBER(MATCH(rt,C:C,0)),$
C$2,IF(ISNUMBER(MATCH(rt,D:D,0)),$D$2,rt))))

After you define the named formula, any new sheet you create by
copying
the
original sheet will automatically contain that named formula.


b&s's Address formula can be made to work for you *also*.
Just wrap it in Indirect to change it from a Text return to an XL
"legal"
cell reference.

=Indirect(Address(Row();Column()+1))


=IF(INDIRECT(ADDRESS(ROW(),COLUMN()+1))=0,"",IF(IS NUMBER(MATCH(INDIRECT(ADDR

ESS(ROW(),COLUMN()+1)),B:B,0)),$B$2,IF(ISNUMBER(MA TCH(INDIRECT(ADDRESS(ROW()

,COLUMN()+1)),C:C,0)),$C$2,IF(ISNUMBER(MATCH(INDIR ECT(ADDRESS(ROW(),COLUMN()
+1)),D:D,0)),$D$2,INDIRECT(ADDRESS(ROW(),COLUMN()+ 1))))))

As far as your list being on another WB or WS, there's really no
problem
there.
Just open both files, side by side, and click between them to set the
references.
That way XL will fill in the correct path for you.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!

--------------------------------------------------------------------------
-
"Pontus" wrote in message
...
Thanx Ragdyer,

Vlookup would function for me. I've been spending some time now on
checking
into both V and Hlookup but have still decided to stick to my
formula
where I
use Match (you can see it in a picture if you follow the url
below).

My current problem is of slight different kind than you understood
though.
It is how to, with code, get a reference to the cell to the right
of
where
the code is possitioned. If you follow the url below you might
understand
my
problem better =)

http://www.yuki-forever.com/sp/ee.jpg


My next problem after this will be how to get the formula to
function
when
the table is in another sheet or document since the List to Sort
will
allways
be in a seperate excel document.

Thanx for all your help
Pontus


"Ragdyer" wrote:

From what you're describing, a simple Vlookup formula should work
very
easily for you ... unless I'm missing something.

All you need is a datalist to create your associations.

From your example, say in J1 to J6, you enter:
ITEM, chair, table, sofa, apple, pasta,

And, in K1 to K6, you enter:
GROUP, furniture, furniture, furniture, food, food.

Then in A1, enter this formula:

=VLOOKUP(B1,$J$2:$K$6,2,0)

And copy down as needed.

That would solve your problem ... as I understand it!

What did I miss?
--
Regards,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-


"Pontus" wrote in message
...
Okey to elaborate (this is gonna be a long one =P).

I'm trying to make my work of sorting a column of articles
easier.
Lets
say
I have a column where every cell contains one article, e.g.

A B
1 Chair
2 Table
3 Apple
4 Sofa
5 Pasta


What I would like to do is to have a formula that I can copy
into
cell
A1
and then drag down so that the cells in column A gives instead
a