If I have a worksheet that has a figure when the program calculates, how do I set up in another worksheet so that it tells me how many pages to print (that has information based on the 1st spreadsheet).
View 9 RepliesMy program(which is an add-in, or will be when I've finished writing it) creates a new worksheet called "Groups Summary" I would like this sheet to be locked so that the user can not modify anything on it. I would also like that if they double click a cell this will launch a userform if based on certain conditions.
I believe this code would have to go in the worksheet itself, so the question is how do I get the macro to enter code into a worksheet that it creates?
Is there a way to write it in a standard module and then automatically copy it when the worksheet is created?
I am trying to edit some formulas Under the Mon-D15 worksheet in this file. The # NUM is where I am having trouble. Basically, what I want to do is only rank the names in C15:C18. I was successful in doing this with C10:C14. But, for some reason it will not work with the lower values.
Question #2
Is there a way to make Columns I:O stay static through Rows 1:24 (width=3)?
I want Rows 25:40 to be different widths (width=2) in Columns I:O.
There is an autofilter applied to worksheet A, I would like to transfer only the autofiltered range from worksheet A to worksheet B, a clear worksheet B statement would also help.
View 9 Replies View RelatedI have a spreadsheet with 5 worksheets representing the working days of the week. Each worksheet contains data pertaining to every occurance of that day for as long as it has been recorded. Each day is broken up into hourly data.
So for example, for Monday's worksheet, you would have
Col A..............Col B........Col C
9/jan/06........5am.........data
9/jan/06........6am.........data
9/jan/06........7am.........data
...etc....then...
2/jan/06........5am.........data
2/jan/06........6am.........data
2/jan/06........7am.........data
...etc....
We are trying to create a function, on another worksheet called Query. The only way I can describe what we want it to do is to do it in psuedocode.
Psuedo........................................
I have been using the attached to pull multiple csv files into seperate sheets. This takes all files from a user specified directory and dumps them into a workbook. I only need three specific files. How do I request specific files instead of a wild card.
View 3 Replies View RelatedI need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet.
Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this:
No. Timestamp Tags Value
1 1-Jan-2010 Tag A 18
2 1-Jan-2010 Tag C 20
3 2-Jan-2010 Tag A 20
4 3-Jan-2010 Tag B 17
5 3-Jan-2010 Tag C 19
6 4-Jan-2010 Tag B 18
7 4-Jan-2010 Tag A 20
8 5-Jan-2010 Tag A 22
9 5-Jan-2010 Tag B 18
10 5-JAn-2010 Tag C 20
I am trying to run an SQL query to the worksheet of the workbook opened. This is worth noting, that this code is being called multiple times. Suppose if I have around 100 items in a listview, any item I will check or uncheck, this query will be fired. I tried selecting the five items, and the following code ran five times, and the excel ghost instance appeared.
View 4 Replies View RelatedI'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.
I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
Sample stock system LOADTESTING.xlsm has ten products. "LoadTesting" shows the opening stock for each. CSV file "Van Sales" has the Stock Number in Col M and the sales figure in Col N. I want the code to Open a CSV file "Van Sales" (which for some reason the site would not upload) Establish the sum total sold for each product (VBA equivalent of SUMIF) Deduct that sum for each item from the opening numbers on "Van Sales" (e.g. Product 001 started with 156 on the van, 92 were sold, so the "revised" van figure should be 64).
Can't get the code to link to the right "ranges" from the Van Sales workbook.
(You will have to change the file path calling the Van Sales file, but if you do get it to work,
VB:
Option Explicit
Sub GETSALE()
Dim Dest As Range, Dest1 As Range, Dest2 As Range, Dest3 As Range, oCell As Range, varResult As Variant
[Code].....
I have an entire row of if statements that basically check other sheets if a number occurs. If it does then it will display "Pending Approval" ,"Open", or "Closed" depending on which sheet the # appears on .. Just wandering if their was a better formula for this..
View 7 Replies View RelatedI have written an IF formula that only clacluates the first IF values in the formula and I dont know why.
Here is my formula: =IF(I2999;"1000";IF(I2>1999;"2000";IF(I2>2999;"3000";IF(I2>3999;"4000")))))
So if in I2 I have the value 800 it correctly returns
I need a formula to calculate the sum of a range of rows.
Example:
If rows 1-10 have a width of 12.75 each, and rows 11-15 have a width of 15.5 each. I need a formula to tell me that the sum of the widths of rows 1 to 15 is 205.
Any idea?
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
I have the following formula:
=TEXT(D2-C2-TIME(,60,),"[h] h
s: m mi
s")
It's taking out 60 minutes for lunch. Now I want to use this to calculate straight time and not deduct any time. How can this be modified?
I'm trying to make a worksheet that calculates monthly residuals. For example, if I have income of $275 in the first month and then the next month I have the first $275 and an additional $275, and so on. I've created the following formula
=RC[-1]+(RC[-1]*2)+(RC[-1]*3)+(RC[-1]*4)+(RC[-1]*5)+(RC[-1]*6)+(RC[-1]*7)+(RC[-1]*8)+(RC[-1]*9)+(RC[-1]*10)+(RC[-1]*11)+(RC[-1]*12)
where RC[-1] is the base amount that adds on every month. This gives me the number I am looking for. However, I am wondering if there is an easier way to do this.
Excel 2003 SP2
--------------
I have been programmatically setting the formulae in a worksheet. I have various formulae:
Example 1 - =COUNTIF(Data!M$6:M$9871,3)/$E$4
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 56.80%
The cell displays 56.80%, all is good!
Example 2 - =SUM(IF(Data!$J$6:$J$9871=$D8,IF(Data!$M$6:$M$9871=3,1,0)))/$E$8
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 53.37%
The cell displays 0.00% !?!
Column C contains zip codes.
Column D contains # of adults in the household.
Column E contains # of children in the household.
How might I create a formula that calculates a total # of adults AND children in Columns D and E based upon a series of specific zip codes (but not all the zip codes) in Column C?
The whole document works but the last one i need. I'll post it all just in case i have an error elsewhere.
Start Time -- Stop Time -- Break -- Total -- Hours Worked -- Average -- Pay
6:00 -------- 3:00 ------- Yes ---- 1600 -- 8.00 ----------- 200 ------ $xx.xx
E2 Formula for time: =IF(C2="Yes",(((A2*24+12)-(B2*24+24))+1),((A2*24+12)-(B2*24+24)))
F2 Formula for average: =D2/E2. and now for the problem one. G2 Formula for pay: =IF(F2<165, E2 * 7.25, IF(F2<180, D2 * 0.07, IF(F2<190, D2*0.08, D2*0.09)))
Just to break that formula down more:
0 - 164 = 7.25 * Hours Worked
165 - 179 = 0.07 * Total
180 - 189 = 0.08 * Total
190+ = 0.09 * Total
it only calculates 7.25 by the hours worked no matter what comes up in F2
The Excel file is in this link
[URL]
If you look at the chart on the left, it breaks down how this whole thing works. It takes the sum of the numbers that fall within each line. I wrote it out on column M so that it's more clear. In column D-L, I filled in random numbers ranging from 4000-700. The numbers in those gray boxes are ignored. This Macro should only apply to Column M and beyond. Is it possible to write a macro that will be able to do this?
using Office Professional Plus (2013). Within my spreadsheet, I have a series of progressive dates to be used in as a timeline. Ultimately, what I need to accomplish is that when I change one date, all the proceeding entries update according to my timeline criteria (in this case 3 weeks or 21 days). I created a SUM formula that worked but only calculated one time. Once the calculation completes, the cell drops the formula. I need this to be constant as the dates often change. How do I get the formula to maintain? Further more, is there a better method to accomplish the task at hand other than the SUM option?
View 7 Replies View RelatedI am having an issue creating a formula that calculates % of Budget based on signs. What I mean is positive budget, negative actual and vice versa. Here are all the cases. Can someone please create a formula that I can copy down.
Actual Plan% Budget
85,60696,85188.4%
70-82185.9%
-1,530-3,786247%
-100100-200%
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?
Please see an example:
Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer
[Code] .....
I guess it has something to do with AVG and IF formulas.
I need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
I have a column that calculates & displays the number of minutes between two cells, i would like to conditonal format the column to either display a 2-color scale by number, min 0 and max 40 or i would like to apply a fill to all cells > 40. I can do this with normal numbers typed into the cells but i cannot get it to work with the result of the formula. I have tried formating the cells to number but still i cannot get it to work.
View 3 Replies View Related