I am using Excel 2003 and have created a spreadsheet in which one column has text. I have created a second spreadsheet and I am trying to use vlookup to bring in the text from spreadsheet number 1 to spreadsheet number 2. Some but not all of the text comes over-it just stops at certain point. Has anyone else experienced this and is there a solution?
View 9 RepliesI have created a non-subjective job evaluation spreadsheet, which takes data obtained from a work daily of an individual and is implemented in my spreadsheet. The columns of areas to be evaluated will average each individuals performance by averaging the individual to the rest of his shifts performance and a point system has been established depending on their work level. The situation I'm running into is I'm using the formula below to establish my point system. In an effort to be fair in every way possible, should a particular column end up with all zeros "0" this would then become the average, which should provide the individuals in this group with three "3" points. My forumula as it is set up will now only give one "1" point, when it should be three. The point system works great other than this one particular problem, when everyone has a zero. I'd sure appreciate anyone's suggestion how I might be able to add one more IF statement to this existing formula. Note: I29 is the cell for this particular column that represents the average of the column with data in it. I28 is 10% above average, I27 is 20% above average, I30 is 10% below average and I31 is 20% below average.
point system is 1 point for 20% below average, 2 points for 10% below average, 3 points for average, 4 points for 10% above average and 5 points for 20% above average.
=IF(ISTEXT(I3),0,IF(I3="",0,IF(I3<=$I$31,1,IF(I3<=$I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27, 5,IF(I3<$I$27,4))))))))
I have a workbook that has twenty data sheets. The user presses a button that copies they're specified sheets to a new workbook and prompts them with the SaveAs Dialog. How do I code it so that if they press the CANCEL button on the SaveAs Dialog it closes the created spreadsheet without saving it?
View 6 Replies View RelatedI have a button that will create tabs based off of information from a text box and now I want to select one sheet and insert text into certain cells so when they create a new tab information that is generally going to be there is autogenerated. I have an example of my code below. Please point me in the right direction cause I cant find anything really helpful that I havent tried. Everything is under the "gateway" then "New tabs" and PBG-Activity list is the only one with this macro.
View 2 Replies View RelatedWorkbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
View 4 Replies View RelatedI have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.
files: twilight sheet squirrel temperature
I've got the following code and have been trying to make the cells in column 1 align TOP LEFT but haven't been able to.
[Code] .......
I wish to Automatically copy the TEXT that is written from Spreadsheet 1 cells D5 to F5 to Spreadsheet 2 cells F5 to J5 .... a similar range of cells.
Is there a formula I can use or do I need to venture into the programming side of things.
How would you set up a macro to delete numbers in a spreadsheet in one column that appear in another column or worksheet?
In other words, if I have the numbers 1,2,3,4,5,6,7,8,9, 10 in one column and in the same column, or another column or worksheet I have the numbers 1,2,3,4,5 - I need a way to delete 1,2,3,4,5. I know how to delete duplicates via the advanced filter but I want to have the duplicate and original deleted.
I was wondering if it is possible to hide a column(s) but still have data in that column be visible at the bottom of the spreadsheet. For example, if I hide column G can it only hide the column from rows 1 - 50 and then the column will appear after row 50?
View 3 Replies View RelatedIn certain cells of a spreadsheet I have long text entered but it shows a series of hash signs instead.
View 10 Replies View Relatedim on a spreadsheet that im plugging into falconview, a map viewing program-on the spreadsheet i have coordinates, but not directions (N,W,S,E)-how can i enter in say a "W" before each coord without manually plugging it in- i would greatly appreciate it-its for OTJ military work
View 11 Replies View RelatedI am trying to make a macro where when you click the Export button Macro it just takes anything that has a Yes in Column A2:A100
View 9 Replies View RelatedI am trying to 'tag' new part number records in a spreadsheet (see SS2 attached) - checking against a second excel which contains existing part numbers (see SS1)
If Part Number in SS2.xls (column B) appears in Item Code in SS1.xls (column B) then i would like to put a 'Y' or similar in column J of SS2.xls .
Is there a way to make a simple text analyser that will return the number of times each word is used in a sentence or paragraph within a cell?
I use excel to write short paragraphs to upload onto social network sites and I would like a formula or program that tells me how many times each word has been used over a number of cells, to avoid repetition. Is there a way to do this?
I am trying to stop this code from re-occurring at the end of the spreadsheet. There are two constants that will always appear as text, one is "blank" and the other is "Grand Total". I would prefer to use "Blank" if possible. The spread sheet varies in size from day to day. I have a range right now in use up to A700. I did this because when I originally put in a column formula, it ran until 20000 + rows down. Can someone please provide me with a formula that stops this code once it reaches the bottom of my spread sheet?
View 2 Replies View RelatedI am trying to pull a value (text) from another spreadsheet corresponding with a number. I can't use vlookup because none of them are in a left column. HEre's an example:
there are 2 columns with names and percentages. I am trying to pull a name that corresponds to a certain percentage.
I have a userform with a textbox that has numbers in it, but when I write them to Excel they are put in the column as text, not as numbers.
[/CODE]Cells(erow,6).Value = txtbox_Amount[CODE]
How can I force it to write the information as numbers?
P.S. My computer won't let me paste into these posts, even when it asks me to allow access to my clipboard. What setting can I change so I can paste my code instead of typing it.
I generated a large spreadsheet and now for legal reasons all the data has to be in Upper case. Is there a quick way to convert all the text.
View 6 Replies View RelatedWhat I want to be able to do now though, is somehow, when someone sends the document back to me, create an FTP queue. I can do this with BulletProof FTP, because it allows really simple text queues. So, what I need excel to do is, when someone has finished selecting their files, is write the results to a text file like exactly like this (without the Line1:, etc.):
Line1: <text from D2>
Line2: 0
Line3: 0
Line4: 0
Line5: ?
Line6: <text from E2>
(then repeated for each row in the excel document until column A is returning blank)
I have about 10k poorly formatted txt files that I need to grab 4 items from each and put in a spreadsheet. How can this be done in Excel or should it be done in Access instead?
Here's a link to what each txt file looks like: MTRP88PF700721.txt
I only need the number next to Posting Journal on line 3 (12686 in this case), the 3 digit number next to Class Total on line 11 (101 in this case), the Extended Cost Final Total and the Extended Retail Final Total (23.77 and 39.30 in this case)
because one of my staff members is currently opening each txt file and typing these figures into a spreadsheet!
I am trying to make compatible for international users. They enter bike rides via a form. When the user hits submit Excel finds the date and then posts the ride info.
The problem is that if I format the date textbox (textbox1) as international the date inputted in textbox1 isn't being found in the column.
Code when the form initializes ...
I have a spreadsheet with over 20 tabs - each containing 6 text boxes containing financial commentary. Each month, the tabs are 'overpasted' with the latest month equivalents. This results in the latest month's text boxes sitting on top of the previous month's. I would like to attach functionality that deletes all the text boxes at the start of the monthly cycle - so the 'overpasting' exercise starts with a spreadsheet that contains no text boxes. I have been unsuccessfull with my attempts because each text box has a unique number - which changes every month. Does any VBA script exist to delete all the text boxes in a workbook or tab?
For reasons that are too longwinded to go into, there is no way around the overpasting set-up as summarised above (I recognise this is far from the ideal solution).
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
I am trying to get the first column to float down as you scroll down the spreadsheet to fill in information. How do I get that going. I can't figure it out.
I use excel 08, computers viewing the files are 03.
I am wanting to freeze the first column on my spreadsheet (easy) but also the top few rows. Every time I try (am using 2007) it seems to make a strange selection where it splits the screen exactly into 4 equal parts rather than just the first column and top 4 rows.
View 3 Replies View RelatedI have a list of files in folders.
When I make the index of those folder, i have an excel list with in column A these numbers
1.1.1.1 (so the file 1 in the 1st subsubfolder in the 1st subfolder in the first folder)
1.1.1
1.2.1.1
1.2.2
1.3.1
etc... The list goes on with 1000s rows.
And in column B I have the names of the files
Unfortunately, when i sort the spreadsheet with the number on column A, the order is not good....
So I would like to find a solution to sort things in the good order.
I have a spreadsheet with unique identifiers.
I would like to add a column from another spreadsheet which also has unique identifiers - but some may be missing and there may be some missing so cannot simply cut and paste over
Merging the two sheets by this unique identifier?
I am doing a project and met with a problem with the excel spreadsheet.
will keep my questions short and sweet for easy reading.
i have 2 excel spreadsheet which i need to link up.
one has an option for you to choose your nationality eg. american /chinese
the option is done via a "dot" checkbox
once "american" is checked, and the name is entered at a column below it.
the name will be keyed into the 2nd excel spreadsheet under the "american column"
and if its checked "chinese", the name will be entered under "chinese" column which is also in the same spreadsheet.
>>i understand that once i keyed in more entries. i need to create a For...loop whereby the system will do a run-through to check for a empty slot, insert another row and place the data in the rows.
>>how do i link up and make sure that the computer will know when to put under the "american" column and when to put under the "chinese" column.
I have created a sample spreadsheet (the real data is sensitive) but I am basically faced with a spreadsheet of many columns of which I would like to remove the 4th,5th, 8th,9th, 12th, 13th etc...
So every 3rd column starting from Column B, and also every 3rd starting from Column C, (every 3rd, and every 3rd + 1 starting from B?), it's obvious what I'm after from the spreadsheet I've provided.
Unfortunately manually isn't an option, this needs doing every week, and it goes all the way across to column HZ!