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....
This question is related to
excel
excel-2007
excel-formula
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.
For such you must rely on VBA. You can't do it just with Excel functions.
Copy the cell. Paste special as link. Will update with original. No formula though.
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.
you can use those functions together with iferror as a work around.
try =IFERROR(VALUE(A4),(CONCATENATE(A4)))
You can use =A4, in case A4 is having long formula
Source: Stackoverflow.com