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?

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?

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?

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?

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?

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?

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?

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?