You are here:

Excel/i don't have a clever response here!

Advertisement


Question
Hi, Tom pleased to meet you.
I use Excel 2010 and Windows 7.

In cell A18, the entry is e1, without equal sign, or double quotes or amphersands,just e1.
In cell B18, the entry is b1.
In cell C18, the entry is c1.
In cell D18, the entry is d1.
In cell E18, the entry is a1.

Each of these 5 entries are meant to refer to cells. For example, the first entry, e1, refers to cell E1. The second entry, b1, refers to cell B1, and so forth.

My question:
I want to change the first entry e1 to =e1 so that the cell, A18 would show the value entered in cell E1.
Similarly, I want to change the second entry b1 to =b1 so that the cell, B18 would show the value entered in cell B1.
Similar changes are desired for the other 3 cells in row 18. I know that I can enter the equal sign manually, but that time consuming.
Is there a function or formula or some way to enter the equal sign in the cells in row 18, using copy and paste, or something?

Thanks for your help.

Chris

Answer
Chris,
Nice to meet you as well.

In any cell you can put

=Indirect(A18)

then drag fill that to the right.

that will pull in the value in cells e1, b1, c1, d1 and a1

Now select those five cells and do a copy
then select A18 and do a paste values.  

then you can delete the five contiguous cells in a single row that contain the Indirect formula.

Does that answer the question satisfactorily?

there isn't an easy way to put an equal sign in front of the cell "names" you have in A18:E18

if you actually want A18:E18 to hold references to the cell "names" in those cells (ex A18: =e1), then you could us a macro.

sub MakeFormulas()
Dim cell as Range
for each cell in selection
 cell.Formula = "=" & cell.value
Next
End Sub

put this in a standard module in the visual basic editor.  then select the cells with the cell "names" and run the macro.

or
in another cell in put in

="="&A18 then drag fill to the right
These cell will now look like
=e1    =b1  =c1  =d1  =a1

now select these and do a paste values on A18:E18  which will overwrite your original entries.  

Now select those 5 cells and go to the right side of the home tab on the ribbon and select "Find & Select" dropdown arrow and select Replace.  In the resulting dialog put in equal signs for both Find What and Replace With  

Find What:  =
Replace With:  =
then click OK.  

This will cause Excel to reevaluate these entries and convert them into formulas


--
regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.