I design all my spreadsheets on a 19inch LCD however when I push them out to the network drive and our assoiciates open them on there 17 inch CRT'S the sheets dont fit correctly. What can I do to correct this on my end so when they open them they look and fit correctly on there screens?
View 2 RepliesI have a very big list in one column with the customers name and its account number. I am looking for a way to extract just the number from the cell.
I am aware of the 'fit data to columns' option but some of the customer names have an inconsistant number of spaces to assign a delimiter.
I'm struggling with getting my Excel worksheet to fit nicely on the pg for printing to .pdf. I could tinker with column widths manually to work this out for a printed report. But I need to print a couple hundred reports with varying column widths, so I need a vba solution.
The context is that I'm using vba to process two sets of interlinked data on a worksheet. I only want to print one set of data, and keep the second set out of the print view. To do this, I'm keeping the first set of data in columns 1 to 10 and the second set of data in columsn 21 to 30. I set the print range to only include columns 1 to 10.
This works fine for keeping the second set of data off the printed pg. The problem is that there is always a gap between the end of column 10 and the maximum width of the printed pg. Given the formatting of the report, this doesn't look very good.
I know that page setup offers the "fit to" option. This would work if I wanted to fit everything on the worksheet on the pg, but I don't. I just want to fit columns 1 to 10 to the pg. Is there a way to do this, allowing for varying widths in columns 1 to 10? Is there a way to do it via vba?
I have a worksheet compiled with text and decimal numbers all the numbers are filled in via. a data validation drop down list. This fills in many cells in certain columns and rows with data from others sheets depending on what item is choosen from the list.
Is it possible to have a macro or two macro buttons that will convert the decimal numbers from inch to metric or vice versa. When clicked. Note that there are decimal numbers in 3 decimal places up to five decimal places.
I know to convert Decimal to Metric is .0000/.03937
Metric to Decimal is .0000*.03937
Need to convert decimal to inch fractions IE: 4.5 = 4 1/2 inches
View 9 Replies View RelatedI am a woodworker. I have a CAD program that gives me a cut list of all the parts I need. I then take this list into Excel and do some stuff with the data.
all the dimensions are in fractional inches where ' is used to designate feet and " designates inches. I use the find and replace command to get rid of the ' and ". if the dimension is just a fraction like 3/4 Excel thinks this is a date and displays march 4th. I have tried formatting the cells to numbers before i do the find replace with no luck.
I have the following data (measured in 1/1000th of an inch)(I have rounded down in this example though!!!)
NameABCDEAGGReal RANK
AAAAA0.110.120.090.120.270.1422
BBBBB0.120.1140.080.1350.2610.1421
CCCCC0.350.170.170.160.110.1923
copied down to a minimum of 100 rows (names) The AGG is the sum(a:e/5)
When u use RANK(agg,agg1:agg100,1) It ties Names AAAA and BBBB as they both have AGG of 0.142. Problem is the rule book states ties are split by the smallest of the results for each competitor in columns A to E. In this example BBBB is 2nd as he has smallest result in Column C being 0.08.
I have tried the AGG+1/min(a:e) and still end up with incorrect results. The problem is the numbers are so small and you have to split ties all the way to the 100th person involved. I cant use sort either as the data has to be printed in alphabeticall name order all the way to 100 names with their ranks recorded against the names.
How can we determine Number of Logical Pixels per Inch based on the system configuration using API and return the value to Cell A1 of Sheet1.
I often use feet and inch inputs for calulations. I prefer to input a typical feet & inch input into one cell using this format: ft-n.
example: 12ft 9in would input as 12-9
This would need to be converted into a decimal for calcualtions. Also I would like to convert from Decimal back to ft-in.
I'm going to turn the F1 key off in my spreadsheets so that I do not hit it by accident so manytimes, but how do I bring up help without it?
View 5 Replies View RelatedI am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?
View 6 Replies View RelatedI have two spreadsheets, A & B. A can be thought of the master spreadsheet and has one record for every employee giving personal information, including a personel number. B has 0, 1, or 1+ records for every employee in A. Each of these records has a personel number. I want to copy the value of a specific field from B to A.
For example:
A:
personel number1
personel number2
personel number3
B:
personel number1, 'sally'
personel number1, 'john'
personel number3, 'pete'
merge A & B:
personel number1, 'sally', 'john'
personel number2
personel number3, 'pete'
I want to do....is...a macro or something....to be like "Hey...were going to match up column information based on the first column, there exists both the same data in both sheets....as you can see in A3 and A2, now, I want to be able to add the information that is in book1, into the "DBL approved", so like...the 2nd row, I want columns B-G to be filled via book 2 because its saying,
"okay, A3 in DBL has the data 00-120-01, but there exists no data next columns over...but oh look, A2 in Book1 does, let's fill in that missing information!" so now it'll fill in the bullet points, brand, segment, etc."
I am working with excel2003. I have to different workbooks in which on is a Bill of lading that is used to show the items being shipped. The other workbook has a list of the items that we ship.
On the BOL I want to be able to type in the "L" number (or I.D. number) in D14, D19, D24, D29. By typing in the L-number I want it to automatically bring up the product name into cells E14, E19, E24 and E29. With the exact name of the item it will help prevent employee error. I want it to also bring over the exact weight over from the flavor list in cell range C5:C28.
I have the FlavorCodes range with all the L-numbers on the BOL in cell range U9:U48. I thought I had it set up correctly but when I select the L-number in the drop down list in cells E14, E19, E24 and E29 I get error code #NA. I thought I defined the cell range but it is still not working.
The workbooks are attached below.
how do i access data from different spreadsheet tabs in a same excel file..e.g: if tab 1(student) and tab2(teacher), how do i access tab2 let say cell C4
View 14 Replies View RelatedI have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction".
For example:
Spreadsheet 1:
Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G.
If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F
I have two spreadsheets (lets call them Spreadsheet A and Spreadsheet B). Spreadsheet A is a list of all my customers and Spreadsheet B is a list of potential leads which may or may not include some which are already customers. Is there a formula I could include in an extra column to highlight which ones are customers already so that I can ensure I don't call them.
If it makes a difference the two spreadsheets are stored on seperate locations on my computer.
I have been handed 503 spreadsheets containing system data for multiple business units in my company. Each spreadsheet is identical in structure (same column headings) but just varies in the number of rows of data
I’ve now been asked to add (4) additional columns to each of the 503 spreadsheets. The (4) new columns are to be called:
“Class”
“OpCat Template ID”
“Agent”
“Last Occurrence”
And added in columns L, M, N, and O.
Is there a way to do this automatically instead of having to manually enter this data 503x?
I have got a few spreadsheets which save information for each departments, about 8. They contain information on bookings & I am being asked to get information on certain date ranges, mainly monthly reports. (They are being filled in automatically from another excel file.)
Can I have a file which I could call "Master file" which has a macro in that looks up and copies all the relevant rows from each departments spreadsheet into the new master file. I dont think this would be too difficult (still beyond my capabilities though) My intial thoughts were something like (in half code half english).
Create a form where you specify your date range and a submit button e.g. txtstartdate txtenddate
Lookup 'G:FolderGeneral[General.xls] IF in between txtstartdate and txtenddate then paste
ActiveWorkbook.Sheets("master sheet").Activate
Range("B2").Select
Do
Is it possible for me to link to other spreasheet data, and have the links update (retrieve the data) without those files being opened?
View 9 Replies View RelatedI have a spreadsheet which needs to be shared by 10 people. The spreadsheet is fairly simple but I don't know the best way to do this. I have heard of public folders or to share the .xls.
View 4 Replies View RelatedIm using this code. My data is in column A from Cell A2-A65000, and the data I want to compare against is in a different sheet col K cells k2-k65000. I need it to compare column A on "Sales Reps" against Col K "Data Sheets" and count how many times whats in cell A2-A65000 occurs on in Cells k2-k65000. So if 689 was in cell A2 and was listed in cell k3, k4, k5, it should return the value 3 in Cell B2. My formula is not working. Getting an error.
For Each ce In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
ce.Offset(0, 1) = WorksheetFunction.CountIf(Sheets("Data Sheet")(Range("k:k"), ce.Value))
Next ce
I have three different spreadsheet, Spreadsheet A with the name of all the clients of the company. Spreadsheet B with the name of all the clients in Toronto and Spreadsheet C with the name of all the clients in Montreal. Now i have to create a fourth spreadsheet to lookup the clients who are not in the Spreadsheet A (basically trying to find new clients). How do i search for the new clients in all the three spreadsheet?
View 2 Replies View RelatedI have sucessfully created a spreadsheet which links to another using a vlookup and an array. I now need to reverse the process. My requirements are this:
I have individual spreadsheets which represent individual projects/quotes for our customers. I'd like each of our sales staff to have a summary spreadsheet which summarises all their ongoing projects in one spreadsheet, dynamically.
when a new project is created on a new (projects/quote) spreadsheet, it looks to the summary spreadsheet and looks to see if that project exists in the summary, if not, it creates a new line and writes the summary details to it. then as the project spreadsheet evolves, it updates its details on the summary sheet dynamically.
Basically I order thrice a week from a certain supplier to fill the shelves of my shop.
They make available to me two spreadsheet dumps: an orders spreadsheet, with the following columns:-
order ref
date
unique product code
description
qty
9262
03/07/2013
622642
Fabric Dye
4
And an invoices spreadsheet with the following columns:-
invoice ref
date
unique product code
description
qty
122551
04/07/2013
622642
Fabric Dye
4
Basically I can see the potential here to find out what we've ordered but that which they haven't sent which would be so handy.
I have tried to vlookup() produce code against product code, this kind of works but below are some example drawbacks:-
1) no idea if I've ordered it ten times and they've only not sent it once, or if I've ordered it ten times and they've not sent it nine times.
2) no idea of dates - i.e. ordered this in June '13, October '13 and February '14 and they sent it in February '14 only.
I'm doing an audit where I need to compare data between two spreadsheets that are housed on the same worksheet. The data I'm trying to compare is the ssn#. I'm expecting duplicates, I'm trying to find ssn#'s that do not match up with another. In other words, if one spreadsheet has 92ssn#s, the other has 79, I want to be able to identify those that do not have a matching ssn# from one spreadsheet to the other.
Both spreadsheets have the following columns:
Column A Column B Column C
SSN Last Name First Name
The tab on the bottom of sheet 1 is titled "CX Data", the tab on the bottom of sheet two is titled, "BCBS of AL Data"
in creating a VLOOKUP formula that will meet my needs?
I'm having a problem with how excel opens my files. Up until yesterday when I would open any of my spreadsheets they would open seperately. I would have multiple files open across my taskbar and could easily switch between files with a simple Alt-tab windows function.
Now when I open multiple excel files it opens them under "one" file so to speak. The only way to switch between files is if I go to Window>then select the file I want or minimize within excel so I can see the other spreadsheets.
What I am trying to do is take the data (all columns) in Spreadsheet 2 and put that data in Spreadsheet 1 with all the companies matching up, how do I do this, is this possible?
I tried using VLOOKUP but I don't know if I was doing it right and I don't even know if it's the right function to use.
In sheet 1, column A I have a list of about 30,000 companies
In sheet 2, column A I have a list of about 10,000 (pulled from sheet 1), I have other info associated with these companies in columns B and C.
Basically I want to take column A, B and C from sheet 2 and match them up with the same companies in sheet 1. So there will be 10,000 matches and 20,000 N/A's. This is how I set up the VLOOKUP but nothing happened.
I have two spreadsheets. Spreadsheet A has a column of numbers representing employee IDs (thousands of them). But I need to also have the First and Last Name for these employees to cross-reference. Spreadsheet B has the employee ID, Last Name, First Name. Spreadsheet A is large and grouped in a way that I cannot just sort by the employee ID to match up with Spreadsheet B. So basically what I am trying to do:
Look in Spreadsheet B for the employee ID found in Spreadsheet A. If found insert the information in the Last Name and First Name cell for that row copied into Spreadsheet A
Example:Spreadsheet A
Employee ID
123456
412378
<blank cell>
512345
685217
654553
Spreadsheet B
123456 Alberts Rick
654553 Johnson Mark
412378 Smith John
So I want for Spreadsheet A to compare with Spreadsheet B. If 123456 is found in Spreadsheet B, then put Alberts and Rick in the corresponding cells next to 123456 in Spreadsheet A.
I am trying to find a vlookup formulae for multiple spreadsheets in excel.
I have the below data that i need to lookup, test and produce an output.
Main Spreadsheet:
Column B...........................Column C
A 0000564235.................... <Desc> <---- Desired output is Coumn C from searching all part numbers in each spreadsheet.
Spreadsheet 1:
Column B (Part No.) ..........Column C (Desc)
A 0000564235................................Hose
Spreadsheet 2:
Column B (Part No.) .......Column C (Desc)
A 02315645646 ............................Clamp
Spreadsheet 3:
Column B (Part No.) .......Column C (Desc)............