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 have included the table. What I want to do is in a user form pick a county ie Devon, Then by clicking "go" all entries that contain "Devon" are then copied to a " sheet 2" I don't want to use the Ctrl-F
View 2 Replies View RelatedI want to zero out or blank out my running balance in the columns where I do not have an amount entered. So instead of having my running balance total run all the way down the spreadsheet, I want it to be blank at the first cell where I have not entered in a new bill. I have attached an example of what I am trying to do.
View 2 Replies View RelatedI am copying a large sheet into a spread sheet. The problem is one of the columns contain numbers with some cells having an asterisk at the end of the number. This causes problems with the formulas on other sheets that use this data. Can I use a formula or what is the best way to eliminate the "*".
View 4 Replies View Related I have a spreadsheet with names on and I need to check to see if the surname in column C, also appears in column A&B.
I would like to list, in another sheet, the names that are in Column C that are NOT in A&B. Then I can see what data is missing.
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 have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
I was trying to compare string from excel spread sheet to a value I am not getting any output. It is not working
View 4 Replies View RelatedIs there a way to lock out a spread sheet if it is sent to an unauthorized e-mail address or forwarded to another e-mail address?
View 9 Replies View RelatedI 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?
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).
View 3 Replies View RelatedI 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?
View 9 Replies View RelatedIm 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 want to concatenate two columns in a separate tab but when I do, the values appear as ....
View 9 Replies View RelatedI 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 need a formula to figure the 5 lowest of last 6 sores, since everyone doesn't play every week there would be blank spaces and it needs to just give me the average if they have less than 6 scores!
I have manipulated the formulas until I am blue in the face and cannot get it to work;
I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
On the attached spread sheet here is what I am trying to do. In B2 I will either enter D (digital label) or F (Flexo label). With that than if a D is entered nothing else will happen with in spread sheet but if F is entered D23:Q40 will need to be cleared out and blank.
View 5 Replies View RelatedI am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E
Finish date is column F
Owner is column L
I am setting up a spread sheet to calculate call duration it would be a great help if data could be entered using one hand IE use decimal point on the number pad instead of the colon is this possible. I will need to subtact start time from end time to get the duration
View 9 Replies View RelatedI have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
View 9 Replies View RelatedThis 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?
Each sheet has the same basic formatting. A1 contains a name. B1, C1, D1 are column headers. B2:B is data. C2:C is data and always stops at the same row B2:B range does. The only differences between the sheets is that they might not stop at the same row. I want a macro that merges A1 vertically as shown in my spread sheet to the end of column B and C. I want a border around the merged data, as well as around the B data and the C data individually.
View 3 Replies View Relatedi have a spread sheet which has a very complex simulaton in it. excel is not the ideal place for it, but it is not possible to port it wholesale to something more sensible. i have modified the code so that it is possible to run two copies of the spread sheet (in two seperate excel processes) at one time. Due to the dual core nature of the machine this has almost no overhead.
I have created a controlling spread sheet that is cabable of launching multiple copies of the simulation and starting the code executing. the problem is the controlling spread sheet always waits for the code to return before it continues, hence it would open two seperate versions of excel, start the first simulation executing and then wait until it completes before starting the next one. is there any way of starting a function in another work book without waiting for the execution to complete?
i have a list of parts that adds up and shows a total at the bottom (great, works perfectly)
in the next column i have a percentage (margin) which again works perfect
ie.
Col A Col B Col C Col D Col E
1 ENGINE 4500 5000 11%
2 SURCHARGE 1800 2000 22%
3 OIL 1 2 100%
Totals 6301 7202 ??%
what i would like to do (forgive me if i have missed something) is for the total value in colum D to add up but for the % in column E not to be included in the total %
Is it possible to have a situation that when the word"SURCHARGE" is in the row then the % will not be added to the total %
In other words i need the surcharge price to be included into the final price, but the surcharge would give me a false figure so that needs not to be included in the total %
I've attached a sample workbook. Data Table 2 on the Data Tables sheet is what I'm after. Basically I have a formula that sums up quantity 1 (Q1) and quantity 2 (Q2) for all widgets at a site name and month. So for example, go down through column site name and column month and add up all of Q1 for site1 in the month of Jun 2014 (all widgets). This works but it shows duplicate totals which makes sense as there are mutliple widget types at each site. I think the example workbook explains it better. Note the Pivot Table sheet is an example of what I'm showing currently and what I'd like to show. While I understand that depending on how I set up the Pivot Table I can get similar results using either Table 1 or Table 2 but the other thing is that my users like to do some filtering of the data sheet itself without regard to the Pivot Tables.
sum quantity and show total for each site.xlsx
I created a spreadsheet to track my bowling scores by date by lane
Date Lane game 1 game 2 game 3 total
What I would like to do is show my total average by lane for the season. 33 weeks. From there I think I can graph it out.
I'm fairly new to Excel (2007).
I have used a simple COUNTIF formula (=COUNTIF(C7:C207,"Name")) on sheet 1 to give me the total I want.
What I now want to do, is display this total on a different sheet in the same workbook (Sheet 3) without having to take all the data from Sheet 1 over to Sheet 3.