I am working on importing bunch of student data onto a server. I got an excel sheet from the secretary with all of the information I would need to create user names and passwords.
There originally were 5 columns that had information like first name, last name, grade, et cetera.
I wrote a formula to combine to columns, and I wrote another formula to make everything lowercase. I am left with 7 columns.
I only need two of the columns. When I delete a column, it changes the parameters for the formula and I get an "ref#" in the cell.
When I copy and paste the two columns into a new book, I get the same error. Is there anyway I can trim down my document to have just the essentials so I can try importing them into the server?
Essentially I want to copy the text in the cells--not the formula. Thanks all you Excel-Fu Masters!!!
Edit: the two formulas I used where =B2&""&C2 and =LOWER(F3)
Do I need to add absolute values somewhere? My excel is not that sharp these days.
Posted by BaoQingTian (Member # 8775) on :
Just make a new sheet and:
Copy.....Paste Special.....Cell contents.
Posted by lem (Member # 6914) on :
You are the Master. It is all about the basics. I can't believe I forgot how to do that!
Posted by Dagonee (Member # 5818) on :
Alternatively, if you want the formulas to still be formulas, just highlight the columns you don't want and choose "Hide" from the menu.
This is less useful in concatenation scenarios like yours than in mathy formulas where you might want to adjust them later.
Posted by cmc (Member # 9549) on :
I'm so way late on this - but you can also create the a new sheet in your workbook, click on the cell you want to start your column in and then just type '=' and go to the original sheet and click on the cell you want it to reference...
Posted by lem (Member # 6914) on :
Here is a follow-up question (afer an explanation).
The import/export function is not working on my server. it is an NT4.0--old old old. The school is not going to invest in a new server for at least 2 years.
I have tried everything last year to get the import/export to work. I have read, studied, and invited people with much more experience then me to come and take a look at it. No one could fix it or get it to work. Not even our head district technology guy.
Last year I just copied and pasted from the excel sheet to the User Management to create each profile. One at a time. Copy and paste. Username and Password.
Click click click. Ughh....
Someone suggested today that there is a way in excel to create a Macro where I can use the keyboard to: copy--paste--macro to extract data from next row--past--macro to extract data from next column--paste--macro to extract data from next row...et cetera.
Is that possible? If so, can someone point me to a page where I can research that out? Bouncing back and forth between programs gets old after a couple hundred students.
Or do I have to still be in Excel for the Macros to work?
[ August 17, 2006, 03:53 PM: Message edited by: lem ]
Posted by BaoQingTian (Member # 8775) on :
You can record a macro. Just do an Excel macro search online. I really haven't found any excellent Excel resources or I'd point you to one. Another option is to write a VBA script. I wrote my first one about a month ago when I was dealing with about 30,000 items and doing it manually was not an option. Tom Davidson suggested I use a VB script. I was worried, but if you've had any c++/java/php etc experiences it's pretty easy. I think my thread is probably still around if you want to use the VBA script for reference to see what one looks like.
If it's too daunting for you, it's definately something your head IT guy should be able to put together in about 15 minutes, it sounds pretty simple. Then you could just run the script once and you wouldn't have to keep hitting the shortcut key for every student like you would with most macros.
Posted by Icarus (Member # 3162) on :
New Question:
When making a scatter plot, is there any way to make it indicate that a given pair of values has more than one point? That is, if (3,12) is one pair, Excel puts a dot there. If (8,23) and (8,23) are both points in the data, Excel puts a dot at those coordinates. Is there any way to indicate to the viewer of the plot that there are in fact two pairs represented by that point? Like, having points of different colors?
Posted by TheGrimace (Member # 9178) on :
If they're from different data series you can always have another shape for that series, but I get the feeling you're talking about something like multiple people getting the same grade in the same series, in which case I don't think there's a simple way to do it. (but I could definately be wrong)
Posted by Primal Curve (Member # 3587) on :
I'm trying to figure out how something works on an Excel character sheet I've been playing around with. I've figure all the formulas and things out so that I can start playing with it and modifying things, but I cannot figure one thing out.
There is absolutely no VB on this spreadsheet. Everything is done with built in functions and by using extra sheets. It's actually quite nice that I don't have to tinker with someone's code to get it to bend to my will.
However, somehow or another, the creator of the spreadsheet added a combo box to several cells. It doesn't appear to be the VB combo box, but some kind of function of excel I'm not familiar with.
I can email you the spreadsheet so you can check it out. It's completely baffling me.
Posted by Primal Curve (Member # 3587) on :
Gah. I just figured it out. I guess I was lazy with the Help file.
Here's what I got:
quote:
Type the entries for the dropdown list in a single column or row. Do not include blank cells in the list. If you type the list on a different worksheet from the data entry cell, define a name for the list. If you type the list in a different workbook, define a name with an external reference to the list.
Select the cell where you want the dropdown list.
On the Data menu, click Validation, and then click the Settings tab.
In the Allow box, click List.
If the list is in the same worksheet, enter a reference to your list in the Source box. If the list is elsewhere, enter the name you defined for your list in the Source box. Make sure the reference or name is preceded with an equal sign (=).
Make sure the In-cell dropdown check box is selected.
Specify whether the cell can be left blank: Select or clear the Ignore blank check box.
To display optional input instructions when the cell is clicked, click the Input Message tab, make sure the Show input message when cell is selected check box is selected, and then fill in the title and text for the message.
Specify how you want Microsoft Excel to respond when invalid data is entered.
![[Hail]](graemlins/hail.gif)
![[Grumble]](graemlins/grumble.gif)