General discussion

Locked

Trouble linking fields in Excel 97

By Shanghai Sam ·
Hi, I have got a really complex problem here and I don't know if this can be solved. I will go through the scenario. There are two files, lets say file A(the source file) and file B(update file). Now there are two fields which are linked and whenever information is entered on file B, it will pull up information from file A and enter it on file B. Now this is the difficult bit. People want to enter information differently on file B. i.e the field they are linking to on file A has no dashes in the entry i.e id no= 1234567 and the field they are linking from file B has dashes i.e id no = 123-45-67. Now is there a way of linking these fields together albeit they have the same information but one of the fields has dashes in them. Someone pleasehelp or just tell me it is impossible. Cheers Dave

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Trouble linking fields in Excel 97

by aisling In reply to Trouble linking fields in ...

1) if the dashes in the data entry field are always in the same place - put a custom format on that field so that users don't have to enter the dashes to see "123-45-67" this way the dashes don't form part of the data entered and can easily be linked back to file A

2) if not, have another column to eliminate dashes using the MID function (returning specified characters in text) and FIND function (to find the dashes and eliminate these characters from the MID function) This is a lot more complicated that option 1!

Good Luck

Collapse -

Trouble linking fields in Excel 97

by l_lk In reply to Trouble linking fields in ...

Hi, I assume that all id no have same lenght,e.g. 7 digit, and you want to have two dash after 3rd & 5th digit, you can set the "cell format" of file B by right click of you mouse and choose self define, and add a new format as : ###-##-##
and you will found that the content of the field will become 123-45-67, but if you only input 6 digit at file A (e.g. 123456), at file B, you will get a result : 12-34-56.(it will count from right to left, therefore if the digit different, it will present diff feature)

Back to Software Forum
2 total posts (Page 1 of 1)  

Software Forums