Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula by joining text and the value from a formula
I would like to create a formula by joining together a text string and the
value within a defined cell. For example, if the value of 10 is in the cell a5 and I have typed the value 5 in the cell h5 rather than typing =a5 I might like to create a formula rather like this ="a"&h5 or =value("a"&"H5") to return the value in the cell a5 ie 10 I can see a lot of uses in formula building eg when copying formulas across columns, I might like to increase the cell address row number by 1 or a pretdetermined number each time |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula by joining text and the value from a formula
=INDIRECT("A" & H5)
so if H5 contains 10, this formula will give the same results as: =A10 Read about INDIRECT() in Excel Help. It's a really neat function. -- Gary''s Student - gsnu201001 "Jesper Audi" wrote: I would like to create a formula by joining together a text string and the value within a defined cell. For example, if the value of 10 is in the cell a5 and I have typed the value 5 in the cell h5 rather than typing =a5 I might like to create a formula rather like this ="a"&h5 or =value("a"&"H5") to return the value in the cell a5 ie 10 I can see a lot of uses in formula building eg when copying formulas across columns, I might like to increase the cell address row number by 1 or a pretdetermined number each time |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula by joining text and the value from a formula
Hi Gary
Thanks for superfast response. Strangely I was trying = indirect earlier today, but couldn't see a way for it to accomplish what I wanted. To explain a bit more, it isn't the value 10 that I necesssarily want to get to, it is the formula a5. So if in earlier example, I have Values in a5, a6, a7 etc I want to be able to have the values 5, 6, 7 in columns H I J, row 5 I want to be able to copy my joined together formula eg = "a"&"h5" into, say cell H6, so that it reads a5 and that when I copy it into cell I6, and J6, I want it to read =a6 and =a7. For example I might want the cell in a5 to multiply with the cell in a105 and the cell in a6 to multiply with a106 etc, and ideally I would like to be copying acoss columns with the cell reference increasing by 1 for every column. Of course I might not want the cell reference to increase only by 1, so if I wanted it to increase by 3 each time, eg =a5 or = a8 or = a11 then I could just put in the numbers 5, 8 and 11 into cells H5, I5 and J5 In this case, I couldn't get the = Indirect function to help me. When increasing my cell reference only by 1 each time, I can copy my formulae down and then transpose into my row, but this workaround doesn't always help and is a bit longwinded. Many thanks for your time. Jesper "Gary''s Student" wrote: =INDIRECT("A" & H5) so if H5 contains 10, this formula will give the same results as: =A10 Read about INDIRECT() in Excel Help. It's a really neat function. -- Gary''s Student - gsnu201001 "Jesper Audi" wrote: I would like to create a formula by joining together a text string and the value within a defined cell. For example, if the value of 10 is in the cell a5 and I have typed the value 5 in the cell h5 rather than typing =a5 I might like to create a formula rather like this ="a"&h5 or =value("a"&"H5") to return the value in the cell a5 ie 10 I can see a lot of uses in formula building eg when copying formulas across columns, I might like to increase the cell address row number by 1 or a pretdetermined number each time |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula by joining text and the value from a formula
My apologies; I see this does in fact work
Thanks again. Jesper "Jesper Audi" wrote: Hi Gary Thanks for superfast response. Strangely I was trying = indirect earlier today, but couldn't see a way for it to accomplish what I wanted. To explain a bit more, it isn't the value 10 that I necesssarily want to get to, it is the formula a5. So if in earlier example, I have Values in a5, a6, a7 etc I want to be able to have the values 5, 6, 7 in columns H I J, row 5 I want to be able to copy my joined together formula eg = "a"&"h5" into, say cell H6, so that it reads a5 and that when I copy it into cell I6, and J6, I want it to read =a6 and =a7. For example I might want the cell in a5 to multiply with the cell in a105 and the cell in a6 to multiply with a106 etc, and ideally I would like to be copying acoss columns with the cell reference increasing by 1 for every column. Of course I might not want the cell reference to increase only by 1, so if I wanted it to increase by 3 each time, eg =a5 or = a8 or = a11 then I could just put in the numbers 5, 8 and 11 into cells H5, I5 and J5 In this case, I couldn't get the = Indirect function to help me. When increasing my cell reference only by 1 each time, I can copy my formulae down and then transpose into my row, but this workaround doesn't always help and is a bit longwinded. Many thanks for your time. Jesper "Gary''s Student" wrote: =INDIRECT("A" & H5) so if H5 contains 10, this formula will give the same results as: =A10 Read about INDIRECT() in Excel Help. It's a really neat function. -- Gary''s Student - gsnu201001 "Jesper Audi" wrote: I would like to create a formula by joining together a text string and the value within a defined cell. For example, if the value of 10 is in the cell a5 and I have typed the value 5 in the cell h5 rather than typing =a5 I might like to create a formula rather like this ="a"&h5 or =value("a"&"H5") to return the value in the cell a5 ie 10 I can see a lot of uses in formula building eg when copying formulas across columns, I might like to increase the cell address row number by 1 or a pretdetermined number each time |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONCATENATE text to create a formula to be evaluated | Excel Worksheet Functions | |||
create a formula by using text from other cells | Excel Discussion (Misc queries) | |||
Joining dates and text in formula | Excel Worksheet Functions | |||
How do I create a formula for text entries | New Users to Excel | |||
Can I create a formula from text in several cells? | Excel Worksheet Functions |