pasting data into a choice column

[16Tons]

I am trying to cut and paste data from an excel sheet into a sharepoint 2007 list. The data in excel is has multiple entries in each cell (eg: left, right, up, down). I would like to paste it into cells that have been formated for multi-choice checkboxes so that the ckeckboxes are checked automatically depending on what is in the original cell.

I have a lot of data that I need to convert from the excel sheet into sharepoint, so clicking the checkboxes by hand is not feasable. Is there a easy way to do this? I have tried everything I can think of.

Thanks in advance!

 

[MaxEd]

Hvae you tried creating a link to Sharepoint from Excel and doing all the updates in excel then syncing it up with Sharepoint?

-Laughter works miracles.

 

[16Tons]

Thanks for the response!

I tried your suggestion and have been playing with it all morning but have been unable to get it to work still. I can link the spreadsheet and sharepoint list together without any problem, but I havent been able to get it to populate a multi choice field.

I am working with excel 2003. I dont know if that might have something to do with it. If you have any other suggestions, I would really appreciate it!

Thanks again!

 

[MaxEd]

I'm not sure how to handle drop downs you might play with the excel spreadsheet to see if you can get them to work. I haven't done much with the spreadsheets.

-Laughter works miracles.

 

[sahrepiont]

This is a funny with the WSS3/MOSS2007 and the nature of lists. I had to get a number of Excel sheets into a number of lists. it turned out to be the 'required felds in the lists that caused to the problem. Hint: try to make the import (if you have to do it useing data from an Excel spread sheet) as simple as poss, no complicated Excel formulas or references!

Rgds

Sahrepoint man

 

[16Tons]

I figured it out!!

Ok, so here is how you take excel data and make it populate to a multichoice field:

put all of the entries that you want in the multichoice list in one column in the excel sheet deliniated by ;# in front of each and then another ;# at the end of the list.

Example:

The checkbox list in sharepoint will be:
Option 1
Option 2
Option 3
Option 4

In the excel sheet, list the entries like this:
;#option 1;#option 2;#option 3;#option 4;#

paste this into the sharepoint list and be sure that the sharepoint column you paste it into is formatted as single line text. Once the data has been pasted in, then format the sharepoint column as a multichoice checkbox.

Note: it doesnt matter which order you make the options or whether they match the order of the inputted list. If you input:
Option 1
Option 3
Option 4
Optoin 2
when you were formatting the sharepoint column, this would be the order of the list when viewed in sharepoint, but each checkbox would still be filled in.

There is only one problem that I cannot figure out. when the whole process is finished, if there was no data in a given cell in the newly formatted checkbox column, a ; would be in the cell. I cannot sort by it nor will the column filter by it or by blanks so I cannot issolate it. the only way I have found to get rid of it is to open the checkbox dropdown in each cell and click ok. if anyone has a suggestion on how I can prevent this from happening or how to get rid of it en masse, I would appreciate it.

I hope this makes sense and I hope you find it useful.