CODEWITHSUNDEEP
excelexcel-2007excel-formula
Amie-lea
Amie-lea

Reputation: 103

A formula to copy the values from a formula to another column

I have a column of values created from a formula, I know I can copy the values over to another column by using the clipboard. BUT...I want my spreadsheet to be automatic whilst avoiding the use of VBA coding, so it would be ideal if I could create a formula for the next column which copies the VALUES over to the next column. Maybe an INDEX/MATCH kind of method but one that copies ONLY the values rather than the formulas.

So in essence I want a formula to copy the values from a formula....

Upvotes: 7

Views: 199273

Answers (6)

user11604334
user11604334

Reputation: 1

Copy the cell. Paste special as link. Will update with original. No formula though.

Upvotes: -2

Stefano Arnone
Stefano Arnone

Reputation: 1

What about trying with VLOOKUP? The syntax is:

=VLOOKUP(cell you want to copy, range you want to copy, 1, FALSE).

It should do the trick.

Upvotes: 0

ben
ben

Reputation: 11

you can use those functions together with iferror as a work around.

try =IFERROR(VALUE(A4),(CONCATENATE(A4)))

Upvotes: 1

Joe
Joe

Reputation: 51

Use =concatenate(). Concatenate is generally used to combine the words of several cells into one, but if you only input one cell it will return that value. There are other methods, but I find this is the best because it is the only method that works when a formula, whose value you wish to return, is in a merged cell.

Upvotes: 5

Viji
Viji

Reputation: 2629

You can use =A4, in case A4 is having long formula

Upvotes: 13

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

For such you must rely on VBA. You can't do it just with Excel functions.

Upvotes: 3

Related Questions