Flashheart
|
|
« on: Wednesday, September 5, 2012, 14:08:01 » |
|
This sounds like something that should have an easy answer, but I'm buggered if I can find it.
I have a formula in excel and I don;t want to have to type it in any time. How do I save it so I can just insert said formula into a cell whenever I need it.
Cheeeeeers
|
|
|
Logged
|
|
|
|
@MacPhlea
Offline
Posts: 2321
|
|
« Reply #1 on: Wednesday, September 5, 2012, 14:09:19 » |
|
Just copy and paste it or drag it down through the cells...
If your formula includes a cell that is fixed in location (i.e. isn't relative to others then just preceed it with $ i.e. $A$3)
the other way os to create a macro button that inserts it when you click the button...
|
|
« Last Edit: Wednesday, September 5, 2012, 14:11:07 by @MacPhlea »
|
Logged
|
|
|
|
Flashheart
|
|
« Reply #2 on: Wednesday, September 5, 2012, 14:14:59 » |
|
Well yeah.
But I want it to remain in excel even for use later on and in different workbooks. Excel already has formulas built in ready to use (sum, count etc). How do I make one of those myself?
|
|
|
Logged
|
|
|
|
Ginginho
Offline
Posts: 6872
|
|
« Reply #3 on: Wednesday, September 5, 2012, 14:15:08 » |
|
Are you familiar with macros? You can do it as a macro, create a shortcut key (for example, ctrl, shft + Y) and save it in "new book", this will be available whenever you open a new sheet.
|
|
|
Logged
|
|
|
|
Ginginho
Offline
Posts: 6872
|
|
« Reply #4 on: Wednesday, September 5, 2012, 14:20:03 » |
|
Click on record macro, give it a name and remember the shortcut key you choose. Select "New book" in the "store macro in" field. Highlight the text in the function text field, copy.
Done.
Open up a new workbook and do the shortcut key combo you chose, see if it worked?
|
|
|
|
Flashheart
|
|
« Reply #5 on: Wednesday, September 5, 2012, 14:20:46 » |
|
Just tried to create a macro and managed to crash excel.
I'll crack it.
|
|
|
Logged
|
|
|
|
Flashheart
|
|
« Reply #6 on: Wednesday, September 5, 2012, 14:26:42 » |
|
That image is different from mine, I'm using 2010 and don't get the 'Highlight the text in the function text field, copy.' option.
And boys are fantastic.
|
|
|
Logged
|
|
|
|
jonny72
Offline
Posts: 5554
|
|
« Reply #7 on: Wednesday, September 5, 2012, 14:27:14 » |
|
But I want it to remain in excel even for use later on and in different workbooks. Excel already has formulas built in ready to use (sum, count etc). How do I make one of those myself?
They're called functions, not formulas. You can create your own but you need to write them in VBA. Plenty of tutorials on the interweb.
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #8 on: Wednesday, September 5, 2012, 14:27:44 » |
|
You can also set up a button on your tool bar and assign the macro to it
|
|
|
Logged
|
|
|
|
Flashheart
|
|
« Reply #9 on: Wednesday, September 5, 2012, 14:29:33 » |
|
They're called functions, not formulas.
You can create your own but you need to write them in VBA.
Plenty of tutorials on the interweb.
Something else for me to never get around to doing then
|
|
|
Logged
|
|
|
|
Ginginho
Offline
Posts: 6872
|
|
« Reply #10 on: Wednesday, September 5, 2012, 14:33:35 » |
|
Ignore my suggestion, i've just closed excel and reopened and tried it, and it didn't work.
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #11 on: Wednesday, September 5, 2012, 14:35:35 » |
|
Ignore my suggestion, i've just closed excel and reopened and tried it, and it didn't work.
You need to save it in a hidden work book that sits behind excel.
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #12 on: Wednesday, September 5, 2012, 14:38:58 » |
|
When you record the macro you have to save it in the personal macro workbook.
|
|
|
|
Flashheart
|
|
« Reply #13 on: Wednesday, September 5, 2012, 14:55:44 » |
|
Is visual basic the same language as when creating the formula (it looks as though it may be), or would I have to learn a new computing language?
This strikes me as something that should really be much, much, much simpler.
|
|
|
Logged
|
|
|
|
Flashheart
|
|
« Reply #14 on: Wednesday, September 5, 2012, 14:56:32 » |
|
When you record the macro you have to save it in the personal macro workbook.
But nothing happens
|
|
|
Logged
|
|
|
|
|