Excel/i don't have a clever response here!
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.
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.
Nice to meet you as well.
In any cell you can put
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.
Dim cell as Range
for each cell in selection
cell.Formula = "=" & cell.value
put this in a standard module in the visual basic editor. then select the cells with the cell "names" and run the macro.
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