Author Topic: autofill in EXCEL (Read 14868 times)

kalos · « **on:** April 30, 2009, 03:17 PM »

hello

I would like to do this in MS EXCEL:

when I type a specific "word" in a column named "column" to automatically write in the same line: "word1" in "column1", "word2" in "column2" etc (in other words to autofill the other cells of the line with specific data for each collumn)

can you tell me please how to do this?

thanks

David1904 · « **Reply #1 on:** April 30, 2009, 03:57 PM »

I'm assuming you already have a table of data somewhere where "word", "word1", "word2" etc, are already existing as rows (or columns), and that what you want to do is to have the related items filled in whenever you choose a particular instance of "word".
If this is the case, then using VLOOKUP or HLOOKUP would probably be you answer.

If this is not the case you may need to supply more details so we can get a better understanding of your requirements.

David

kalos · « **Reply #2 on:** April 30, 2009, 04:26 PM »

I'm assuming you already have a table of data somewhere where "word", "word1", "word2" etc, are already existing as rows (or columns),
-David1904 (April 30, 2009, 03:57 PM)

nope, I don't have the "word", "word1", "word2" etc stored in an excel file, but I can do this if it is needed
also, please note that the "word", "word1", "word2" etc are specific data, not variables

what you want to do is to have the related items filled in whenever you choose a particular instance of "word"
-David1904 (April 30, 2009, 03:57 PM)

what I want is to have the related data automatically filled ("word1" in "column1", "word2" in "column2", etc, in the line I typed the "trigger" "word")
the trigger event that will trigger the autofill of data would be to write "word" in a specific column named "column" (but if that's too hard, let's just say to only write the "word" in any column)

If this is the case, then using VLOOKUP or HLOOKUP would probably be you answer.
-David1904 (April 30, 2009, 03:57 PM)

not sure about those functions, by I would suppose that a macro could do what I need? just by resembling what I want to do with some macros in office that autowrite "by the way" after you type btw or something...

tranglos · « **Reply #3 on:** April 30, 2009, 05:46 PM »

hello

I would like to do this in MS EXCEL:

when I type a specific "word" in a column named "column" to automatically write in the same line: "word1" in "column1", "word2" in "column2" etc (in other words to autofill the other cells of the line with specific data for each collumn)
-kalos (April 30, 2009, 03:17 PM)

I'm not sure this will help you do what you want, but try this: type "word1" in a cell. Click this cell. There will be a tiny black square in the bottom right corner. Click this square (this is a little fiddly - the mouse cursor should change to a small black plus sign), then drag down, as far as you wish. Excel will fill the column cells with "word2", "word3", etc. If you drag to the right, Excel will likewise fill the row.

This works not only for numbers, but also for days of the week, months, and any lists you define manually.

Is this anywhere near what you need? It does need manual dragging; it won't happen automatically as-you-type, though. For automatic behavior, you would need a formula, but I cannot find anything that looks like the "autoincrement" described above in the list of formulas in Excel 2003.

Target · « **Reply #4 on:** April 30, 2009, 06:11 PM »

do you want the same entry across the row, like this?

Row1 col1 = word1, col2 = word1, col3 = word1
Row2 col1 = word2, col2 = word2, col3 = word2

if so, simply put the formula =$a1 in each of the columns, then fill down the requisite number of rows

kalos · « **Reply #5 on:** April 30, 2009, 06:35 PM »

mm, not sure I have made my self clear enough, since I don't think these suggestions fit what I need to accomplish, so I post an example:

here is what I type manually:

http://img167.imageshack.us/img167/5548/022312.png

autofill in EXCEL

and here is what should be filled automaticaly:

http://img149.imageshack.us/img149/6899/022406.png

autofill in EXCEL

ofcourse I need somehow to specify that I whenever I type "SMITH" in the second cell of a row, to fill the first cell of the row with "JACK", the third with "42" and the fourth with "981571", I suppose this can be done with a macro or maybe VLOOKUP or something, this is what I am asking

Target · « **Reply #6 on:** April 30, 2009, 06:40 PM »

all done with lookups, however you have to already have all the info in another table

and if you already have the info in another table...

tranglos · « **Reply #7 on:** May 01, 2009, 05:19 AM »

ofcourse I need somehow to specify that I whenever I type "SMITH" in the second cell of a row, to fill the first cell of the row with "JACK", the third with "42" and the fourth with "981571", I suppose this can be done with a macro or maybe VLOOKUP or something, this is what I am asking
-kalos (April 30, 2009, 06:35 PM)

What Target said. It looks as though you'll need to enter all the data somewhere, at least once. One way of doing this would be to enter it all manually in one Excel file (or sheet), then copy-paste or use formulas to get the data. It might quickly become tedious though: it's probably easier to type "Smith" than to look up the cell address and type =g14, and the latter is more prone to hard-to-detect errors (e.g. if you type =f14 instead).

You might be better off with a database rather than a spreadhseet. I don't know enough about Access to be much help, but as a general principle, if one of the data points (e.g. last name) is unique, it would be sufficient to get the remaining information for each row. If no single field is unique, you'd have to enter at least two (or more) fields to identify a record; then the db would be able to fill in the remaining fields. But that seems to require a custom-written program, or perhaps some SQL scripting in Access would do.

dluby · « **Reply #8 on:** May 01, 2009, 07:57 AM »

I can't see the sample images from kalos, all I see is the broken image link! Have you any ideas what's wrong here?

tranglos · « **Reply #9 on:** May 01, 2009, 10:15 AM »

I can't see the sample images from kalos, all I see is the broken image link! Have you any ideas what's wrong here?
-dluby (May 01, 2009, 07:57 AM)

The images display fine for me. If your browser doesn't show them, perhaps it cannot reach imageshack servers for some reason. The URLs are

http://img167.images...g167/5548/022312.png
http://img149.images...g149/6899/022406.png

dluby · « **Reply #10 on:** May 01, 2009, 10:18 AM »

You were correct, our strict firewall has blocked access to Imageshack

katykaty · « **Reply #11 on:** May 01, 2009, 11:52 AM »

First thing that springs to mind is HLOOKUP - because presumably your source data will be horizontal rows by name?

kalos · « **Reply #12 on:** May 01, 2009, 02:52 PM »

thanks
is it easy for anyone to post what I should write in excel to do this? I am not familiar at all with LOOKUP commands

David1904 · « **Reply #13 on:** May 01, 2009, 04:04 PM »

Here is an example of using vlookup.
You need to create the list of data first - sort by the first field (the one you will be using for reference), then name it (Alt/insert/name/define)
Use insert function and fill in the boxes.

Here is an image (I hope!) that shows the end result. Use Ctrl` (Control grave) to toggle between showing formula and text

kalos · « **Reply #14 on:** May 02, 2009, 05:35 PM »

mm, I entered all that text in an excel, but what should I short exactly? list F?
also, what should I name exactly and how?

I get this error:

http://img5.imageshack.us/img5/3484/012006.png

autofill in EXCEL

AndyM · « **Reply #15 on:** May 02, 2009, 09:38 PM »

You need to have a table someplace in a worksheet that has all the info you will later want to reference.

You then reference that table in your lookup formula.

If you want "Jack", "42", and "981571" to appear when you type "Smith", then "Jack", "42", and "981571" all has to be in a table someplace to refer to.

If the info in Excel's help files isn't clear, a google search will list a multitude of primers, examples, and explanations on vlookup, hlookup, index, match, etc. (all methods of locating data in a table).

tsaint · « **Reply #16 on:** May 02, 2009, 11:09 PM »

Kalos,
your error (I guess) is because when you copied the example formulae, you didn't set up "Datalist". In your screenshot, it looks like excel doesn't know what "Datalist" is.
You need to highlight the cells which make up your table of data (F2:I4 in the example) and then name that cell range with the name "Datalist".
Then the formulae in the example should work.
How probably depends on your version of excel. In mine, I highlight the range of cells, right click, then select "name a range"
tony

Target · « **Reply #17 on:** May 05, 2009, 06:32 PM »

You need to highlight the cells which make up your table of data (F2:I4 in the example) and then name that cell range with the name "Datalist".
-tsaint (May 02, 2009, 11:09 PM)

not actually necessary, you can reference the range directly, eg in cell A2 th formula would be =vlookup(B2,$f$1:$I$4,2,false)

Note that the range reference needs to be absolute or else the range will move as you autofill (that's what the dollar signs are for)

kalos · « **Reply #18 on:** May 05, 2009, 07:22 PM »

that's sweet, it worked

however it appears #N/A in the fields, is there any way to 'hide' it?

Target · « **Reply #19 on:** May 05, 2009, 08:09 PM »

formula changes to =if(iserror(vlookup(B2,$f$1:$I$4,2,false)),"" =vlookup(B2,$f$1:$I$4,2,false))