| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combined formula
Hi all,
I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 I would like to have another cell where I can enter a value (e.g. 2) and receive in an additional field the value (e.g. Test2). How can I add to the formula (e.g. =A...) a variable from another field? Kind regards, Simon |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combined formula
One way
With B1 containing the input number, eg: 2 In C1: =INDEX(A1:A3,MATCH(TRUE,INDEX((RIGHT(A1:A3)=B1&"") ,),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Simon Minder" wrote: I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 I would like to have another cell where I can enter a value (e.g. 2) and receive in an additional field the value (e.g. Test2). How can I add to the formula (e.g. =A...) a variable from another field? Kind regards, Simon |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combined formula
Hi Max,
Thank you very much for your solution. However, I have a problem applying the formula to my example. I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 Let's assume I enter in a cell (B1) the value "1". I would like to see the value "Test1" in cell (B2). If I enter the vaule "2" I would like to see the value "Test2" in cell (B2). The formula would be stored in the cell (B2) and would take the variable from cell (B1). Your formula seems to work only with numbers, but not with text in the selected range (e.g. Test1...Test3). Kind regards, Simon "Max" wrote: One way With B1 containing the input number, eg: 2 In C1: =INDEX(A1:A3,MATCH(TRUE,INDEX((RIGHT(A1:A3)=B1&"") ,),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Simon Minder" wrote: I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 I would like to have another cell where I can enter a value (e.g. 2) and receive in an additional field the value (e.g. Test2). How can I add to the formula (e.g. =A...) a variable from another field? Kind regards, Simon |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combined formula
try this put this in B2 ="Test"& B1 "Simon Minder" wrote: Hi Max, Thank you very much for your solution. However, I have a problem applying the formula to my example. I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 Let's assume I enter in a cell (B1) the value "1". I would like to see the value "Test1" in cell (B2). If I enter the vaule "2" I would like to see the value "Test2" in cell (B2). The formula would be stored in the cell (B2) and would take the variable from cell (B1). Your formula seems to work only with numbers, but not with text in the selected range (e.g. Test1...Test3). Kind regards, Simon "Max" wrote: One way With B1 containing the input number, eg: 2 In C1: =INDEX(A1:A3,MATCH(TRUE,INDEX((RIGHT(A1:A3)=B1&"") ,),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Simon Minder" wrote: I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 I would like to have another cell where I can enter a value (e.g. 2) and receive in an additional field the value (e.g. Test2). How can I add to the formula (e.g. =A...) a variable from another field? Kind regards, Simon |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combined formula
Hi Raz,
Thank you for your advice, but I would like to select with the number in the field "B1" a value out of the range A1...A3, by adding the number to the formula. E.g. I type in "1" and the formula in "B2" would change to "=A1" and display the value from "A1". If I enter the value "2" into the field "B1" I would like to see "Test2" in the cell "B2" and the formula would be "=A2". I only want to be able to change the number in the formula e.g. "1" in "=A1" to a "2" when I enter a number "2" in a cell. Kind regards, Simon "Raz" wrote: try this put this in B2 ="Test"& B1 "Simon Minder" wrote: Hi Max, Thank you very much for your solution. However, I have a problem applying the formula to my example. I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 Let's assume I enter in a cell (B1) the value "1". I would like to see the value "Test1" in cell (B2). If I enter the vaule "2" I would like to see the value "Test2" in cell (B2). The formula would be stored in the cell (B2) and would take the variable from cell (B1). Your formula seems to work only with numbers, but not with text in the selected range (e.g. Test1...Test3). Kind regards, Simon "Max" wrote: One way With B1 containing the input number, eg: 2 In C1: =INDEX(A1:A3,MATCH(TRUE,INDEX((RIGHT(A1:A3)=B1&"") ,),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Simon Minder" wrote: I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 I would like to have another cell where I can enter a value (e.g. 2) and receive in an additional field the value (e.g. Test2). How can I add to the formula (e.g. =A...) a variable from another field? Kind regards, Simon |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combined formula
Got it: =INDIRECT("sheet name"&ADDRESS(G1;G2))
"Simon Minder" wrote: Hi Raz, Thank you for your advice, but I would like to select with the number in the field "B1" a value out of the range A1...A3, by adding the number to the formula. E.g. I type in "1" and the formula in "B2" would change to "=A1" and display the value from "A1". If I enter the value "2" into the field "B1" I would like to see "Test2" in the cell "B2" and the formula would be "=A2". I only want to be able to change the number in the formula e.g. "1" in "=A1" to a "2" when I enter a number "2" in a cell. Kind regards, Simon "Raz" wrote: try this put this in B2 ="Test"& B1 "Simon Minder" wrote: Hi Max, Thank you very much for your solution. However, I have a problem applying the formula to my example. I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 Let's assume I enter in a cell (B1) the value "1". I would like to see the value "Test1" in cell (B2). If I enter the vaule "2" I would like to see the value "Test2" in cell (B2). The formula would be stored in the cell (B2) and would take the variable from cell (B1). Your formula seems to work only with numbers, but not with text in the selected range (e.g. Test1...Test3). Kind regards, Simon "Max" wrote: One way With B1 containing the input number, eg: 2 In C1: =INDEX(A1:A3,MATCH(TRUE,INDEX((RIGHT(A1:A3)=B1&"") ,),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Simon Minder" wrote: I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 I would like to have another cell where I can enter a value (e.g. 2) and receive in an additional field the value (e.g. Test2). How can I add to the formula (e.g. =A...) a variable from another field? Kind regards, Simon |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combined formula
Got it: =INDIRECT("sheet name"&ADDRESS(G1;G2))
"Max" wrote: One way With B1 containing the input number, eg: 2 In C1: =INDEX(A1:A3,MATCH(TRUE,INDEX((RIGHT(A1:A3)=B1&"") ,),0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Simon Minder" wrote: I have three cells with a value: Cell Value A1 Test1 A2 Test2 A3 Test3 I would like to have another cell where I can enter a value (e.g. 2) and receive in an additional field the value (e.g. Test2). How can I add to the formula (e.g. =A...) a variable from another field? Kind regards, Simon |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Decrease Decimal in a combined text/formula cell | Excel Worksheet Functions | |||
| Array formula combined with Lookup | Excel Discussion (Misc queries) | |||
| I need to create an array formula combined with a countif | Excel Worksheet Functions | |||
| How to od a combined IF and AND in a matrix formula | Excel Worksheet Functions | |||
| Can you combined the SUMIF and SUBTOTAL functions in a formula? | Excel Worksheet Functions | |||
![Simon Minder[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
