Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
Hi,
I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
Do you mean:
A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
Hmm, I'll try to explain better.
What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus "JMay" wrote: Do you mean: A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
Would you care to elaborate on what you intend to do with that address of
the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Pontus" wrote in message ... Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus "JMay" wrote: Do you mean: A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
"Pontus" wrote in message
Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus .... maybe: =ADDRESS(ROW();COLUMN()+1) -- pozdrav! Berislav |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 name of a group that the article in column B belongs to. The answer I want could for instance be: A B 1 Furniture Chair 2 Furniture Table 3 Food Apple 4 Furniture Sofa 5 Food Pasta Hitherto I have managed to write the code that recognizes a word and gives back the name of the group. My only problem now is that I somehow need to refer to the cell that is possitioned to the right of the cell where I copy-paste my code. This has to be done without going into the code and change manually since the code is kind of massive. I want other persons to be able to use this that do not know which variables to change. If anybody has the answer how to do this it would be really great thanx Pontus "Ragdyer" wrote: Would you care to elaborate on what you intend to do with that address of the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Pontus" wrote in message ... Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus "JMay" wrote: Do you mean: A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 name of a group that the article in column B belongs to. The answer I want could for instance be: A B 1 Furniture Chair 2 Furniture Table 3 Food Apple 4 Furniture Sofa 5 Food Pasta Hitherto I have managed to write the code that recognizes a word and gives back the name of the group. My only problem now is that I somehow need to refer to the cell that is possitioned to the right of the cell where I copy-paste my code. This has to be done without going into the code and change manually since the code is kind of massive. I want other persons to be able to use this that do not know which variables to change. If anybody has the answer how to do this it would be really great thanx Pontus "Ragdyer" wrote: Would you care to elaborate on what you intend to do with that address of the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Pontus" wrote in message ... Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus "JMay" wrote: Do you mean: A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 name of a group that the article in column B belongs to. The answer I want could for instance be: A B 1 Furniture Chair 2 Furniture Table 3 Food Apple 4 Furniture Sofa 5 Food Pasta Hitherto I have managed to write the code that recognizes a word and gives back the name of the group. My only problem now is that I somehow need to refer to the cell that is possitioned to the right of the cell where I copy-paste my code. This has to be done without going into the code and change manually since the code is kind of massive. I want other persons to be able to use this that do not know which variables to change. If anybody has the answer how to do this it would be really great thanx Pontus "Ragdyer" wrote: Would you care to elaborate on what you intend to do with that address of the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Pontus" wrote in message ... Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus "JMay" wrote: Do you mean: A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
Hi b&s,
Sorry for missing your answer but thank you for your help. That was exactely what I thought I were looking for. After having pulled my hair now for a while though I realise that this formula gives back the index of that cell but in text format. What I really nead is some code which gives a reference to that cell instead so that the whole formula can get or just as well gets the content of the cell. If you check my last answer to Ragdyer you might understand much better what I am trying to do. thanks again Pontus "b&s" wrote: "Pontus" wrote in message Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus .... maybe: =ADDRESS(ROW();COLUMN()+1) -- pozdrav! Berislav |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 name of a group that the article in column B belongs to. The answer I want could for instance be: A B 1 Furniture Chair 2 Furniture Table 3 Food Apple 4 Furniture Sofa 5 Food Pasta Hitherto I have managed to write the code that recognizes a word and gives back the name of the group. My only problem now is that I somehow need to refer to the cell that is possitioned to the right of the cell where I copy-paste my code. This has to be done without going into the code and change manually since the code is kind of massive. I want other persons to be able to use this that do not know which variables to change. If anybody has the answer how to do this it would be really great thanx Pontus "Ragdyer" wrote: Would you care to elaborate on what you intend to do with that address of the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Pontus" wrote in message ... Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus "JMay" wrote: Do you mean: A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
"Pontus" wrote in message
Hi b&s, Sorry for missing your answer but thank you for your help. That was exactely what I thought I were looking for. After having pulled my hair now for a while though I realise that this formula gives back the index of that cell but in text format. What I really nead is some code which gives a reference to that cell instead so that the whole formula can get or just as well gets the content of the cell. If you check my last answer to Ragdyer you might understand much better what I am trying to do. thanks again Pontus "b&s" wrote: "Pontus" wrote in message Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus .... maybe: =ADDRESS(ROW();COLUMN()+1) -- pozdrav! Berislav Hi Pontus, Try to put in your formula: =INDIRECT(ADDRESS(ROW();COLUMN()+1)) instead H4 (according your example) If I'm understanding your question correctly. -- pozdrav! Berislav |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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(ADDRESS(ROW();COLUMN()+1));S heet1!$B:$B;0));Sheet1!$B$2;IF(ISNUMBER(MATCH(INDI RECT(ADDRESS(ROW();COLUMN()+1));Sheet1!$C:$C;0));S heet1!$C$2;IF(ISNUMBER(MATCH(INDIRECT(ADDRESS(ROW( );COLUMN()+1));Sheet1!$D:$D;0));Sheet1!$D$2;INDIRE CT(ADDRESS(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 name of a group that the article in column B belongs to. The answer I want could for instance be: A B 1 Furniture Chair 2 Furniture Table 3 Food Apple 4 Furniture Sofa 5 Food Pasta Hitherto I have managed to write the code that recognizes a word and gives back the name of the group. My only problem now is that I somehow need to refer to the cell that is possitioned to the right of the cell where I copy-paste my code. This has to be done without going into the code and change manually since the code is kind of massive. I want other persons to be able to use this that do not know which variables to change. If anybody has the answer how to do this it would be really great thanx Pontus "Ragdyer" wrote: Would you care to elaborate on what you intend to do with that address of the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Pontus" wrote in message ... Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus "JMay" wrote: Do you mean: A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
<<<"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 name of a group that the article in column B belongs to. The answer I want could for instance be: A B 1 Furniture Chair 2 Furniture Table 3 Food Apple 4 Furniture Sofa 5 Food Pasta Hitherto I have managed to write the code that recognizes a word and gives back the name of the group. My only problem now is that I somehow need to refer to the cell that is possitioned to the right of the cell where I copy-paste my code. This has to be done without going into the code and change manually since the code is kind of massive. I want other persons to be able to use this that do not know which variables to change. If anybody has the answer how to do this it would be really great thanx Pontus "Ragdyer" wrote: Would you care to elaborate on what you intend to do with that address of the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Pontus" wrote in message ... Hmm, I'll try to explain better. What I want to do is to have a formula that I can copy in to any cell which will give the cell index of the cell to the right. Exampel: If I copy my formula in to cell K23 I want the answer to be L23 or If I copy my formula in to cell B23 I want the answer to be C23 etc. Hope I made myself more clear now =) Thanks for answering. Pontus "JMay" wrote: Do you mean: A1 =Offset($B1,0,1) B1 = Sum(A2:A4) C1 = whatever "Pontus" wrote: Hi, I want to have a formula that retrievs the index from the cell to the right of the cell with the formula. I know that the formula below gives the index of the cell you are in but that doesn't help me much unfortunately. Is there a simplier way or maybe a way that utilises this code? =SUBSTITUTE(SUBSTITUTE(CELL("address");"$";"");ROW ();"") Best Regards Pontus |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 name of a group that the article in column B belongs to. The answer I want could for instance be: A B 1 Furniture Chair 2 Furniture Table 3 Food Apple 4 Furniture Sofa 5 Food Pasta Hitherto I have managed to write the code that recognizes a word and gives back the name of the group. My only problem now is that I somehow need to refer to the cell that is possitioned to the right of the cell where I copy-paste my code. This has to be done without going into the code and change manually since the code is kind of massive. I want other persons to be able to use this that do not know which variables to change. If anybody has the answer how to do this it would be really great thanx Pontus "Ragdyer" wrote: Would you care to elaborate on what you intend to do with that address of the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD -------------------------------------------------------------------------- |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 name of a group that the article in column B belongs to. The answer I want could for instance be: A B 1 Furniture Chair 2 Furniture Table 3 Food Apple 4 Furniture Sofa 5 Food Pasta Hitherto I have managed to write the code that recognizes a word and gives back the name of the group. My only problem now is that I somehow need to refer to the cell that is possitioned to the right of the cell where I copy-paste my code. This has to be done without going into the code and change manually since the code is kind of massive. I want other persons to be able to use this that do not know which variables to change. If anybody has the answer how to do this it would be really great thanx Pontus "Ragdyer" wrote: Would you care to elaborate on what you intend to do with that address of the cell on the right? The reason I'm asking, is because you can use a "relative" named formula that can return the *contents* of that cell to the right of which ever cell you enter it into. Would that be of interest to you? -- Regards, RD -------------------------------------------------------------------------- |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 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 |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 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 |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
Heey man you are a f.... genious!!! Thanx a million
I have been struggeling to unerstand how the code worka now for a while. I noticed the same problem as you did with the code giving back the an answer even though the text wasn't listed. I also did minor changes as using a reference back to sheet1 since I always copy the list I want to sort into sheet 2(where I will use the formula). How I solved the problem you also did with COUNTIF looks like this: =IF(rt=0;"";IF(MAX(IF(Sheet1!$B$3:Sheet1!$Z$50=rt; COLUMN($A:$Y)))=0;rt;INDEX(Sheet1!$B$2:Sheet1!$Z$2 ;MAX(IF(Sheet1!$B$3:Sheet1!$Z$50=rt;COLUMN($A:$Y)) )))) As you see I just re-used apart of your formula. As I wanted the answer to be the text in cell rt if it didn't exist in the list I just put in rt instead of "Not in List" as you did... Seriously, thanx.. Partly for bearing with all my questions but,,, You are really creative and earn huge credit for this. Thanx a million. //amazed and thankfull Pontus "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 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). |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 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). |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 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). |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting cell index from cell to the right
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 |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row and Col Index of a cell with Max Value | Excel Worksheet Functions | |||
Using Contents of One Cell as Index into Another Cell | Excel Worksheet Functions | |||
index,match + 1 cell below? | Excel Worksheet Functions | |||
autofill according to cell index | Excel Worksheet Functions | |||
autofill according to cell index. | Excel Discussion (Misc queries) |