Getting cell index from cell to the right
Glad you finally got everything working right.
Appreciate the feed-back.
--
Regards,
RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------
"Pontus" wrote in message
...
Thats great, didn't know you could remove thouse...
thanx.
"RagDyeR" wrote:
You're a little redundant with your sheet references.
Once per reference is sufficient, and can shorten your formula a little.
ALSO - Do you realize that your Countif() trap goes to Z1000, while you're
only polling to Z50 in the If() portion of the formula.
Typo ? ... Or what?
Without the extra sheet references:
=IF(rt=0;"";IF(COUNTIF(Sheet1!$B$3:$Z$50;rt)0;IND EX(Sheet1!$B$2:$Z$2;MAX(IF(Sheet1!$B$3:$Z$50=rt;CO LUMN($A:$Y))));rt))
--
Regards,
RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------
"Pontus" wrote in message
...
Btw,
I'll definately use the last code you posted with the minor sheet
reference
changes as this is beautiful (if you can say this about code, hehe):
=IF(rt=0;"";IF(COUNTIF(Sheet1!$B$3:Sheet1!$Z$1000; rt)0;INDEX(Sheet1!$B$2:Sheet1!$Z$2;MAX(IF(Sheet1! $B$3:Sheet1!$Z$50=rt;COLUMN($A:$Y))));rt))
Take Care RagDyer,
Thank you
"RagDyer" wrote:
Just realized that the above formula will return a wrong answer if an
item
is not found in the list.
Use this formula, with a trap for this possibility, instead:
=IF(rt=0,"",IF(COUNTIF($B$3:$Z$50,rt)0,INDEX($B$2 :$Z$2,MAX(IF($B$3:$Z$50=rt,COLUMN(A:Y)))),"Not
In List"))
*Still* an array formula.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"RagDyeR" wrote in message
...
Try this *array* formula.
With group labels in Row 2, from B2 to Z2,
Data in B3 to Z50,
=IF(rt=0,"",INDEX($B$2:$Z$2,MAX(IF($B$3:$Z$50=rt,C OLUMN(A:Y)))))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead
of
the
regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, CSE *must* be used
when
revising the formula.
Be careful - Column() is relative to the Index() range,
That's why they *don't appear* to coincide!
--
HTH,
RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===
"Pontus" wrote in message
...
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
|