Solved How to add a function to prexisting excel doc /multi entries

Microsoft Office excel 2007
January 27, 2012 at 10:46:17
Specs: Windows XP
I am in the process of updating a document that already has over 500 records in it. Up to this point, every field has been entered manually with no use of function codes. I would like to make the document easier to maintain in the future by someone with NO excel experience at all. One of the fields is supposed to ONLY display a Month, based on another field with a date in it. I found a solution on how to do that here.

How do I apply this formula to the preexisting entries that I have, or must I enter it manually for every field?

=CHOOSE(MONTH(D1),"Jan","Feb","Mar","Apr","May", etc.)


See More: How to add a function to prexisting excel doc /multi entries

Report •


#1
January 27, 2012 at 13:06:39
✔ Best Answer
First, let's get some terminology straight so that there is no confusion.

re: Fields

In an Excel spreadsheet, they are called "cells", not "fields".

re: Function Codes

An Excel Function is an instruction to Excel to perform some built in task or calculation.

SUM, VLOOKUP, CHOOSE, MONTH, etc are functions.

A Formula is what a user enters into a cell. It will usually consist of 1 or more functions and will start with an equals sign.

"Code" is usually reserved those times when we're discussing macros, i.e. VBA Code.

Your example is typically called a formula, which in this case consists of 2 functions and a few arguments and it will be placed in a cell.

=CHOOSE(MONTH(D1),"Jan","Feb","Mar","Apr","May", etc.)

OK, now to answer your question...

Whether you need to enter this manually or via autofill will depend on where the formula needs to go.

Your example references D1. If you put that formula in a cell and the drag it down, the D1 will increment for each row, becoming D2, D3, etc.

If you drag it across to the right, the D1 will increment for each column, becoming E1, F1, etc. If you drag it to the right, D1 will become C1, B1, etc.

If you Copy/Paste the formula by copying the cell that the formula resides in (not the formula itself) it will change D1 by a row and column offset based on how far you are from the original cell. (Play with that to see what I mean).

So, we can't answer your question directly without knowing where these "preexisting entries" are. With more detail, maybe we could or maybe I've provided enough information here to help you accomplish your task.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 27, 2012 at 14:14:58
Thank you for the correction in terminology. My last MS Office class was in high school- it's been about 10 years.

I have an excel document of individuals, dates they have completed a training course, as well as their emails. Before I got to it, this was entered as a default excel doc, with no tables set up, and no association between cells.

I am able to sort by name, date, etc. However, the folks I am updating this for want to keep a column(what I was calling a field) for "Month due"- that way they can hide the individual dates, and just print out all of January, for example, and keep a hardcopy record. Personally, I feel it's easier to just get rid of that column entirely as it is redundant.

I am tracking over 500 individuals already, and wanted a way to pull the "date completed" for each person, and autofill the "Month Due" column with only the month to the cells that already exist. As the cells in column E will always be the same as the month completed in column D, it makes more sense to me to just create a formula to autofill, rather than manually typing out the month.

From a database perspective, I have several records already. I want Field E to be autofilled by the date in Field D, for every record. I wanted a way to apply the formula I referenced to every field at once, instead of going through each record one at a time.

I hope what I am trying to say comes across better, and I apologize for any confusion. Thank you for all your help already, it is VERY much appreciated.


Report •

#3
January 27, 2012 at 15:40:04
re: Thank you for the correction in terminology.
re: I want Field E to be autofilled by the date in Field D, for every record.

If you read my lesson in terminology why are you still referring to cells as fields?

Did you read the part of my post where I said this:

Your example references D1. If you put that formula in a cell and the drag it down, the D1 will increment for each row, becoming D2, D3, etc.

Did you try that?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 27, 2012 at 20:45:22
"From a database perspective, I have several records already. I want Field E to be autofilled by the date in Field D, for every record.

However, the folks I am updating this for want to keep a column(what I was calling a field) for "Month due""

If you read my lesson in terminology why are you still referring to cells as fields?

What is a database field?

== a databasefield is a certain part of a data base e.g d.o.b, hobbys ==

it is a part of the database e.g the name or surname or the address

A field is essentially the columns of the table on the database.

Read more: http://wiki.answers.com/Q/What_is_a...


Not to nitpick here, I do appreciate the terminology lesson as it applies to excel. However, in database terminology, as you can see, a field is equivalent to a column in Excel.


Doing as you suggested worked just fine. Thank you very much for all of your help.


Report •


Ask Question