LinkBack URL
About LinkBacks
Hi, this might be a stupid question but is it possible to apply a formula to a whole column just one time, so I don't have to copy/paste it every time I insert a new row?
Thank you.
Hi, this might be a stupid question but is it possible to apply a formula to a whole column just one time, so I don't have to copy/paste it every time I insert a new row?
Thank you.
Last edited by VBA Noob; 11-26-2008 at 01:49 PM.
Depends on the formula. Are you talking about a single formula that is doing some comparison ON the whole column, or is it a formula that only applies to one row at a time?
If you DO need the formula IN each row, you will have to copy it down, but there are a lot of keyboard shortcuts for doing that quickly. Is this a large range?
You can highlight the cell and grab the little square in the lower right corner and just pull down with your mouse.
You can CTRL-C to copy the cell, then use your keyboard or mouse to select the entire range you want the cells copied to, press ENTER and it will copy to them all at once.
If you really want a formula to type itself in everytime you add a row, that will require a MACRO.
Post the macro question in the Programming Forum and be sure to post the formula itself you want to be copied.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Not a stupid question.
Normally people copy the formula down further than they need to and just leave it "sticking out".
For a neater way, try converting your range to a list:
select the table
Data->
List->
Create list->
This should add a "new row" row at the bottom of the range, which may remind you of Access if you're used to that.
Type something in to the first cell of the * line
This:
* keeps "tab" inside the table, so you can cycle straight through if you're tapping info in
* copies any formula down a row when you add a new row
* impresses women and makes them want to disrobe in front of you*
HTH
*Not 100% sure about this one
Hello
Copy the cell and paste to wherever you want it. One way to fill the whole column (and I do mean the WHOLE column) is click the Column letter at the top of the Column, therefore selecting the whole column, and paste.
OR select the cell that houses your Formula, and click on the small black box in the bottom right hand corner of the cell. Then drag down to fill.
__________________
Regards
Rahul Nagar
Founder of www.myshortcutkeys.com.
If you get the answer of your questions then please click EDIT in your original post then click on GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.
Oh no, no... see my post!If you really want a formula to type itself in everytime you add a row, that will require a MACRO.
Post the macro question in the Programming Forum and be sure to post the formula itself you want to be copied.
my formula calculates distance traveled.
basically what I want it to do is when I insert a new line, just type in the name and the cell on right of it automatically gets a value
=IF(B23="Post Office",1.8,IF(B23="Gary",3.8,IF(B23="Staples",2.6,IF(B23="Staples",2.6,IF(B23="Bank",2.3,IF(B23="UPS",2.3,IF(B23="Met. Furniture",0.3,0)))))))
right now it works perfectly but like I said, I have to copy it every time, it doesn't take a lot of time but still, it would be nice if I did not have to do it.
Also, is there an easier way to make that formula? cause I would imagine if I had 100 destinations it would get pretty messy
Thank you in advance.
Search the forum for "vlookup" there's about a million examples.
Creating a list, as I described, will copy this kind of formula automatically.
HTH
PS, you might not get 100 destinations, there is a limit on nesting ifs in XL<=2003 - no more than 7!
PPS you have Staples in there twice...
Last edited by Cheeky Charlie; 11-26-2008 at 12:08 PM. Reason: nested ifs won't work anyway
I have an older 2000 Excel, I do not have List in my Data, is there a way to do it in my version? or should I just upgrade mine, but I do like this older one cause it works faster and feels familiar.Originally Posted by Cheeky Charlie
Thanks, didn't even notice it. but what if I wanted excel to check twice that it is truly staples? lol
Cheeky, that List approach is new to me. Awesome find. I can put that to work today! Thanks for that.
Select the range you want your formula to be in (e.g. F2:F250). The first cell (F2) should be white and the others grayed.
Type in your formula as you would for F2. Then Hold the control key down when you hit enter. The formula will appear in all columns with the cell references changing accordingly.
Does this work for you?
ChemistB <- Also on Excel 2000 and wishing I had that List ability
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
We could do it with a macro as has been alluded to, but I think that's possibly overkill. In lieu of list, I'd just copy the formula beyond where it's needed.
CC
Glad I could teach you something JB!
Or do Tools > Options > Edit, tick Extend data range formats and formulas. When you enter data in a new row, Excel will magically copy down formulas in the same row from the row above.
Entia non sunt multiplicanda sine necessitate
shg, I though this only works when the user inserts a row, not when a user inserts new data? As far as I can tell, that's how it works for me (xl2003)
CC
Enter something (anything) in A9:C9, and see what happens in D9.
mmm... works only if I put something in every cell
even your example has a gap...
CC
Yep, it only fills in the formula when any cells that the formula refers to are filled in. So if the formula was A5*B5, the formula would appear as soon as A5 and B5 are filled in. Unfortunately, with a Count function, all cells in the count range need to be filled in.
shg writes
Note that this only works if your list already contains at least 4 rows and it does not work on ALL kinds of formulas. See below info from MicrosoftOr do Tools > Options > Edit, tick Extend data range formats and formulas. When you enter data in a new row, Excel will magically copy down formulas in the same row from the row above.
cheersWhen Auto Extended Formulas May Not Work
Formulas copy down if four of the last five cells contain a similar formula and the cell above the active cell contains the formula that you want copied down. There are times when formulas may not extend. The following list contains examples of formulas that Excel will not copy down.
- Formulas that reference cells in other sheets or workbooks do not copy down, even if they also reference a cell in the list containing the active cell.
- Formulas in which all of the formula's references point to areas outside the list.
- Formulas that reference a dimensional horizontal range in which not all row references are relative, for example:
=SUM($A$1, A2, B1, B2) Column references have no effect on the copy down trigger.
- A formula that uses a series reference, where none of the row references are relative, for example:
=SUM($A$1, $B$1) Column references have no effect on the copy down trigger.
- Formulas containing a range name or natural language formula.
- Data that you paste into the new row.
Most Microsoft Excel functions also copy down, as long as they follow the basic guidelines outlined in this article.
Teylyn
Last edited by teylyn; 11-26-2008 at 05:39 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks