State And Local Sales Taxes
Feb 21, 2009
Our state carries a 4% sales tax on all items except food and prescriptions.
Our county carries a 3% sales tax on everything.
Attached on my work sheet:
Column "C" determines if an item is either food or non-food.
"G5" is the subtotal of column G
"G4" is the S/tx on "G5" at 3%
"G3" is the S/tx on "G5" at 4%.
"G2" is the gross pay out.
My question is:
I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C".
If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4" If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".
View 2 Replies
ADVERTISEMENT
Jun 3, 2012
I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.
View 7 Replies
View Related
Dec 16, 2009
I have attached a sample workbook of what I am struggling with. It is for payroll, and I need to calculate taxes and pension as a percentage of Total W-2. The problem is that the 'Total W-2' column then deducts these taxes and pension.
Currently, the worksheet has about 30 columns out to the right using 'guesses' to back into these numbers, but I hope there is an easier way.
View 14 Replies
View Related
Jan 5, 2012
I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....
All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.
Column A Column B
201 $200
202 $300
203 $450
View 8 Replies
View Related
Jul 2, 2009
I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.
View 3 Replies
View Related
Feb 4, 2013
I wanted to get the data from data sheet where i have entered the data manually for sales what have done
Now I wanted is when i enter date i should get the product and price in other sheet
Example:
If I enter date in a1 cell
I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet
And c1 - c10 price (if i sold 10 item @that price)
View 4 Replies
View Related
Feb 20, 2008
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies
View Related
Oct 17, 2013
I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....
The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.
Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....
View 6 Replies
View Related
Jan 5, 2010
I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.
View 4 Replies
View Related
Jan 4, 2005
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.
View 9 Replies
View Related
Mar 27, 2013
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
View 4 Replies
View Related
Oct 3, 2012
In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
View 9 Replies
View Related
Oct 14, 2009
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
View 10 Replies
View Related
May 25, 2009
I have a worksheet that derives, in cell C12, "Revenue Before Return and Taxes". Based on and from this number, I need three (3) formulae to compute (1) in cell C14, a "Return" which, for these purposes, is equal to "x", (2) in cell C16, a Federal Income Tax provision which is, for these purposes, equal to "x*(.35/(1-.35))", and (3) in cell C18, a State Income Tax provision which is, for these purposes, equal to the sum of (a) the Return, plus ( the Federal Income Tax provision, multiplied by 6%, or "(x+(x*(.35/(1-.35))))*6%". All three of the results in cells C14, C16, and C18 must equal the total in cell C12, "Revenue Before Return and Taxes". What I need are formulas to come up with the different pieces, and they must be based on the "Revenue Before Return and Taxes" figure in cell C12.
View 9 Replies
View Related
Aug 20, 2013
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
View 1 Replies
View Related
Jan 10, 2007
I have created a form wherein i have created a textbox to enter the excel filename and three different combobox to select the year, month and the day respectively. These are actually the folders on my hard drive and the excel files which i want to open is inside these folders with similar names. I want to search these files for the selected year, month and day from the combobox. The filename will be provided in the textbox. the path for the files is say c:yearmonthday*.xls
View 6 Replies
View Related
Mar 7, 2007
I would like to, using VBA, assign a hyperlink to an excel cell that links to another worksheet within the same workbook. I've tried the forums, and combined with a previous post and MS Excel Help File, I've come up with the following attempt:
With Worksheets("Test Destination")
.Hyperlinks.Add Anchor:=.Cells(counter + 4, 5), Address:=strHyper2, _
TextToDisplay:=(#1/1/2007# + counter)
End With
strHyper2 is previously defined: strHyper2 = "[WeatherTester.xls]1!A1"
My spreadsheet file is "WeatherTester.xls", my worksheet is "1", and "A1" is the desired link location for the cursor. Counter is an integer. I get the error "Invalid procedure call or argument". I also tried to enter a hyperlink directly through the formula property, but Excel didn't like the single quotations I had to use within the HYPERLINK function. The hyperlink formula works in the following form: =HYPERLINK("[WeatherTester.xls]1!A1","1 Jan")
View 3 Replies
View Related
Dec 3, 2008
I need to know how to read a filename from my local drive c: ? for example in my local drive i have some 100 files, i need to pick there filename, date and time stamp .. etc.
View 7 Replies
View Related
May 8, 2006
I am working on a process model, which requires that the same basic procedure be carried out for each unit in the process.
One worksheet is allocated to each unit with data concerning the flow streams being passed from the previous unit and the product being linked to the next unit. Although the same procedure is therefore being followed on each sheet, the input data differs.
I currently have the code to perform the required technical operations and calculations for a single unit. This code is very much dependent on named ranges.
I want to use the same core to perform the routine-operations, but need either unique named ranges on each page or I need a way to define name that is limited to the specific worksheet on which it is located.
For example, I might have an input range for a heater and splitter, with a technical add-in in VBA performing calculations on the input for each unit. How can I use two separate instances of the name "Input" instead of "Input_Heater" and "Input_Splitter"?
View 3 Replies
View Related
Jul 16, 2006
The default program for opening JPG files is "Windows picture and fax viewer" which is fine with one of my colleagues. Upon declaring and clicking on a Hyperlink, to a JPG fle, the picture is opened in "Microsoft photo editor". How can one define the program in which Pictures opens via Hyperlinks will be displayed!? (WINXP & Office 2002)
View 3 Replies
View Related
Apr 14, 2014
30 people in different countries use a sophisticated VBA Excel file on their local PCs, let's call it myexcelfile.xls. This file contains no custom data, as all data is stored in myexcelfile_backup.xls.
The biggest challenge with this set up is that whenever there is a newer release or bugfix of myexcelfile.xls, I need to send it out per email to 30 people and hope they replace it with my updated version.
Note that newer versions can replace older versions without any data loss, as all data is stored in the backup file.
Idea: with tools like WGET or BITSADMIN, I was thinking of creating a batch file, that would simply download the latest version from a FTP server, and replace the older version with the newly downloaded one.
Constraints: I shouldn't be using any software that is not native to Windows 7the files can sit in different folders on these 30 computers, therefore the update mechanism would have to identify the path they are in.the update mechanism would have to close the file first before running the update. Question: What is the best way to go about this? A DOS Batch file? Or another excel file that is just the "updater", meaning, it's only purpose is to download the new file from the web and overwrite the old file?
View 1 Replies
View Related
Feb 17, 2009
I have a macros in my spreadsheet when I open it a number in the macros goes up 1 each time the worksheet is opened. I want to put this file on a server that can be accessed by various people. The problem I am getting is every time someone opens it from a different location with a different PC the number starts at 1 again. I want to keep the number going up 1 each time no matter who opens it.
View 4 Replies
View Related
Apr 26, 2009
I have Excel 2003 SP3 on Vista Home Basic fully patched. My code runs very slowly until I pull out the ethernet cable then it runs quickly.
View 9 Replies
View Related
Jan 28, 2010
I have a program operating in excel 2007 that extracts a picture from a URL and puts it into a PPT slide. I now discover that PPT 2007 doesn't allow this.
So my intent now is to download the jpg into a local file and then import into the PPT.
This is from a couple of sites:
[url]
[url]
At the top of the code:
Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long
in the excel cell if a URL picture needs to be inserted it starts with the prefix "picture=" then the http address.
This did not work.
So I then copied the code from the websites into a module (changing the destination file and URL to mine of course) and called the procedure and still nothing.
View 7 Replies
View Related
May 20, 2006
use an address in a cell and http://local.live.com/. i would like to paste an address into http://local.live.com/ from a cell (or concatenation) into the "Where" textbox...?
View 2 Replies
View Related
Jun 18, 2006
I am trying to wirte a macro that will allow my users to import data from a HTML file locatedon their local hard drive. THe directory information will not change however I would like the opporutnity to "choose" the appropriate file prior to completing the macro. Do I need to construct a form first?
Sub QuestionGetExtData()
Range("H11").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;C:foofoo.html" _
, Destination:=Range("A11"))
.Name = "foo.html"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False......................
View 2 Replies
View Related
Feb 2, 2007
I have an Excel 2003 application that automatically opens and closes a lot of workbooks. When done with a workbook, I close it with: ActiveWorkbook.Close SaveChanges:=False
On most users' machines, this works well. On one user's machine, the procedure is still asking: " Do you want to save the changes you made to 'filename'? "
at each workbook close. Considering that a typical run might inspect over 2,000 workbooks, this is a problem for the user. Is there a setting that can override "SaveChanges:=False"?. If so, where would I find it, and how would I programmatically turn it on/off?
View 2 Replies
View Related
May 4, 2007
I'm working on a spreadsheet that will connect to a firebird DB (Programmed using VBA and ADO intially).. The spreadsheet itself will reside on a server in a shared folder..
When a user opens the file up, I'd like for the spreadsheet to use the servers ODBC connection, and not the client machine.. This way we don't have to install the Firebird ODBC driver on each client workstation..
View 9 Replies
View Related
Jan 16, 2010
What is the code to open a URL or local html file with a command button?
View 4 Replies
View Related
Jul 13, 2007
Case "TD"
strfilename = "\TomsblackibmTomsProposals" & strfilename
I would like to send this file by email if possible instead of to his computer. The user recently went from a desktop "in the office" to a laptop "all over the place".
Sub Save_and_SaveSalesman()
Dim strPath As String, strPath2 As String, CurrPath As String
Dim WB1 As Workbook
Dim WB2 As Workbook
Set WB1 = ActiveWorkbook
'First thing, save my work
WB1.Save
CurrPath = WB1.Path
'ASSUMING THAT C6 and O3 are BOTH in WB1
'move this line HERE: only do this once, and concatenate in the Select..Case later
'doing thsi inside the Select..Case pulls values from WB2, which might cause errors.............................
View 9 Replies
View Related
