I have worksheet where I would like to apply a number value proportionately throughout a column bases on user-defined conditions (i.e. >=, <=, Between) from other colunmns. See example below and attached worksheet with further detail.
Col A Col B Col G Col L Col M
01380Employee 15.0 5.5 (0.8)
00304Employee 24.0 - (0.8)
01901Employee 33.8 (1.0) (0.8)
01729Employee 43.0 - (0.8)
01482Employee 53.0 (1.1) (0.8)
00299Employee 62.5 - -
00069Employee 72.5 0.5 -
01783Employee 81.5 - -
02076Employee 91.0 - -
01934Employee 100.5 - -
TOTAL 4.0 -4.0
In the above example, I would like to have the TOTAL value of Column L (4.0) be inversely spread throughout column M based on criteria selected by a user in a section of the spreadsheet.
Criteria:
Column G >= 3 AND all values in Column L = 0; Spread -4.0 (Inverse of cell L12 = 4) in column M for records that match criteria.
I would like to have a command button apply the logic of the conditional criteria set for Columns G & L and spread proportionately ithe value from n Column M.
I added examples of the different criteria and the layout in the file attached.
I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
I have a workbook with two sheets, and I am having difficulty in finding how to match up the data.
Sheet 1 (named "TS") consists of several columns, but it's always a series of date columns followed by multiple site numbers.
Sheet 2 (named "Dates") consists of just two columns, a site ID and a Date column.
I am having difficulty in finding how to lookup/match the date that goes with the store number, as the information in Sheet 1 (TS) is laid out in a horizontal fashion.
So, for example: Sites: 14352, 14425, 11661, 404, 8142, 3571 should all have the date of 5/17/2014.
As such, on Sheet 2 (Dates), whenever those site IDs are encountered, the date of 5/17/2014 would need to be applied in col B.
Similarly, on Sheet 2 (Dates), for site IDs 1201, 3613, 9316, 9735, 13211, 7640, the date of 6/21/2014 would need to be applied in col B.
Here is a link to a sample workbook: [URL] ........
What I would like to do is return the total qty of items on the 'QUOTATION' tab that are upholstery and this having been determined by whether there is a Y (for Yes) or N (for No) against the equivalent item in the 'BASIC_LIST' tab.
So in the below example, the total of Upholstered items would be 182 as items 'ST-11' to 'ST-13A - Tray' do not have a 'Y' against the matching item references in the 'BASIC_LIST' tab.
I would like this Y/N column to be the determining factor as opposed to any other criteria or filtering. (Also the Items column in the 'QUOTATION' tab is only ordered in the way it is for ease of view for this query!)
Picture 2.png Picture 3.png
SUM VALUES OF ITEMS THAT HAVE ALREADY MATCHED ANOTHER CRITERIA.xlsx
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"
Below is a small sample of data, real data is around 100 rows
Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
The first step will be to get the formula or macro to fill in the cells under the date columns that will show how the consumption of the 555 is assessed to be spread out over the period. The formula should portray an equal distribution of the consumption over the period.
******** ******************** ************************************************************************>Microsoft Excel - claimv1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2=BCDEFGHIJKLM2Start DateEnd DateTotal consumptionLast week day14-dec21-dec28-dec04-jan11-jan18-jan25-jan01-feb317-dec24-jan555Assesment 4 Actual Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same.
I have attached a snapshot of what I am trying to do.
i have to make it to where when i put a number in it also puts in the description and unit price just by the number i put in to a cell. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong
I have a spread sheet (sheet2) that runs an add-in and does some calculations and arrives at a result which is a number in cell P6. All I have to do is type in a Stock Symbol in cell A1, and the sheet runs the add-in, calculation automatically. If I change the symbol in A1, the sheet re-runs the add-in/calculation and gives me the result for the new stock in cell P6.
Sheet 1, has a list of stocks, indexed in Column A.
Rather than me changing the symbol manually in sheet 2 cell A1, how can I automate it such that it will run the functionality automatically for each symbol in Sheet1 column A sequentially and then tabulate the results for each stock/symbol in Sheet 1 column B.
I tried to attach the sheet/book herewith, but could not. It is less than 100KB in size
I don't know how else to ask this so I will just tell you what I'm trying to do.
-I have a list of name on one column say "A" and this list of names goes through "A1" through "A145"
-I am taking those names and pasting them via "right click" "paste special" "Values" onto another spreadsheet.
-BUT I have to take each name one by one and paste them evenly in every 8th row for example ( "A1" A8" "A16" "A24" ...)
-Is there a way to take the existing data on that row and just spread it evenly throughout the same row? So I don't have to take names one by one by one...
I have a few hundred work orders done in Excel. Each spread sheet is a seperate file but all are identical in structure (basic order form). I need to change the value in the same cell on all the forms (manager's name needs to be changed on all our forms).
Question: How do I do this without having to open and edit all five thousand forms?
Is it possible to consolidate data from various worksheets into a single worksheet using multi reference points or lookup_values?
For example, I have 2 worksheets named Section A and Section B in which I have detailed monthly expenditure details (e.g. overtime, allowance, uniform, etc) for each sections, respectively. Then in the 3rd worksheet, called Combined, I have 2 columns - first is 'MONTH' and second, 'SECTION'. Now, suppose I have a 3rd column in which I want to pull over the overtime expenses for both sections. That means, my VLOOKUP must first look for the SECTION worksheet, then find the MONTH before returning the value found in the overtime column. How can I do this?
im creating a spreadsheet at work that gathers project and FTE figures. I have attached a copy, but ive had to totally simplyfy it to get it to the required uploadable size.
Basically each team member goes into the sheet tab with their name on it (the full version has about 40 sheets), enters the total hours per week they will be spending on each project.
Then the manager opens the spreadsheet, clicks on get data then enters the date. Data for the selected date is then shown. However I need excel to go into each persons sheet, take the names of the projects that will be worked on (obviously the ones that are not blank) then report the names onto the front sheet under "name of projects".
can anyone help me with this? as you will see, im self taught with vba etc so its probably not the best but im trying.
I have two sheets within one workbook, mean I have 300 columns, as u know excel sheet is limited to 256 columns, so I divided the columns in 2nd sheet, now I want to sort the data, but when I sort the first sheet data, the second sheet data not sorted,
I have raw data converted from pdf into excel Workbook spanning into many sheets.Each sheet having 5 sets of records. It is highly disoriented.Some times it is in 4 columns and some times in three columns having 30 unique Records which I Want to put in rows neatly in a single sheet .Raw Data is like in table below.In some Columns There are only 20 Records.I want them in rows organized under headers.
Name: Aa bb Fax:*5555 Team:United Actor:Arnold
[Code]....
Problem is that in some columns there are only 10 fields present,eg: name field is there Add,state,Country is not.Also in some columns many fields are in a single cell,eg: Tel,fax,Email in a single cell.
Each Record That Needed to extract starts after : Also I can Replace all required fields like name ,add, city, to 1,2,3 if that is going to work.
See attached. (I added in some arrows for the first month so it is easier to follow the logic)
I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.
Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.
Essentially, I need the formula to return the correct amount that is being paid, even across different years.
I don't know if my formula can be modified or a new formula would be more efficient.
(This post originated from Commercial Services, which I just requested be removed from that forum)
formular to show a coinage total in a spread sheet im sure its to do with < > but new to this and dont have a clue any help apreciated. This is what i am trying to achieve.
excel formula If A1= 10 Then B2= 0 If A1 =7 Then B2 = -3 If A1=13 Then B2= 3
I am working towards creating a spreadsheet for making a budget for a film + a system to monitor the expenses vs budget allocated on a day to day basis (to ensure the budget is not being crossed).
I would like to calculate the average number of hours worked per day in a particular week from my time sheet (in "Google spread sheets"). Daily hours are calculated in hr:min:sec format. Days not worked display 0:00:00. The formula I use to calculate total hours worked that weeK is for row 9 as follows: =INT( SUM(G9,K9,O9,S9,W9) )*24+LEFT(TEXT( SUM(G9,K9,O9,S9,W9) ,"HH:mm"),2)&TEXT( SUM(G9,K9,O9,S9,W9) , ":mm:ss")
How do I calculate the average number of hrs and minutes worked per day (excluding days not worked, cells containing 0:00:00)
I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?
Im trying to import data from a plumbing supply website to an excel spread sheet.
On the supply house web site, for each part catgory there are corresponding pages containing a price list data table.
there are from 1 to 3 pages (price list data tables) for each part category.
i can import data from page 1 and page 3 fine. When i try to import data form page 2, it returns no data, or imports page 3 (instead of 2)
I spoke to the person who designed the website and he said that it was a problem within excel and not a problem with his program. (said it was a problem with excel not being able to recognize the page)
Public Sub ListMyFiles() Dim fso As New FileSystemObject Dim fso_Folder As Folder Dim fso_File As File Dim file_count As Long
Set fso_Folder = fso.GetFolder("S:SHARED All of TransportationTraffic AssessmentMultiway Warrants") file_count = 1 For Each fso_File In fso_Folder.Files file_count = file_count + 1 Cells(file_count, 1).Value = fso_File.Name Next fso_File Set fso = Nothing End Sub This works great, but is there a way for the macro to recognize the file names that have already been added to the spread sheet and only return new ones that have been added to the folder?
I am currently working on a very large spreadsheet with a current size of 94mb. So obviously I have problems running and using it. My question is a general one, I need to understand better why a spreadsheet gets so large. It has 55 tabs with the largest having 1000 rows. It also links in a number of places between sheets but also to external locations. I have moved all files that it is linked to in to the same file. I am at the point where I am thinking about starting again! And I was wanting some tips or even a recommendation of a book that could help me optimize my spreadsheet going forward so to increase the speed but also reduce the size.
I got a problem with a time calculation, if it's even possible.
I want to calculate the amount of hours that have been woork in a slot.
Example: First slot is from 08:00am till 17:00pm, time cost 100% Second slot is from 1700am till 24:00, time cost 110% Third slot is from 24:00 till 08:00 am, time cost 120%
I'm working from 08:00am till next day 01:00am So my hours are: 9 in slot 1, 7 in slot 2, 1 in slot 3.
I have a spreadsheet with data across multiple worksheets and I am trying to figure out how to use the data from the multiple spreadsheets to create a single pivot table on a separate worksheet. I cannot reasonably bring all the data into one workshet because each one has thousands of rows. I want to pull data from select columns in each worksheet. How can I isolate only the data I need from different worksheets into one pivot table?
