autofill in EXCEL

[kalos]

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]

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]

I'm assuming you already have a table of data somewhere where "word", "word1", "word2" etc, are already existing as rows (or columns),

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"

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.

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]

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)

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]

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]

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:

and here is what should be filled automaticaly:

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]

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]

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

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]

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]

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?

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.imageshack.us/img167/5548/022312.png
http://img149.imageshack.us/img149/6899/022406.png

[dluby]

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

[katykaty]

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

[kalos]

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]

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

[attach]

[kalos]

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:

[AndyM]

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]

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]

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".

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]

that's sweet, it worked

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

[Target]

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