| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I assign a partial cell address using data?
I am new to Excel programming and have run into a problem.
I will try to describe as best as I can: Cell A23 has text "ABC" Cell B12 has formula which results in the number 23 What I would like to do in cell A99 is "=A23" where "A" is fixed and the "23" comes from cell B12. In other words it would look something like this: =A(B12) The above does not work...even if I convert 23 to text. Any suggestions? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I assign a partial cell address using data?
Maybe this:
=LEFT(A23,1)&B12 A simple recorded macro yields this: Range("A99").Select ActiveCell.FormulaR1C1 = "=LEFT(R[-76]C,1)&R[-87]C[1]" HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "YGSBill" wrote: I am new to Excel programming and have run into a problem. I will try to describe as best as I can: Cell A23 has text "ABC" Cell B12 has formula which results in the number 23 What I would like to do in cell A99 is "=A23" where "A" is fixed and the "23" comes from cell B12. In other words it would look something like this: =A(B12) The above does not work...even if I convert 23 to text. Any suggestions? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I assign a partial cell address using data?
Hi ryguy7272
Sorry...I do not understand...will the result be "ABC" as that is what I am looking for? Maybe I was not very clear in what I am trying to do. I am trying to come up with a formula that will show the data from cell A23 in cell A99 using a reference to cell A23 with "A" as fixed and "23" as the variable found in cell B12. Thank you "ryguy7272" wrote: Maybe this: =LEFT(A23,1)&B12 A simple recorded macro yields this: Range("A99").Select ActiveCell.FormulaR1C1 = "=LEFT(R[-76]C,1)&R[-87]C[1]" HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "YGSBill" wrote: I am new to Excel programming and have run into a problem. I will try to describe as best as I can: Cell A23 has text "ABC" Cell B12 has formula which results in the number 23 What I would like to do in cell A99 is "=A23" where "A" is fixed and the "23" comes from cell B12. In other words it would look something like this: =A(B12) The above does not work...even if I convert 23 to text. Any suggestions? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I assign a partial cell address using data?
You: Will the result be "ABC"?
Me: I don't understand what you mean. Please provide another example, or restate you initial example. You: I am trying to come up with a formula that will show the data from cell A23 in cell A99 using a reference to cell A23 with "A" as fixed and "23" as the variable found in cell B12. Me: I think this is what I did, right. Did you try it? Well, here is another idea. Put the name of your sheet in cell A1. Assuming the sheet is named 'Sheet1', use this function anywhere (except A23 or B12): =LEFT(A23,1)&INDIRECT("'" & A1 &"'!B12") Good luck, Ryan--- "YGSBill" wrote: Hi ryguy7272 Sorry...I do not understand...will the result be "ABC" as that is what I am looking for? Maybe I was not very clear in what I am trying to do. I am trying to come up with a formula that will show the data from cell A23 in cell A99 using a reference to cell A23 with "A" as fixed and "23" as the variable found in cell B12. Thank you "ryguy7272" wrote: Maybe this: =LEFT(A23,1)&B12 A simple recorded macro yields this: Range("A99").Select ActiveCell.FormulaR1C1 = "=LEFT(R[-76]C,1)&R[-87]C[1]" HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "YGSBill" wrote: I am new to Excel programming and have run into a problem. I will try to describe as best as I can: Cell A23 has text "ABC" Cell B12 has formula which results in the number 23 What I would like to do in cell A99 is "=A23" where "A" is fixed and the "23" comes from cell B12. In other words it would look something like this: =A(B12) The above does not work...even if I convert 23 to text. Any suggestions? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I assign a partial cell address using data?
Hi RyGuy
The result that I get is "A23" but the result that I want is "ABC". I will try to explain differently... I need to make a command that will display the contents of A23 in cell 99 by using the known column "A" and getting the "23" from cell B12...in other words, I need to be able to make the equivalent of "=A23" by using a variable from B12. If B12 contained 25, I would like the equivalent of "=A25", etc. That is why the result should read "ABC" as that is what is stored in A23. "RyGuy" wrote: You: Will the result be "ABC"? Me: I don't understand what you mean. Please provide another example, or restate you initial example. You: I am trying to come up with a formula that will show the data from cell A23 in cell A99 using a reference to cell A23 with "A" as fixed and "23" as the variable found in cell B12. Me: I think this is what I did, right. Did you try it? Well, here is another idea. Put the name of your sheet in cell A1. Assuming the sheet is named 'Sheet1', use this function anywhere (except A23 or B12): =LEFT(A23,1)&INDIRECT("'" & A1 &"'!B12") Good luck, Ryan--- "YGSBill" wrote: Hi ryguy7272 Sorry...I do not understand...will the result be "ABC" as that is what I am looking for? Maybe I was not very clear in what I am trying to do. I am trying to come up with a formula that will show the data from cell A23 in cell A99 using a reference to cell A23 with "A" as fixed and "23" as the variable found in cell B12. Thank you "ryguy7272" wrote: Maybe this: =LEFT(A23,1)&B12 A simple recorded macro yields this: Range("A99").Select ActiveCell.FormulaR1C1 = "=LEFT(R[-76]C,1)&R[-87]C[1]" HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "YGSBill" wrote: I am new to Excel programming and have run into a problem. I will try to describe as best as I can: Cell A23 has text "ABC" Cell B12 has formula which results in the number 23 What I would like to do in cell A99 is "=A23" where "A" is fixed and the "23" comes from cell B12. In other words it would look something like this: =A(B12) The above does not work...even if I convert 23 to text. Any suggestions? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I assign a partial cell address using data?
In A99:
=INDIRECT("A" & B12) Tim On Jun 16, 1:49*pm, YGSBill wrote: I am new to Excel programming and have run into a problem. I will try to describe as best as I can: Cell A23 has text "ABC" Cell B12 has formula which results in the number 23 What I would like to do in cell A99 is "=A23" where "A" is fixed and the "23" comes from cell B12. *In other words it would look something like this: =A(B12) The above does not work...even if I convert 23 to text. Any suggestions? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I assign a partial cell address using data?
Thank you soooooo very much. Sometimes its the simple things that seem to
trip me up and you have helped me tremendously. While on the subject, would you know how I would add the following to the formula...assume that cell A23 is in Sheet2 and I am working in Sheet1... If I put the text "Sheet2" in an empty cell on Sheet1 or reference Sheet2 directly, what would be the syntaxii used? Again Thank you "Tim Williams" wrote: In A99: =INDIRECT("A" & B12) Tim On Jun 16, 1:49 pm, YGSBill wrote: I am new to Excel programming and have run into a problem. I will try to describe as best as I can: Cell A23 has text "ABC" Cell B12 has formula which results in the number 23 What I would like to do in cell A99 is "=A23" where "A" is fixed and the "23" comes from cell B12. In other words it would look something like this: =A(B12) The above does not work...even if I convert 23 to text. Any suggestions? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I assign a partial cell address using data?
=INDIRECT(F1&"!A"&B12)
With Sheet2 in F1 and 23 in B12 On Thu, 18 Jun 2009 01:15:01 -0700, YGSBill wrote: Thank you soooooo very much. Sometimes its the simple things that seem to trip me up and you have helped me tremendously. While on the subject, would you know how I would add the following to the formula...assume that cell A23 is in Sheet2 and I am working in Sheet1... If I put the text "Sheet2" in an empty cell on Sheet1 or reference Sheet2 directly, what would be the syntaxii used? Again Thank you "Tim Williams" wrote: In A99: =INDIRECT("A" & B12) Tim On Jun 16, 1:49 pm, YGSBill wrote: I am new to Excel programming and have run into a problem. I will try to describe as best as I can: Cell A23 has text "ABC" Cell B12 has formula which results in the number 23 What I would like to do in cell A99 is "=A23" where "A" is fixed and the "23" comes from cell B12. In other words it would look something like this: =A(B12) The above does not work...even if I convert 23 to text. Any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation of partial data in a cell. | Excel Discussion (Misc queries) | |||
| Text Being Seen as Partial Cell Address | Excel Discussion (Misc queries) | |||
| Assign the cell address with a function | Excel Worksheet Functions | |||
| Partial Address match in an array | Excel Discussion (Misc queries) | |||
| remove partial data from cell | Excel Worksheet Functions | |||
Hybrid Mode
