Saturday, December 10, 2011

Auto-fill forms from data in Excel - Challenge Accepted!

Recently a co-worker asked me if it was possible to have cells in Excel auto-fill from data records on another excel sheet without programming. Similar to the auto-fill forms you see in your browser, where you enter your name and the browser automatically fills the rest of your information, like address or phone number.

So you'll go from this:






To this with just a click:






I had no idea if this was possible, I don't exactly remember being taught this at school. So I figured I look into it. Challenge Accepted! After many a Google search I found nothing on this. So if there is a function for it, I haven't found it. So after about an hour of playing around with Excel functions, I came up with this:

=IF(ISERROR(INDIRECT(ADDRESS(MATCH(A,B,0),C,4,TRUE,"SHEET"))),"",INDIRECT(ADDRESS(MATCH(A,B,0),C,4,TRUE,"SHEET")))

SHEET = Name of the sheet containing the data
A = Cell containing data to compare to SHEET. (A1, B2, etc.)
B = Name of array holding the data on SHEET.
C = Column holding Data.

Here a sample if you want to see it work. temp.xlsx It simply looks at the Name column cell and compares it to a list in a already filled out table on SHEET, and prints out whatever is in C. The IF and ISERROR function are used to keep the cell clear in case of an error, makes it look prettier. Since leaving the Name cell empty would return an error.




Subscribe
Google Reader or Homepage Subscribe

1 comment:

  1. Hello!!
    I just wanted to say THANK YOU.
    the explainantion and the excel file helped me a lot to do an autofill in a format I use at work.

    Though i need to mention that you missed to mention that you used validation data to do the list of data we needed to show. but that is it.. i knew that because i actually play with excel and i really like to make my life easier with all the formulas. I dont know them all but this is a start.

    Thank you very much

    Lilie

    ReplyDelete