Return formula not Value

Question

I have the data in SHEET 1

"A1" is "2"

"A2" is "4"

"A3" is "=A1+A2"

and it would give a result "6"

Similarly i have data in "SHEET 2" A1 and A2, (say 3, 6) i want some function that would get the formula from SHEET 1 "A3" to SHEET 2 A3 and not the value "6"

I tried simple "=" but that would return the value

Answer 1

We need some "rules" to follow in order to create the macro.  A description of what you do that would cause the code to create the formula is needed.  Something like this:

When a value is typed in by the user in Column A on sheet "GoodMorning" then I need the formula from column X on that same row on the sheet copied into the next empty row on sheet "GoodNight" but put into column V.

Also, if the new formula needs to refer to cells on the original sheet, we'd need to know that - it kind of complicates the issue a little, but obviously it's a critical point.

For example, on the GoodMorning sheet the formula might simply be

=A5 + (B5*2)

and if we copied that literally over onto the GoodNight sheet, then it would be doing the same thing, but referencing the cells on the GoodNight sheet, not the GoodMorning sheet.  In order to do the same math with the same cells from the GoodMorning sheet, we'd have to make it look like this on the GoodNight sheet:

='GoodMorning'!A5 + ('GoodMorning'!B5 * 2)

Fairly easy for a human to figure out very quickly, not so easy to work up in code, but do-able.

However, I'm betting you want the formula to look exactly like it did on the GoodMorning sheet, otherwise the =GoodMorning!A3 formula you already tried but didn't work for you would have been just fine.  I'm also betting you want those formulas copied to the same cell addresses on the "GoodNight" sheet, otherwise they're probably not going to work as expected.

Sheet names, columns and rows involved are all important pieces of information in coming up with a solution.

Answer 2

i want it while using workbook

copy paste is not worthy because there are too many cells at random positions

i a, a beginner on Excel but i would still try the VBA code methode

Answer 3

How are you wanting to do this?  I mean are you just trying to set up some sheets for use later or do you need to have this happen while actually using the workbook?

There is no built-in worksheet function that copies the formula, as you've found out.  Copy and Paste will do it (Copy followed by Paste Special and choose Formula).

Or you can select several sheets and type the formula into a cell on one of the selected sheets and it will be entered on all selected sheets.  Use caution here, because everything you do on one of the selected sheets will also be done on all others until you de-select the group and go back to having just one sheet selected.

If you need this to happen while the workbook is actually in use, then it will require some VBA code (a macro) to make it happen.