SUM DATA IN SHEET TOTAL DEPENDS ON CRIETRIA
Aug 16, 2009WHAT'S BEST FORMULA TO SUMMING DATA IN SHEET TOTAL DEPENDS ON CRIETRIA 1 AND 2
View 9 Replies
ADVERTISEMENT
Formula With Elements That Depends On The Total
Sep 23, 2008We have a need for formula that works out cost pricing but 1 of the items is based on the total cost excluding that item (VAT) and another is based on the total price (Cost of payment solution)...both are percentages of the respective factor. The way we have tried at the moment produces a circular formula...is there a way around this without a circular formula or a way to make a circular formula work to ensure profit is £0?
View 9 Replies View RelatedMacro Allow To Total The Data On The Total Sheet Depending On What Unit Number Is Selected
Apr 22, 2009This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
Data In Form Combobox2 Depends On Selection From Combobox1
Apr 25, 2014combobox's.. I have it working as I need it to with Active-x combobox but I need to move to a form method because the Active-x combobox is buggy in the worksheet and has these strange font and box resizing issues which there is no solution to out there.. Something about if you have external monitor or projector plugged in and it using a different resolution to what your native laptop or pc is using.. doesn't make any sense to me and unfortunately I have given up on it.
Problem is what I want doesn't seem like it can be done with form combobox but I will seek your support first before I come to that conclusion.
So using form Combobox, this is my setup:
Combobox1 contains a short list of Teams i.e. ENT,MEL,EMEA,MTVSSL,TSJ -- I am referencing this from the FORMAT CONTROL property Input Range: TEAMS!A2:A6
Combobox2 I need to list the names of the people in those teams i.e. John Doe etc.. I have column with teams as headers and the people in each column in the respective teams. i.e. B1:B16 has the list of people for the team called ENT (B1 is the header "ENT") and B2:B16 is the names of the people.
Problem is I don't know how I can link the two based on selection in Combobox1?
One catch, I prefer not to hard code the names of people in any code, if I can reference from a table or cell that would be better as the list of people within a team will increase over time.
Data In Form Combobox2 Depends On Selection From Combobox1
Apr 25, 2014 I have it working as I need it to with Active-x combobox but I need to move to a form method because the Active-x combobox is buggy in the worksheet and has these strange font and box resizing issues which there is no solution to out there.. Something about if you have external monitor or projector plugged in and it using a different resolution to what your native laptop or pc is using.. doesn't make any sense to me and unfortunately I have given up on it.
Problem is what I want doesn't seem like it can be done with form combobox but I will seek your support first before I come to that conclusion.
So using form Combobox, this is my setup:
Combobox1 contains a short list of Teams i.e. ENT,MEL,EMEA,MTVSSL,TSJ -- I am referencing this from the FORMAT CONTROL property Input Range: TEAMS!A2:A6
Combobox2 I need to list the names of the people in those regions i.e. John Doe etc.. I have column with regions as headers and the people in each column in the respective region. i.e. B1:B16 has the list of people for the team called ENT (B1 is the header "ENT") and B2:B16 is the names of the people.
Problem is I don't know how I can link the two based on selection in Combobox1?
One catch, I prefer not to hard code the names of people in any code, if I can reference from a table or cell that would be better as the list of people within a team will increase over time.
VBA Export Data To Path Depends Upon 1 Combobox Values Sheets And 2 Textbox Date Values
Jun 5, 2014I have set of user-form contains with Combox & 2 textbox and to generate report one cmd button
I have 3 different sheet contains report of daily activities ( Dispatch,Closed,Cancel)
If Dispatchcalls Select In Combobox1 ,Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from dispatchcalls Then Save Data Into Excel File As "Dispatchcalls".
If Closedcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Closedcalls Save Data Into Excel File As "Closedcalls".
If Cancelcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Cancelcalls Save Data Into Excel File As "Cancelcalls".
"C:UsersmaniDesktopNew folderLenvo_ReportsONSITE CasesVlokupuf" This is path i stored existing 3 file dispath,closed,cancel
Calculating Total Time In One Sheet And Pasting Results Automatically In Another Sheet
Apr 21, 2014I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
How To Add New Column Of Data That Depends On Value Of Other Column
Jun 10, 2014Original table
Date Target
May 1, 2014
May 2, 2014
May 3, 2014
May 4, 2014
I have an excel table populated with 2 columns Date and Target where Target is a binary column which will be filled with either 1 or 0. I want to write a function such that if date is before May 3, 2014, then populate the Target column with 0. Otherwise, populate the column with 1.
Output I want
Date Target
May 1, 2014
0
May 2, 2014
0
May 3, 2014
1
May 4, 2014
1
Value Depends On Previous Values
May 21, 2007I am analysing data to determine buy or sell transaction signals. The problem is that the method can and does produce frequent signals that are merely confirmation rather than new information.
Therefore, I need to select a signal, eg a "buy" signal where the previous transaction signal was the opposite, eg a "sell" signal.
In the attached sheet N10 is a valid signal as the previous signal was a "sell" signal. However, N14 is not valid as the previous signal was also a "buy" signal.
Currently, I am using a simple formulae to determine the signals, e.g. =IF(J26>0,J26,0). Hoevever, I need to modify this by looking back to confirm that a value >0 occurred first in column K rather than column J. If the value >0 occurred first in column J then I need to ignore the buy signal.
I am not sure if the formulae can be modified or if a macro is needed.
Shift Cells Down Depends On Condition Of Others
Oct 20, 2009Whenever "online" appears in the "Desc" column I need to shift the matching row down (shaded area). for example, "online" appears in H2, hence, C2:E2 need to be shifted down to C3:E3 leaving C2:E2 blank. Next, H3 has "online" there, C3:E3 need to be shifted down one row so the result will look like the data on the "result" tab.
View 5 Replies View RelatedContents Of TextBox Depends On CheckBoxes
Aug 15, 2012I have 4 checkboxes and if they are all unchecked then I need some text in the textbox. If even one of them is checked, then some other text in the text box.
Here is the code that I have been playing with:
Code:
Private Sub TextBox2_Change()
Dim check As OLEObject
Set check = Sheets("Sheet1").OLEObjects("CheckBox1,CheckBox2,CheckBox3")
[Code].....
Copy The Entire Row Depends On A Cell Value
Jan 9, 2009I would like to create a macro for my boss, their requirement is, in sheet 1 , they have one column, To chase . So when type "y" in any of the cell in a 'To chase' column, then copy the entire row and paste to sheet2.
Also is it possible to do this any selection change event of the cell?
Calculate The Sum Of Part Used Depends Upon End Of Serial Number?
Apr 8, 2014I want excel formula to calculate the sum the value of partused depends upon serialno repeats where repeats of end of serial no i want result at end of every multiple serial no end cell.
View 2 Replies View RelatedAdd Item To Combobox Depends On Multipage That Is Active
May 28, 2014I have a Multipage with 3 pages in userform1, and I just want to add an item to the combobox in userform2 depends on the multipage that is active.
I have the below code but I'm getting an error 'Object doesn't support this propert or method'
Commandbutton in Userform1
[Code] ......
Combobox Values Depends On Cell Status?
Oct 2, 2013I have a worksheet with three columns in it. the first column is the "channel column", the second column is the "status column"; which indicates the status of the channel column wheter it is active or not, the third column is the "circuit name" column which indicates the circuit name for each channel. now, the status of the channel depends on the value of the circuit name column.. if the cell in the circuit name column is blank, the status column has an if formula that automatically changes it to vacant status, otherwise active.
i added command buttons namely "active" and "vacant". this buttons correspons to active userform and vacant useform when clicked. in each userform there is a combobox. My problem is how would i fill the combobox in the active userform with the active channels only , and same in the vacant userform with vacant channels only. the comboboxes list should correspond to changes in the status column, that is if one channel gets an active status it would be deleted from the vacant userform and be added to the active userform
Create 26 Folders (depends On Financial Year)
Apr 16, 2009I want to create 26 - 27 folders (depends on financial year)
Basically in the current folder I want the folders to be created in the following format FE 22-04-09 for each fortnight in the financial year.
Change Events - Depends On Cell Values
Jul 6, 2009I receive real time data ( Last Trade Price ) of soybean commodity futures through DDE in to excel ( cell A1 ). During market hours A1 will keep updating every milliseconds or seconds.
My cell B1 ( =A1 ) will have same value as A1 and will update at the same time.
What I'm looking for - A macro code so Whenever value in B1 is between 9.5000 and 9.5050, I want run a subroutine ( similar to calling a macro ). Since B1 would change dynamically so everytime B1 comes between range 9.5000 and 9.5050 a subroutine is fired.
I'm planning to CALL following subroutine -
Sub BuyBeanst()
Range("N9").Activate
ActiveCell.Value = "Y"
Range("T10").Activate
ActiveCell.Value = "Y"
Range("T11").Activate
ActiveCell.Value = "Y"
End Sub
Display Selected Cells Depends On User Input?
Jul 17, 2013I am trying to display number of lines which depends on value pass by user. Actually user is passing some value. on which some group of lines has to be display.
eg
for 1 value 9 lines
for 2 value first 9 lines + another 9 lines
for 3 value first 18lines + 9 lines
and so on till 52.(this 9 lines are set of some column and rows)
thus cant use macros..
Creating A Formula To Calculate A Result That Depends On 3/4 Criterias Been Met
Apr 28, 2008Firstly its probably best you download the file from below and open it up.
http://www.mediafire.com/?un3smhmdyzt
I need a formula to put in cells in column S on sheet March.
It needs to match up the correct price from sheet 'Container Price List'.
The price that should be displayed depends firstly on the account number, container and waste stream all matching to decipher which line the price is on. Then the price displayed should be the price in the column called 'exchange charge' however if this is 0 it should be the price in the column called 'rental charge'.
So for example, if you look at row 27 in sheet 'March' the price displayed would be £80.39. The formula will look at the account number 'c028' on sheet 'March' and match it to the any of 'c028' on sheet 'Container Price List'. It will then look at the container in sheet 'March' which is RL14 and match it with the ones that are RL14 in sheet 'Container Price List'. Then it will look at the Waste Stream in sheet 'March' which is GEN.C and match it with the GEN.C in sheet 'Container Price List'. The result given should be the exchange charge in sheet 'Container Price List' which in this case is £80.39.
If for example the exchange charge displayed £0.00 then the result given should be the rental charge in sheet 'Container Price List' instead.
Display Number Of Lines Which Depends On Value Pass By User
Jul 17, 2013I am trying to display number of lines which depends on value pass by user. Actually user is passing some value. On which some group of lines has to be display.
E.g.
for 1 value 9 lines
for 2 value first 9 lines + another 9 lines
for 3 value first 18lines = 9 lines
and so on till 52.
Time Sheet Total
Nov 23, 2007I am putting together a time sheet for my peoples and can not figure out how to total the hours and remain in hour format, so I have the first employee's start time in B10, Stop time in B11 and total in B12, then the second in C10, C11 and C12 etc etc a grand total of all employees in H12 how would that be entered?
View 9 Replies View RelatedVBA Sum (summary Will Appear At The Bottom Of The Sheet With A Total )
May 22, 2009I have a worksheet which contains details of a storage rack. I would like to be able to identify lines which have identical values in columns B, C and D then take the value in column E and add it to all other occurances when B, C and D are identical. A summary will appear at the bottom of the sheet with a total next to it.
The full list is 600 rows in length....
Total Cell Count In A Sheet
Jun 11, 2007how can i count the number of filled rows and columns (containing both characters and numbers) in a worksheet through a macro? do not tell the individual functions like count or CountA.
View 5 Replies View RelatedCreating Running Total Of Different Sectors From One Sheet
Dec 11, 2013I need to create running totals for each year of the Contract Values for each Sector that my Company deals with. There are 5 Sectors - C, M, W, WW and WtoE. Each possible contract has its own Contract Value.
For eg -
SectorContract Value
W € 36,779.33
WW € 20,325.20
W € 246.14
W € 2,168.40
WW € 176,617.00
W € 320.65
[Code]...
In the whole document we have previous year's contracts too - but I want to create running totals for each year - i.e. for 2010, 2011, 2012, 2013, 2014. How could I separate these easily?
Obviously the total depends on which sector we are looking at - I thought about using the IF formula - but realised that there would be a lot of IF's - is there an easier way of doing it? If not - what's the best way?
To Show A Coinage Total In A Spread Sheet
May 8, 2009formular 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
Copy Content Of All Worksheets Onto One Total Sheet
Jan 13, 2009I have 11 worksheets in one workbook, the last worksheet is a total page.
I'd like to run a macro that copies the information from each sheet and pastes it into the total sheet. My range on every sheet begins at A2, but the end of the range is unknown.
All I have so far is trying to loop to a new empty cell on the total page (coded in a module - is that right?):
Copy Same Range From 4 Sheets To A Total Sheet
Oct 23, 2007I need to combine data from 4 tabs with employee names, onto a tab called total. The tab named total is hidden. Is there a way to do this using code?
In my sample I need the name then dates and data below in the yellow areas. As long as Column a has data above cell A22.
Double Click On A Total It Opens A New Sheet
May 15, 2009i am working on a pivot report and it works fine so far, but the only thing i am trying to do here is when duble click on a total it opens a new sheet with the report needed but it names the sheet as sheet1 and so on (sheet2,3,4...)
is there a way when i duble click a total it automaticly names the sheet for me?
Running Total In TextBox On UserForm From Sheet
Jun 21, 2006I am trying to get running totals in three TextBoxes on a UserForm. The UserForm is to enter one item from a receipt which can have many items. The running total is for the number of items on the receipt, amount of discounts (coupons), amount of sales tax and the grand total. A small sample is attached
View 4 Replies View RelatedRunning Total- Sheet That Tracks Thousands Of Records
Dec 3, 2009I am working on a excel sheet that tracks thousands of records. Basically, have 400+ employee's who have scores that need to be tracked. The scores are % based... to start we have 15+ columns but all that matter for these calculations is probably 3. We have score 1 which their required to hit 90% score 2 which is 80%, they are seperate scores. Some people dont count so we disqualify the formula from counting a 0. My current forumla is...
COUNTIF(K418,"<90")+COUNTIF(N418,"<80")-COUNTIF(N418,"=0") where the rows change for each record. We are counting the # of times they don't hit their goal. They can only be held accountable for one score a month not for both scores so created a 2nd column which has =IF(O418>=1,1,0) so if it came back with 2 counts it changed it to 1. OK, so background explained
Now the tricky part... we have a running total per month of how many times they did not hit their goal. We have a column with the mm/yyyy. So we have a pivot table which breaks down the associates by name department etc... and once completely drilled down into it gives a breakdown of their score for every month and then if they did or did not hit their goal by referencing the previous field I discussed. Currently it has a running sum, but I am stuck on the twist I need to throw in... if they meet their goal for 3 months straight, 1... we will call it "occurence" drops off. So for example.... Jan 1 Occurence, Feb 0, Mar 0, Apr 0. So in this case, Jan, Feb, Mar need a running total of 1. Once april hits, the running total needs to go back to 0. So if they got an "occurence" in May, the running total would go back to 1 instead of the current 2 my sum shows.
I imagine there has to be some way I can do this, but I have been thinking about this and hit a brick wall. Any idea's at all? For reference column A is the mm/yyyy (which can be reformatted differently pretty easily). Column K is score 1, column N is score 2, those are the 3 fields I reference for my forumulas currently, the pivot table uses everything else.
Here is an example of one associate.... the top row is the average for the associate and the sum of "occurences"... I want to or plan to have a running total section added on the side however if possible. This associates running total should be 2 instead of 3 as there was a 3 month gap for Mar, Apr, May so it would have been -1 in May as explained above.
Doe, Jane103.7788.823
1/1/200998.5894.120
2/1/2009101.6270.591
3/1/2009110.61105.880
4/1/2009131.78117.650
5/1/2009102.0588.240
6/1/2009110.4658.821
7/1/200998.23105.880
8/1/200986.8270.591
9/1/2009100.3982.350
10/1/200 97.1694.120