• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create formula for an entire column

J

juwin_pt

New Member
Creating a formula for an entire column. Is there a way not to increase the file size?


Please look at this attachment for the entire question.


http://www.sendspace.com/file/0oz9u9
 
Hi juwin_pt,


If you don't want the formula in the whole column, then don't do it. If you only want to apply the formula to the cells in your example then put the formula in C1, then double-click on the fill-handle ( little black square on C1 lower right corner) this will only apply the formula as far down as the data in the adjoining column
 
Hi OldChippy,


Your solution works only when I know how many rows there are.


I want to make a data entry sheet, where people can put in different values. I want to create a column in that sheet, which has a calculated value, based on 3 other columns which user enter. All the rows in the sheet has the same formula. I dont know how many rows the user will enter. So, as per your advice, till which row, do I copy the formula? It is possible that the user may do a "Insert row" in between. Then, the formula column on the new row is blank.


Thanks,

Juwin
 
Hi juwin_pt,


I'm not sure what version of Excel you are using, but if it 2007, you can create a Table, then if an extra line is inserted then the formula is inserted also


http://www.jkp-ads.com/articles/Excel2007Tables.asp
 
Hi,


I am using 2007 version. The problem with table method is, if the user populates non-adjacent rows (leaves a blank row in between), the formula is not copied further.


Attachment: http://www.sendspace.com/file/b20c6a


Here, after entering, evenif he realizes and tries to delete the blank row, the formula is not copied.


Thanks,

Juwin.
 
Hi,


If the inputter is likely to input into non-adjacent rows, then may be a formula copied down to a certain number of rows (your max, not entire column), so that the inputter knows the formula is there, but it won't work until all cells in the row are filled in?


=IF(COUNT(A2:C2)=3,A2*B2*C2,0)
 
See, that goes back to my original question. I don't know the number of rows. So, I cant do that. If I knew the number of rows, I can very well paste formula to those rows, not wanting Excel to autocopy the formula. I want something which is more column based formula.


Thanks,

Juwin.
 
I would say then VBA is the only way to do that and thats not my expertise I'm afraid, sorry I can't help you :-(
 
The problem with VBA is the security popups. I can do VBA, but digitally signing the macro is the problem. Thats why I was looking for a solution without using VBA.
 
Juwin

"I am using 2007 version. The problem with table method is, if the user populates non-adjacent rows (leaves a blank row in between), the formula is not copied further."


This should involve education of the users and maybe a warning cell at the top which tells them when a Blank Record is detected.
 
Juwin


Fair Point,


But the world is a better place because we have a great group of people here trying to help each other out.


Your point of highlighting cells is correct, but technically doesn't answer your own question, just as we also didn't answer it, because you haven't actually "created a formula for an entire column", your just using a highlighting methodology.


If you want to highlight something of interest in the forums don't pose it as a question.
 
I was not highlighting something of interest. I am serious about getting a method of stating one formula, for an entire column.
 
You must log in or register to reply here.
Back
Top