Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell value from another worksheet
Hi All
I have one worksheet called "Main" which contains instrument details and recalibration due dates. Individual instrument details are called up to include location code of where they are in the country, from a combo box and displayed in 6 cells. The second worksheet called "Depfind", Containes all the addreses and Telephone numbers of the relevent location codes, eg ab,cd,123. etc. Again a combo box is used to bring up the contact details on a location code typed in the box. What I am trying to do is instead of typing the location code every time as there are over 500. Have the location cell on the main worksheet linked to a cell on the depfind that will call up and display the contact information each time. There are about 3000 records in the main worksheet with many types of insruments at the same location. I have tryed all sorts, INDEX,VLOOKUP,Etc Cant seem to get anything to work.Access does this perfectly by using columns from the combo box. Anyone have any ideas. Thanks in advance Exelplate |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell value from another worksheet
Have the location cell on the main worksheet linked to a cell
on the depfind that will call up and display the contact information each time. Some thoughts .. perhaps we could use hyperlinks .. A sample construct is available at: http://www.savefile.com/files/3059257 AutoHyperlink_Excelplate_wks.xls Assume you have this set-up In Main, cols A & B, data from row2 down ------------ Instr# Recalib_DD 1111 Date1 1112 Date2 1113 Date3 1114 Date4 1115 Date5 etc In Depfind -------- Instr# are listed in col A & Location codes are in col D (col D is the lookup col), with data from say, row4 down: Instr# Field1 Field2 Location Code 1114 Data1 Data11 1 1115 Data2 Data12 2 1113 Data3 Data13 3 1111 Data4 Data14 4 1112 Data5 Data15 5 etc Note that the Instr#s above (in Depfind) are intentionally scrambled to be different from the order in Main. This is for illustration purposes when we check that clicking on the hyperlinks we're going to create in Main does jump to the correct cells in Depfind. In Main --------- Put a label in C1: Location Code Put in C2: =HYPERLINK("#"&CELL("address", INDIRECT("'Depfind'!D"&MATCH(A2,Depfind!A:A,0))),I NDIRECT("'Depfind'!D"&MATC H(A2,Depfind!A:A,0))) Copy C2 down This will create hyperlinks in C2, C3, etc which extract/display the location codes (matching the Instr#) from Depfind as the friendly names in the cells. And when you click on the hyperlinks, they'll bring you to the correct cells in col D in Depfind (Location code col). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Excelplate" wrote in message oups.com... Hi All I have one worksheet called "Main" which contains instrument details and recalibration due dates. Individual instrument details are called up to include location code of where they are in the country, from a combo box and displayed in 6 cells. The second worksheet called "Depfind", Containes all the addreses and Telephone numbers of the relevent location codes, eg ab,cd,123. etc. Again a combo box is used to bring up the contact details on a location code typed in the box. What I am trying to do is instead of typing the location code every time as there are over 500. Have the location cell on the main worksheet linked to a cell on the depfind that will call up and display the contact information each time. There are about 3000 records in the main worksheet with many types of insruments at the same location. I have tryed all sorts, INDEX,VLOOKUP,Etc Cant seem to get anything to work.Access does this perfectly by using columns from the combo box. Anyone have any ideas. Thanks in advance Exelplate |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell value from another worksheet
Hi Max
And from Singapore WOW! I am from the UK Thank you so much for your input. I will try your recomendation tonight. In the mean time I put =t9 to call up the depot code and tried to work out how to reference that cells content eg "AB" IN the cell below to call up the address details below from worksheet depfind. One thing I did discover, because I think trying so many different combinations in cell t9 VLOOKUP, FIND etc, etc. I type in =t9 press enter and it returns =t9 the next cell to it returns the correct value strange Huh have you ever had that one? I will keep you posted through the group Best Regards Excelplate Excelplate wrote: Hi All I have one worksheet called "Main" which contains instrument details and recalibration due dates. Individual instrument details are called up to include location code of where they are in the country, from a combo box and displayed in 6 cells. The second worksheet called "Depfind", Containes all the addreses and Telephone numbers of the relevent location codes, eg ab,cd,123. etc. Again a combo box is used to bring up the contact details on a location code typed in the box. What I am trying to do is instead of typing the location code every time as there are over 500. Have the location cell on the main worksheet linked to a cell on the depfind that will call up and display the contact information each time. There are about 3000 records in the main worksheet with many types of insruments at the same location. I have tryed all sorts, INDEX,VLOOKUP,Etc Cant seem to get anything to work.Access does this perfectly by using columns from the combo box. Anyone have any ideas. Thanks in advance Exelplate |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell value from another worksheet
You're welcome, Excelplate ! .. I type in =t9 press enter and it returns =t9 the next cell to it returns the correct value strange .. Probably the cell was inadvertently formatted as TEXT before the formula was entered ? One way to restore is to re-format the cell as General/Number (via Format Cells), then re-enter the formula. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Excelplate" wrote in message oups.com... Hi Max And from Singapore WOW! I am from the UK Thank you so much for your input. I will try your recomendation tonight. In the mean time I put =t9 to call up the depot code and tried to work out how to reference that cells content eg "AB" IN the cell below to call up the address details below from worksheet depfind. One thing I did discover, because I think trying so many different combinations in cell t9 VLOOKUP, FIND etc, etc. I type in =t9 press enter and it returns =t9 the next cell to it returns the correct value strange Huh have you ever had that one? I will keep you posted through the group Best Regards Excelplate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to add cell comments in unlocked cell on protected worksheet | Excel Discussion (Misc queries) | |||
Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
Refrencing another cell in a worksheet that "could" exist | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |