I have 2 sheets of data one for data the second for input and calculations.
SHEET 1 (Data)
HEADINGS - FROM / NUMBER
A / 81
C / 99
E / 01
SHEET 2 (Input & Calculations)HEADINGS - FROM / TO / FROM val / TO val
I want to be able to stick "A" in Col1 (FROM) and the number 81 to automatically appear in Col3 (FROM Val). I am unabel to use LOOKUP as my Pocket PC doesen't recognise it.. VLOOKUP and HLOOKUP are ok though, I just can't seem to get them to work for me.
I was trying to use an if statement to produce results based on 2 cells. The number in the 1st cell can be the number 1 to 5, but each number has critera with it. Say the cell is A1 and i enter the number 1. Then i want to check the value of Cell A5, if this is less than 40 then the cell A10 should read 250 and if A5 was over 40 then it should read 285. struggled to get a formula properly to work.
Here are the values for each number: A1 = 1 with cell A5
I am trying to simplify the manipulation of data to create a full postal address from a range of cells.
Due to the varied way in which postal addresses are made up, I have found it necessary to create a 2nd Worksheet, which uses helper columns to ensure that spaces and commas appear in the right places.
I recently came across some code on this site from JBeaucaire which I have tried out but in my particular workbook, the commas and spaces do not come out correctly.
The enclosed sample workbook contains a selection of addresses which should cover most of the variations for addresses in the way that they are entered in my main workbook.
Sheet1 (Master) is the main data. Sheet2 shows the formulas that I have used to create a full postal address. Sheet3 makes use of a VBA Module and shows the results.
I felt that VBA code is the right way to go but I'm unable to modify the code to make it work or come up with an alternative VBA solution.
I need a macro that can search a sheet for a match and then copy all 7 cells to the right of the match. I have attached an example of the sheet that will serve as the database to search, and a userform example that will be similar to the userfrom that will display the copied cells when a match is found. I plan to copy and paste the 7 cells to a different sheet so that the userform can display the results with the control source property. I do not need a way to add to this database. I know very little about searching a database so.
I coach High School track and recorded all the kids workouts in DATAEASE - I had to upgrade my PC - (Dataease no longer works) so I am trying to record the kids workouts in EXCEL and be able to produce reports either by Code or NAME. Attached worksheet - SHEET 1 - data entered / SHEET 2 - is a report I would like to produce.
Any formula where I can in a single cell have 18 Nov - 22 Nov as an output, and drag it to the right so the next cell shows the 25 Nov - 29 Nov and so on. i.e. just the weekday range, in that format?
Any way to produce a report per row of an excel spreadsheet. For example if my spreadsheet contains the names of 10 people in column one and lots of columns of data per person. Is there an easy way for me to pull all that data to produce a one page report for each person listed with the data within it?
I have a set of unique customer names in a column A, say A1 to A200 in my data sheet. These customers have route numbers assigned to them (defined by the client) column B - B1 to B200. The route no.s are 1,2,3,4,5,6,7, & 8. The column lists can be of varying lengths but no more than 500 rows. I want to relate the routes to 8 separate sheets in the workbook. In each sheet I want to extract a column of customers based on the route number. So if the first customer in column A is route '1' (column B) it populates the first entry in sheet 'Route 1' with the customer name as so on for the rest of the sheets.
- which is marvellous and gives me exactly what I want. However my test book only has this dragged down 10 rows and already it takes 20-30 seconds to calculate. The real workbook is likely to stretch to several hundred rows, and to make matters that little more complex where I have Sheet2!$B$2:$B$100 this will relate to a completely separate book on a company network.
Is there any more efficient method of creating this list or will I just have to bear with the calculation speed?
How to write code to produce 17 rows of 0s, then 17 rows of 1s ....all the way up to 120. So that would be 2040 rows altogether. It takes forever to do it manually.
I have the following data which is download as csv, i need a macro to produce result in sheet2, notice if there are duplicate stmt or ledg for the same case No then the units need to be consolidated as shown in sheet2
Outstanding Holdings Case ReportAs At Thursday 4 June, 2009 16:15AccountSourceCase No.Ledg StmtValue DateMessage FeedSecurity CodeDescriptionCcy CodeUnitsExceptionUnsettle UnitsCase TypeKnown SecurityMatch NoCase AgeAge BreakAgent Code:DERIVHOLD ABERDEENJPMC.AEIABIDERIV562272Ledg4-May-09JPMC.AEIABIXT0906AUS 10YR 0309 BOND FAUD-440Hold Singleton CaseYes305779830LATEST CASE NOTES:5-May-09FuturesJPMC.AEIABIDERIV569967Ledg1-Jun-09JPMC.AEIABIYT0906AUS 3YR 0609 BOND FUAUD7-70Hold Singleton CaseYes2599402LATEST CASE NOTES:2-Jun-09FuturesItem Count : 2JPMC.AEIABIFA1569598Ledg29-May-09JPMC.AEIABIQTC0813QUEENSLAND TREASURY CORP 140813 6.00 GBAUD150000015000000Hold Net DifferenceYes3598143JPMC.AEIABIFA1569598Stmt29-May-09305496516891QUEENSLAND TREASURY CORP 6+ BDS 14/ AUG/2013..........
Am looking for an easy way to produce a list of combinations (maximum 6 numbers) from a range of numbers listed in 6 different columns:
Example Column A contains : 1,2,3 Column B contains : 7,8,11,15 Column C contains : 12,16,18,19 Column D contains : 17,30,31 Column E contains : 30,31,32,33,34 Column F contains : 37,39,40 The rules are:
In each combination of 6 numbers, numbers should always be taken from ALL 6 columns. In each combination, numbers cannot repeat.
I have a column of data that requires the cell to be blank if the value is 00/01/1900. The data is linked to another cell. IE: cell E3 is linked to cell $D$3 The cell is formatted for dates in the format shown.
firstly i have attached said spreadsheet 1st sheet is named purchase order 2nd sheet is named purchase order numbers
upon opening the spreadsheet i would like on sheet purchase order to automatically input the next available number from column a in sheet purchase orders ( in this case next available is st010 and put that into cell k12 on sheet purchase order
i then want to enter the relevant data in sheet purchase order cells b16 , b23 , g23 , i23 and d28
i then want to click a button with macro attached to transfer that data in to sheet purchase order numbers
I would like excel to automatically suggest the save as file name and location for my workbook when I click the save, save as or close options. The file name should be a combination of values in cells A1 and A2 of Sheet1. The location should be a folder named "Bill" in C:/My Documents. I think I can do it with a beforesave code but I am new to vba.
I have a workbook with 2 worksheets, Sheet1 & Sheet2. Sheet1 has a list of products on it, with a code, pack size and cost.
Sheet2 has got 8 columns in it.
The first column will be A , B , C
The second will be D, E, F
Third will be G, H, I
Going right through to column 8 which will be V, W X
In the second column in each on I will be have a (sell by date) So B, E, H, through to W will have sell by dates in them.
What I am looking for is a macro that will give me a list of short sell by dates when I click the button. So if today’s date is 02/12/09 when the button is clicked it will give me a list of all the product that have lets say a month sell by date left, I would like to be able to change the month to anything I want, just in case I want to do a longer check on the sell by date.
When it finds a short date I would like it to check against the code, so in the first column the code will be in A, it will match that code up against the code in Sheet1 and give me a list of the Code, and the Product from sheet1 and the sell by date that’s in Sheet2. The problem I have is when data is put into the second column on sheet2 which is D.E.F, I would want it just to check that column and not the A,B,C from column 1.
I have a very long list in columns A and B, about 1000 rows. I was just wondering how would i be able to setup an automatic formula that can produce the data in column D and E?
Ideally, i would the sequence running every 5 rows but iam doing this manually and running the sequence every 5 rows would be way to slow.
I have a column of consignment numbers in this location Sheet Name: C NUMBER Reference: C3:C10000
I have an entry field for which people may type their Consignment number in at this location to search for possible match Sheet Name: Query Reference: Cell D10
I would like to be able to allow a portion of the number be typed in to D10 and then have Cell D11 show the full first match from that partial entry. Thank you,
I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
I am trying to produce numerous workbooks based on a filtered name. I will attach a sample spreadsheet that has the data.
On the sheet we have engineer names. I basically want to filter them (not difficult with a macro) but then to copy the results to a new spreadsheet and save the workbook as the engineers name (ie J. Bloggs has 5 jobs so they are filtered and the results are dumped into a new workbook and then saved as J. Bloggs.xls). This will happen for all engineers.
I have though about doing it as a macro and I think that would give me the end result but we have around 20 engineers and these can sometimes go up and down. Is there anyway to do this automatically?
