PDA

View Full Version : Help Needed! Automatic incremental replication of forumlas



trudymcd
03-02-2006, 09:37 PM
I hope I described in the tile what my problem is, but if not perhaps the following will give you a better idea.

Copying examples given me months ago by Dreamboat (what a life saver), I managed to create a database that meets the needs of the church. At the time, I stumbled on a way to highlight many rows of a column and by holding down some keys with the Enter Key, the formula was incrementally copied to the highlighted cells. I run into problems when making new entries in the database that are now below the rows where the formulas were/are. I don't know how to repeat what I did before. Further, if I insert a row within the other rows containing data, the formulas are not present.

I know that Dreamboat tried to set it up so that if I pressed Enter or maybe some macro button after entering data, the formula's were supposed to be automatically carried to the row below. I could never get that to work, and as I said above, I stumbled on something that worked in reading the Excel Help. I keep thinking I have found the same instructions, but as it does not work, clearly it is not the same or there was some step I no longer remember that I need to do before that. The formulas are written below with explanations of what they do. This is a database of names, addresses and other information, used to create different mailings according to different sort criteria not included in the formulas.

Formula of Mail_List column: =IF(ISBLANK(I2),E2&" "&F2,IF(I2=F2,E2&" and "&H2&" "&I2,E2&" "&F2&" and "&H2&" "&I2))

If there is no name in Column I (Last_Name2), the name of the individual is input as E2 F2 (First_Name1) (Last_Name1), However if column I is not blank and is the same as column F then couple's name is entered as (First_Name1) "and" (First_Name2) (Last_name2); however if the last names are not the same, then the couple's name is input as First_Name1) (Last_Name2) "and" (First_Name2) (Last_Name2)

The following two formula's are using the same name information for a different publication.
Formula of Directory Part1 column: =F2&","
Formula of Directory Part2 column: =IF(ISBLANK(I2),E2,IF(I2=F2,E2&" and "&H2,E2&" and "&H2&" "&I2))

Thank you,
Trudy

Jacob Hilderbrand
03-02-2006, 09:47 PM
If you want to fill the formula down rows or across columns, it will auto increment so long as the references are relative.

If you want a column and or row to increment just write the address as normal (i.e. A1). If you want it to not increment use $ to freeze the column and or row (i.e. $A1, A$1, & $A$1).

This can be useful if you want some references to increment and some to stay the same.

Now to fill the values, just select the cell and put the cursor to the lower right corner of the cell. The mouse will change appearance (to a + symbol by default). Left click, hold the button down, and drag the mouse up or down the rows or left or right across the columns until you get to the last cell that you want. The release the mouse button.

trudymcd
03-03-2006, 12:34 PM
<img> SOLVED! Jake, thank you so much and best wishes to all of you who are there to lend a hand to those of us who know so little.

Trudy

trudymcd
03-03-2006, 12:54 PM
Okay, how does one use Thread Tools to indicate a problem is solved. Obiously, writing Solved in my reply was not the way to do it.
<img> Trudy

trudymcd
03-03-2006, 12:55 PM
Also, why don't my smilies show?

mdmackillop
03-03-2006, 02:44 PM
:dunno , but you'll find the solved button in the Thread Tools drop down at the head of the page.
Regards
MD

Cyberdude
03-04-2006, 02:23 PM
Malcolm, the "Solved" problem still isn't solved, at least it isn't on my displays. It's not in the list.
Trudy, I can't find it either. :motz2:

mdmackillop
03-04-2006, 05:22 PM
Another one for Jake I think.:think: