Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to define certain things in excel like when I type some code(ex 112) in a new sheet, it will automatically import data from a separate sheet and display the contents of it in other columns.

FOR EX

112    agarwal male
119    reena   female

That is, when i type 112 in a column, it will automatically display agarwal and male in the same row but in different columns(The whole data from where it will import is present in an another sheet).

share|improve this question
1  
Sounds like you need a database, not a spreadsheet. –  Kruug Feb 13 '13 at 18:21
    
I want to create a database and then want to use it in excel –  neeraj Feb 13 '13 at 18:29
    
yes , from a seperate dedicated excel sheet –  neeraj Feb 13 '13 at 18:31
    
Are you familiar with VLOOKUP ? –  F106dart Feb 13 '13 at 20:06

3 Answers 3

Use of the IF statement in a formula will do what you seek. The IF function checks for a logical value (true or false) then takes action on that result.

IF(logical_test, [value_if_true], [value_if_false])

So, by having it check the "code" it will know what values to place in the cells of other columns. If you are typing the "code" in A1, then put this formula in B1

=IF(A1=112,"agarwal",IF(A1=119,"reena","")) 

Place this formula in C1

=IF(A1=112,"male",IF(A1=119,"female",""))

As you can see, the first one checks for the code 112, if it's TRUE it places the word "agarwal" in the cell. If it is False it goes to the next instructions which happen to be nested IF statement. At the end of the statement it will leave the cell blank if neither returns TRUE.

You can copy the formulas down the rows by double clicking the lower right corner.

share|improve this answer
    
thanks for your suggestion.Actually i am having a separate sheet which contains all the data.What I want is if i write the first value(ex 112) in another separate sheet, i want it to automatically import all that data from that sheet. –  neeraj Feb 13 '13 at 18:44
    
You may want to edit your question with those details. My answers was based on the question you asked. It is important to include as much detail as possible when asking your questions. I will update my answer accordingly. –  CharlieRB Feb 13 '13 at 18:47

Here is a VBA solution as short as possible.
This means typical things like Option explicit, Application.ScreenUpdating and declaring variables are left out

How it works

Select the range of cells you want to expand and press F3
enter image description here

How to implement

Open your Excel and VBA editor with Alt+F11

Paste this code into your "ThisWorkbook" window (It must be there or it won't work). This registers a keyboard shortcut as trigger event - in our case F3.
You can change this to whatever you want. Even combinations with Shift, Alt or Win are possible.

Private Sub Workbook_Open()
    Application.OnKey "{F3}", "autopopulate"
End Sub

After that, insert a new module and paste the following code there

Private Function autopopulate()

Set data = ThisWorkbook.Sheets(1).Columns(1)

For Each cell In Selection

    On Error Resume Next
    Set search = data.Find(what:=cell, lookat:=xlWhole)
    On Error GoTo 0

    If Not search Is Nothing And cell.Value <> vbNullString Then
        Set lastCol = Cells(search.Row, Columns.Count).End(xlToLeft)
        Range(search, lastCol).Copy
        cell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If

    Set lineend = Nothing
    Set search = Nothing

Next
End Function

Set data = ThisWorkbook.Sheets(1).Columns(1) defines your data range with all your predefined values. Change it to whatever you need. For example .Sheets("My data sheet") or .Columns(20).

Save the workbook and re-open it so the shortcut can be registered.
Now you can type any number and use F3 to populate values right to it

Note

My example code assumes that your data is in sheet1 and all numbers are in column A. You have to change this eventually. Before you ask further questions how to implement, please have a look at my test workbook.

share|improve this answer

Using just excel and formula's, no VBA etc (EDIT - this will only work in Excel 2007 or later)...

In a document called Book1.xls you have the following table containing your base information - NO FORMULAS:

enter image description here

Then in a document called Book2.xls you have the table you want to auto update.

In Cell A1 you put the number you are searching for (in this case 112). In Cell B1 place the following code:

=IFERROR(VLOOKUP(A1;[Book1.xlsx]Sheet1!$A$1:$C$2;2;0);"what")

This will look up the contents of Cell A1 in the area between A1 and C2 in Sheet1 of document Book1.xlsx (note the documents in this case need to be in the same folder) and if it finds them it will display the contents of the cell with col index = 2 (the col index starts at number 1 at the first column in your array)

In this case your array is the contents from A1 to C2 but the information you are looking for will always be in Column A so col index = 1 is Column A, col index = 2 is Column B and col index = 3 is Column C.

In Cell C1 replace the last 2 in the above formual with a 3:

=IFERROR(VLOOKUP(A1;[Book1.xlsx]Sheet1!$A$1:$C$2;3;0);"")

Now, enter a number in Cell A1 in your sheet and if it matches a number somewhere in the array A1 to C2 in your reference document then the contents of cells B1 and C1 will automatically be updated with the name and sex of the matched item.

share|improve this answer
1  
A solution without VBA is of course always better, but let me kindly add two notes: =IFERROR isn't available in Excel 2003 and the OP forgot to mention which version he is using. Also you need to copy the formula around to every column you want to populate. –  nixda Feb 14 '13 at 10:14
    
TY for your comment, I was not aware IFERROR was not available pre-2007 but you are correct, I have edited my answer to indicate this. –  Kevin Anthony Oppegaard Rose Feb 14 '13 at 12:10
    
@nixda IFERROR may be replaced by IF(ISERROR(...)) combination for earlier versions. –  Peter L. Feb 14 '13 at 15:29
1  
Thank you very much Kevin...it is working and my problem is solved...:) –  neeraj Feb 14 '13 at 17:11
    
@neeraj You should mark the answer which solved your problem as accepted with a green check mark –  nixda Jul 11 '13 at 20:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.