Converting formula to text

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

How can I convert a formula into a text? Basically, I have
a formula in one of the cells. I would like to convert the
formula itself into text, so that I can manipulate it and
use it elsewhere in another cell.

Any help would be greatly appreciated.

Jay
 
Jay said:
How can I convert a formula into a text? Basically, I have
a formula in one of the cells. I would like to convert the
formula itself into text, so that I can manipulate it and
use it elsewhere in another cell.

Any help would be greatly appreciated.

Jay
Click to expand...

If you mean to manipulate it and use it AS A FORMULA elsewhere, you cannot
do this. (Post back with the formula and what you are trying to achieve for
more help.)

If you just want to display/print the formula:
1 Text-format the cell where you want it displayed as text.
2 Select the cell with the formula, highlight the formula in the formula bar
and copy this.
3 Paste into the text-formatted cell.
 
Paul,

What I am actually trying to do is to find an effective
and easy way of reading values that are in adjacent
columns and displaying them one below the other.

Here is the deal:
In one spreadsheet, I have values in columns A through C.
I have a total of 50 rows with several blank rows in
between.

Sheet 1:
Col A Col B Col C
23 X P1

54 Y Q1

534 Z R1

32 X S1
43 Y T1

I have another sheet in which I want to list the values
from each column (without including blank rows and some
unwanted rows). So I started with the first column (A) and
started referencing only the rows that I wanted.

What I am now trying to do is somehow try and read the row
numbers that were referenced along with the new column to
list the values from the subsequent columns.

So Sheet 2 should look like this:

Sheet 2:
Col A
23

54

534

32
43

X

Y

Z

X
Y

P1

Q1

R1

S1
T1


If there is a better way to do this, please let me know.

Thanks,
Jay
 
Putting aside for a moment your unwanted rows, the problem (if I understand
correctly) is to list A1:A50 followed by B1:B50 followed by C1:C50 (of
Sheet1 ?) into Sheet2!A1:A150.

To do this, I would put this formula in Sheet2!A1:
=OFFSET(Sheet1!$A$1,MOD(ROW()-1,50),INT((ROW()-1)/50))
and copy this down as far as A150.

Of course, I cannot take this further to deal with your unwanted rows
without them being defined. However, I hope you will be able to see how I
have constructed a formula that calculates appropriate row and column
numbers, rather than approaching the problem as in your original post.
 
Back
Top