Data in cell A1 - 1) A Q
Data in cell A2 - 12) V
Data in cell A3 - 131) B H L
How do I throw away the #) and the following space in cells A1, A2, and A3 and save the rest?
Excel 2003. ie. no VBA.
I'm using Microsoft Office 365 Home Premium. Although this file is saved with Excel 97-2003 - because the computers at work only use 2003.
OK - Here is my dilemma. I've been creating a spread(work)sheet for work. One of the fields that needs to be entered by the manager doing the papwerwork is 'Date'. I currently have the Data-Validation set as follows because the paperwork being done is for the yesterday:
Code:
=TODAY()-1
I have the Error Alert set to Warning prompting the manager to ensure the date is correct (sometimes a mid-shift audit is being done, so the actual current date has to be used). Now, what I am wanting to do is to also set it that if the manager set's the date for a future date, I would like Excel to also either do a Warning or a Critical displaying a message that future dates cannot be used.
I know this may sound trivial, but with this paperwork, dates are extremely critical and vital in the event of an investigation for cash shortages etc.
I have uploaded the excel sheet to my domain, which I can provide a link to - either here or in a private message - but I do not want to do so unless I know that it's ok to do. If so, I will reply with a link to the location of the Excel file.
I've got 2 worksheets in the same workbook.
Sheet 1 contains huge amounts of data - thousands of rows and multiple columns
Sheet 2 - I want to extract the data from sheet 1 column A into sheet 2 column A but only the data from every 21st row.
I want to be able to copy the formula automatically down, otherwise it will take hours to do it manually. So far I can only get the cell reference to increment by 1 each time after copying.
So what I'm trying to achieve is :-
Sheet 2 A1 = Sheet 1 A1
Sheet 2 A2 = Sheet 1 A22
Sheet 2 A3 = Sheet 1 A43
She has a spreadsheet created in Excel 2003 (she is still using 2003) which contains an image with a textbox in the top left corner then all other cells contain data.
When user prints this spreadsheet all that prints is the image and the textbox.
I have cleared the print area reset the print area removed the picture (prints only the text box)
and none of these changes have changed what prints.
I have checked all printer setting and these appear normal.
previously user could print this spreadsheet fine and all other worksheets in the workbook print fine as well.
other users are still able to print to the same printer correctly from this spreadsheet.
I have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
Referral
A
B
C
[Code]....
way to do the calculation using Excel 2003
I have an excel file with a large list of data in the following format, all the data is in the A column –
Mar 11: category one – process number one (1)
Mar 11: category two – process number two (2)
Mar 11: category three – process number three (3)
Mar 11: category four – process number four (4)
Mar 11: category five – process number five (5)
I would like to extract from the list, ONLY the data highlighted in red i.e. the process number data –
Mar 11: category one – process number one (1)
Mar 11: category two – process number two (2)
Mar 11: category three – process number three (3)
Mar 11: category four – process number four (4)
Mar 11: category five – process number five (5)
I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.
Excel 2003ABCDE1Time 1Val 1Time 2Val 2Match203.902.72.73484.7482.12.14545492.33.351006543.34.562003554.472192.71004.57.782015.592196.6102197.7Sheet1
I have a folder on my drive that holds excel files, some of which I would like to extract data from, using an excel workbook outside the folder.
Can you assist me in achieiving this action? Or, direct me to literature that will have the answer?
I have a table similar to this
L M N O P Q
title1 title2 title3 0
12 5 0
0 23 0
13 1 0
453 1 0
I need a formula to get the following structure (without using macro):
A B C
title1 title2 title3
12 5 0
0 23 0
13 1 0
453 1 0
Attached File : ReturnNonblanks.xlsx‎
I just wanted to ask whether the same code will work for many more sheets or will modification be needed? I have a very basic understanding of vba loops, so plz tell me did you check the entire row for being blank or specific cells? Im attaching a file which more closely resembles the data i work with, just tell me whether the previous macro called "ravi" will work fine or will it require modification.
View 12 Replies View RelatedThere are websites available for data, but what is viable and crucial to me is finding a way to
1) Run a repeated process via a macro / vba script to allow for data input into the site
2) Once input into site, data is to be brought back into excel so that I can benchmark my product pricing vs my competition
There are 2 sites I am most interested in getting this information from specifically and although they do the same thing they have a couple of unique pros to them. they are the following
1) Findchips.com
2) eciaauthorized.com
The biggest pro to findchips.com is that they have a beta tool on their site that allows for "mass searches" to be performed as opposed to 1 part number at a time. Up to 100 results can be returned at a time, so although there would be multiple iterations doing it this way would save processing power as each step is not done line by line (p/n by p/n) as i could group them and send them and perform the operations that way.
From the website [URL] ....
I need the Name, Website and contact details. I tried to extract but there is no ID for the element.
Data from the above site is:
1 Name: Shopping da Midia
2. Website: It is a hyperlink of Shopping da Midia that is [URL] ......
3. Contact details:
Tel: 16 2101-2500
Av. Independencia , 192
CEP: 14010-210
Ribeireo Preto
E-mail: compras@cdvirgem.com.br
This table has one column with people's names, other columns with different things, and one with hours.
The figures will change every month, and my dad doesn't know crap about excel, so anything that he has to go put in equations or sort again every month or whatever, is not going to work. it needs to be so that all he has to do is plug in numbers.
It can be either one or two ways: either I need to pull out all the rows that have hours above a certain amount, and have them sitting to the side/under/above the main table in the same worksheet.
I realize that that may involve a long complicated formula, however.
Alternatively, we can have it so that all the rows with hours above a certain number are color coded at located at the top of the main table. I figured that one out with ease, HOWEVER I don't how to make that automatic, so that it does it by itself when the numbers are plugged in each month. My dad would not be able to handle sorting.
I need the hours that =0 color coded and put at the bottom if possible, it's not absolutely necessary, however. I could not figure out how to put the highest hours on top and the hours equaling 0 at the bottom, considering that all rowsthat fall in between need to be kept in alphabetical order.
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get one of the minor files to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make mattters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
I am trying to set up a spreadsheet in Excel 2003 that pulls data from a lot of different websites and formats it all for me. I have managed to do this with a few websites but there are two which I'm having problems with.
This is the table I am trying to get, but when I do a manual web query and select the table and click OK, it says that no data has been found.
[URL] ...
I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.
Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?
Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?
I work in a call centre and I record my daily figures. My target is 82%, and I would like a bar chart in Excel 2003 to colour the chart according to wether I hit target or not. If my daily figure >=82% the bar should be green.
View 9 Replies View RelatedI need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies View RelatedI want to pull data from another sheet, however there are multiple listings of each and I want to pull the data to another sheet for each line. I maybe easier if I try and show below:
Col. ACol. BCol. C
Smith101
Jones512
Green65
Black1214
Smith 3612
Jones1512
Dual25
Green1225
I want to pull this data for each name in Col. A in to another sheet. I've tried "IF", "Vlookup" and a couple of others, it just seems to be hitting the first option and pulling the data but not the ones below.
So if I selected Green on the second tab it brings through the "6" and "5", but not informtion from the listing from Green below. So I wiould like to list all the Green's, and the applicable data.
I have Excel 2003
My spreadsheet has multiple lines of data for multiple items (have a specific item #) and I need to be able to enter an item # on a different sheet so that the requested information can be pulled from the data source. The items have information on multiple rows. I need to have all of the rows pulled into my output file when I specify the specific item. #.
I have attached a spreadsheet showing the type of data is contained and what my desired output would look like.
So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.
I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.
I don't have Excel 2007, only 2003 I have.
I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).
I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.
Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii
Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.
Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.
So essentially I need a VBA Macro code for Excel 2003 that can take raw data that is input every month via a text document and move it to the next sheet in the workbook. This data is essentially broken down by a "platform" in the raw data section ("P") with monthly totals for each month but it is broken down by platform in total which goes in descending monthly order on the "dat" tabs (which is the final place for the data).
So what needs to happen is the data for the last two years must go from the "P" tab to the "PDat" tab under the correct platform and the correct spot for the newest month. The data that is input each month is in the same format month to month, just starting two rows down from the last month.
At the end of two years, the oldest year of data must be deleted and the other data moved up to the vacant area so that the sheet never grows too large (i.e., at the end of this year 2012 will be deleted and all the data will need to get moved up).
Attached is a sample data workbook.
I have a notepad with lot of information's but when i try loading those info's to excel I am getting message "File Not loaded properly"
Is there a VB code which downloads it to excel and automatically moves to next tab and paste the rest.
I'm using Excel 2003
Cell D7 - data validation reads as such: It is a List and the source is I6:I7. I have In-Cell dropdown selected. Ignore Blank is not checked.
I now password protect the sheet and only D7 is unlocked. real simple, the person using it can only use D7 and select one of two in the drop down menu.
The problem is, if the user clicks on D7 and hits delete, the cell goes blank. How can I avoid this?
I have a worksheet with data in it. The data could be in any column or row. I am looking for data which contains the numbers 01. If I find such data, I want to move all the data in that particular cell it to column A.
My code(which is not working) is below. I'm using Excel 2003.
Code:
Sub delete_oldads()
'the code to find 01
Dim cel As Range
[Code].....
Below is an extensive macro that basically writes data from a user's template to a certain data sheet (in the form of rows) depending on their "service group." There have been 3 separate instances of a user saving data and instead of adding rows onto the bottom of the entire list of data, the sheet only has their data and no headers/filters. I believe it is somehow deleting all the previously written data that was on that sheet.
I should also mention that this is a shared workbook. I specifically created separate sheets for each "service group" to eliminate the possibility of users overwriting each other's data (Perviously 60+ users were saving to the same sheet and running into conflicting changes errors. Now the max users writing to one sheet is 10-12). Since it is not occurring every time, I am not sure where the error is. Could it possibly be in the sort or delete sections in bold below?
I am using Excel 2003.
Code:
Sub SaveData_Test()
'---------------------------------------------------------------------------------------
' Procedure : SaveData_Test
' Author : Julie/Cecil
' Date : Fri, 1/13/12
' Purpose : Modified Julies code to capture date entries
' associated with hours logged per category.
' Check notes attached to "sOp" string comment
' for additional details.
[Code] ..........
Objective: I have a financial spreadsheet, which i want to bring to my front sheet the sum of data in between 2 week ranges. For example, if I select week 26 and 52 then all the relevant data will be summed into the relevant cell on my front sheet.
Current Technique: I have on my "data" spreadsheet my columns in B1 down listing Week 1-52 and then the cell headings in B2 onwards. I have created a summary table which currently shows the 4 quarters of the year (1-13, etc) and I am using helper columns to pull this data onto my "front sheet", depending what selection is made on the drop down e.g. 1-13, then all summary data will be dragged onto my front sheet.
On the front sheet, in each cell I need information to be displayed I use the following formula;
"=IF(ISERROR(INDEX(Data!$C$56:$BF$60,Data!$BI56,COLUMNS('Front Sheet'!I13:J13)))"
As you can see the quarter data is useful, but a more flexible date range would be more useful to sum data between any selected week.
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get the minor files (68 seperate files) to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make matters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
I'm doing some simple data extraction, e.g.
A B
1 bob 3
2 mandy 4
3 charlie 6
4 dave 1
5 steve 5
So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with,
gap
mandy
charlie
gap
steve
how would I get,
mandy
charlie
steve
also is it possible to have an if statement in 1 cell change the value of another cell?
e.g.
in a1
if(b1>5, c1="yes",c1="no"), can't seem to get it to work