Home |
Search |
Today's Posts |
#1
|
|||
|
|||
hiding all data in a sheet except desired
Hello All,
I have a worksheet that has approx 20 different categories (columns) and then 5609 rows (1 for each employee). Is there a way for me to setup another sheet (while hiding the original or similiar) then on 2nd sheet have a lookup field by Employee Last Name & First Name - and if it finds a row that matches will display the desired information on this 2nd sheet? I realize that access would be better suited for this, but given time and the amount of records that isn't feasible for me currently. TIA, _Bigred |
#2
|
|||
|
|||
Hi
you could probably use VLOOKUP for this the way i would approach it (based on the info provided) is to create a dynamic range name for the 5609 employee names http://www.contextures.com/xlNames01.html#Dynamic and another dynamic range name for all of the 20 columns of information on this sheet on the second worksheet i would use the combo box control off the control toolbox toolbar to create a drop down list of these names - put the control on the worksheet - right mouse click on it and choose properties choose List Fill Range and type the name of the dynamic range you created of the employee names also link this combo box to the cell it is over - under properties choose Linked Cell and type the reference of the cell it is over in then close the properties box and click on the exit design mode icon on the control toolbox toolbar. now in the adjacent cell where you want the first of the 19 columns to be displayed type the following formula =VLOOKUP($A$1,All_Info,column(),0) where A1 is the "linked cell" reference of the combo box and All_Info is the name you gave to the dynamic range of the whole table (ie the 20 columns) and column() here assumes that this formula is being typed in column B, if it's being typed in column C change this parameter to column()-1 if column D column()-2 etc this formula can then be dragged to fill across the row for the 19 columns. now when you choose a name from the drop down box in A1 all the related information should be filled in for you. Hope this makes sense Cheers JulieD "_Bigred" wrote in message ... Hello All, I have a worksheet that has approx 20 different categories (columns) and then 5609 rows (1 for each employee). Is there a way for me to setup another sheet (while hiding the original or similiar) then on 2nd sheet have a lookup field by Employee Last Name & First Name - and if it finds a row that matches will display the desired information on this 2nd sheet? I realize that access would be better suited for this, but given time and the amount of records that isn't feasible for me currently. TIA, _Bigred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merger Two Data Sheet | Excel Discussion (Misc queries) | |||
Transfer data from sheet to sheet | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Automatic cell increment with data from sheet 1 to sheet 2 | Excel Worksheet Functions | |||
pull data from sheet two, then fill in the data to sheet one (part | Excel Worksheet Functions |