convert this M5447270!V30 to a formula, add somehow the = sign

[Guest]

If I've created a text string in a cell that with the equal sign in front of
it would be a formula (or function) pointing to a cell in another worksheet,
how can I add an equal sign to the text string in many cells so that the
cells end up acting like formulas? M5447270!V30 is the text string. If it
had an = sign in front it would point to a cell in another worksheet. Thanks
for any thots, Bob

 

[Ron Rosenfeld]

If I've created a text string in a cell that with the equal sign in front of
it would be a formula (or function) pointing to a cell in another worksheet,
how can I add an equal sign to the text string in many cells so that the
cells end up acting like formulas? M5447270!V30 is the text string. If it
had an = sign in front it would point to a cell in another worksheet. Thanks
for any thots, Bob

You could use the INDIRECT function.

If M5447270!V30 is in A10, the formula:

=INDIRECT(A10)

will return the value that is in V30 on sheet M5447270


--ron

 

[Gord Dibben]

Bob

A couple of things I can think of.

1. In a helper column enter ="=" & cellref

Drag/copy that down the column.

Select the range and Edit>Copy.

Edit>Paste Special(in place)>Values>OK>Esc

Format to General then Edit>Find

what: =

with: =

Replace all. Done deed.

2. Use a macro which saves a bunch of steps.

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text") 'enter the = sign
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Bob,

What are you trying to do?

If you have a cell reference you can use the indirect function to get the
contents of the cell.

if you want to convert M5447270!V30 to actually referencing the cell then
then one way is to compose the formula in another cell

ie [B1]="="&A1
then copy down the cells, copy paste special values on b and then find
replace = with a =

 

[Gord Dibben]

Ron

Good thinnin' <g>

I take these things too literally sometimes.

I got stuck on the word "convert" and missed the whole point of the exercise
which was to return values from another worksheet.


Gord

You could use the INDIRECT function.

If M5447270!V30 is in A10, the formula:

=INDIRECT(A10)

will return the value that is in V30 on sheet M5447270


--ron

Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Ron

Good thinnin' <g>

I take these things too literally sometimes.

I got stuck on the word "convert" and missed the whole point of the exercise
which was to return values from another worksheet.


Gord

If I had a nickel for every time I did something like that (miss the point of a
post), I'd be a wealthy man!


--ron

 

[Guest]

Thanks, Ron, I had success with this when in the SAME SPREADSHEET. What if
it points to a worksheet in another spreadsheet file? Will INDIRECT still
work for me? I had a little trouble creating the text string from this cell
reference:
[Payroll Weekend Invoices 12-3-06.xls]W5442020'!$T$25

 

[Bob Phillips]

Yes it will, you just concatenate the string text and the cell reference

"shete name"&cell

but there is a mjor problem, tINDIRECT doesn't work on closed workbooks,
which from your description yours will be?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

Thanks, Ron, I had success with this when in the SAME SPREADSHEET. What
if
it points to a worksheet in another spreadsheet file? Will INDIRECT still
work for me? I had a little trouble creating the text string from this
cell
reference:
[Payroll Weekend Invoices 12-3-06.xls]W5442020'!$T$25

You could use the INDIRECT function.

If M5447270!V30 is in A10, the formula:

=INDIRECT(A10)

will return the value that is in V30 on sheet M5447270


--ron

 

[Ron Rosenfeld]

Thanks, Ron, I had success with this when in the SAME SPREADSHEET. What if
it points to a worksheet in another spreadsheet file? Will INDIRECT still
work for me? I had a little trouble creating the text string from this cell
reference:
[Payroll Weekend Invoices 12-3-06.xls]W5442020'!$T$25

INDIRECT will work on an external workbook, so long as you form the name
correctly. However, INDIRECT will not work on a closed workbook.

If the external workbook must be closed, then I would recommend you download
and install Longre's free morefunc.xll add-in (which can be easily distributed
with workbooks, if that is an issue), from http://xcell05.free.fr/

and use the INDIRECT.EXT function. Unlike the INDIRECT built-in function,
INDIRECT.EXT can also return the value of a cell whose workbook is closed.


--ron

 

[RagDyer]

Then you can simply follow Gord's suggestion, which works with open and/or
closed WBs.

One amendment I prefer though, is after the "Paste Special" - "Values",
While the cells are *still* selected, just open and close TTC (<Data> <Text
To Columns>).
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Thanks, Ron, I had success with this when in the SAME SPREADSHEET. What
if
it points to a worksheet in another spreadsheet file? Will INDIRECT still
work for me? I had a little trouble creating the text string from this
cell
reference:
[Payroll Weekend Invoices 12-3-06.xls]W5442020'!$T$25

You could use the INDIRECT function.

If M5447270!V30 is in A10, the formula:

=INDIRECT(A10)

will return the value that is in V30 on sheet M5447270


--ron

 

[Gord Dibben]

I like that RD

No edit>replace necessary.

Learn a new wrinkle every day.


Gord

Then you can simply follow Gord's suggestion, which works with open and/or
closed WBs.

One amendment I prefer though, is after the "Paste Special" - "Values",
While the cells are *still* selected, just open and close TTC (<Data> <Text
To Columns>).

Gord Dibben MS Excel MVP

 

[Gord Dibben]

It is the same as hitting F2 then ENTER to re-enter the formula but allows you
to "edit" multiple cells at once.

Easier yet is a method RagDyer showed me yesterday.

Don't do the Edit>Replace routine.

Just select the range and Data>Text to Columns>Finish

Does the same job with a few less keystrokes.


Gord