I have this data:
Valor
5
2
5
3
16
1
2
Progressive Total Sum can't > 28:
How can I get this in column "NrPag":
Valor NrPag
5 1
2 1
5 1
3 1
16 2
1 2
2 2
i.e.:
5+2+5+3--> < 28 so page N.1
5+2+5+3+16 > 28 after 3 I need page N.2
...
3 1
16 2
1 2
2 2
It now requires a special chart, I am trying to replicate what this other program does, but is slightly corrupted and it's chart section does not seem to work on XP.
The program was made in DOS so I can't provide a Print-Screen shot to show how it looks. If it gets too confusing describing the graph, I have to get XP to somehow run a DOS program correctly so the graph may appear to get a Print-Screen shot. The chart shows the "best 3" NEGATIVE rise in a progression and the rising lines are colored in red. If the other values are POSITIVE, it shows a fall in Blue. ( black background). So if the value of Item 1 is -17.5% and the next Item 2 is -12%, then the red line of Item 1 will show the highest.
BUT, it sort of follows the line back from the OVERALL percent values at the end of the calculation from the start of the first percent value, it's first entry. I have included a sample sheet, all I need to know if a progressive chart can be created with the way I have positioned the numbers, if not let me know how the numbers need to be positioned and it's simply a matter of me making a few changes in the workbook that processes the sums to suit. Finally, the amount of entries can be anywhere from 3 to 30, I have shown in the example 9 entries. The purpose is to show a progression of price changes using percents as the indicators.
I am running a sales spreadsheet that requires sales entered each day. I use a simple formula to run a continuous total starting at Monday and ending Sunday (1st cell for sales input is B4, second D4, third F4 and so on. 1st Cell for accumulated sales is C4, second E4 and third G4 and so on.
I use B4+D4 to caluculate the running total in E4 or example,and to eliminate that total appearing in G4 where the next formula is B4+D4+F4 I precede that with an IF formula IF(E4=0,'', ). This way I only show the current Week to date totals, not the upcoming days in the rest of the week. My problem lies in Holidays. If for instance D4, the Tuesday was a holiday and I enter either "holiday" or just leave it blank, the rest of the weeks formula will not work. I get a Value message. How can I achieve my goal of getting a daily week to date total without having it show in the upcoming days AND have a holiday in there too?
I have a column of data showing trade results - in the most basic way I can explain, as my account value GROWS by a preset amount (say 20-30%) how can I ADD to the number of contracts I am trading. For example I start with $10,000 and the account grows to $12,000 or 20% - according to my own trading rules, this allows me to ADD an additional contract to trade (I start with 1 contract). As the account continues to grow progressively 20% from the previous 20% jump, I add ANOTHER contract.
and to top this off, I do the same in reverse, If I lose or go into a drawdown .. how do I reduce my number of contracts traded?? so lets say I am up to trading 4 contracts and I lose say 10% of the account value - I want to slow down or "ease up" the number of contracts until I get the account back to its previous high. Anyone know how this can be programmed??
One last final caveat - when you go on a winning streak, I add for every 20% INCREASE in the account value and when I lose I reduce contracts at every 10% pullback to preserve winnings as much as possible.but I am ALWAYS allowed to trade a MINIMUM of 1 contract
ok, this may be the same answer as the previous post i did, but if so, i can't figure that part out.
In O2, if M2 = 8, and N2 = 2008, then add O3 + 8 rows (O3:O10)
or, if M2 = 5, and N2 = 2009 then add O16 + 5 rows (O16:O20).
I have a page i want to print fx. 50 copies of, then when i choose to make 50 copies it should automaticly insert this on the page:
Page 1 out of 50
Page 2 out of 50
Page 3 out of 50
and so on...
I came across the solution to this post - [URL] ..... - and was just wondering how to adjust the formula so that it calculates the correct tax starting from the net amount.
Say for example, an employee is paid a fixed, after-tax salary amount, and the employer is responsible for covering the tax - the salary payment needs to be "grossed-up" to calculate the correct amount of tax.
With 'tax brackets' as follows:
Taxable incomeTax on income (2011 – 2012)Tax rate
$0 – $6,000Nil0%
$6,001 – $37,00015c for each $1 over $6,00015%
$37,001 – $80,000$4,650 plus 30c for each $1 over $37,00030%
$80,001 – $180,000$17,550 plus 37c for each $1 over $80,00037%
Over $180,000$54,550 plus 45c for each $1 over $180,00045%
The formula to calculate tax, starting from the gross amount, is:
[Code] ......
Each row in Table 1 represents an "Expanded" array formula.
In Table 2 I have a "Running" formula to return progressive max values working Left to right.
My question is, can this be written as an array?
Returning something on these lines ...
{(IF(B1="","",MAX($B1:B1))),(IF(C1="","",MAX($B1:C1))),(IF(D1="","",MAX($B1:D1))),(IF(E1="","",MAX($B1:E1))), etc.}
Drag B7 across and down to see what the array should contain for each row in Table 1
Sheet1 ABCDEFGHIJKLMN1Table
1 1234 42 141 5 3 53 1 46 423 64 122 4567 89105 122 4657 89106 7Table
2 1234 48 144 5 5 59 1 46 666 610 122 4567 891011 122 4667 8910Spreadsheet
FormulasCellFormulaB7=IF(B1="","",MAX($B1:B1))
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
View 3 Replies View RelatedI have several series of 13 figures (from 0 to 20), such as :
000141833000001
I would like to filter those series with the following condition :
Each figure (except the zeros) should be higher or equal to the first figure (found on the right) which is not a zero.
In the example :
14 should be higher or equal to 18 : FALSE
8 should be higher or equal to 3 : TRUE
3 should be higher or equal to 3 : TRUE
3 should be higher or equal to 1 : TRUE
So, the serie is FALSE, due to the fact that 14 is not higher than 18.
I'm using a procedure (PageSetupXL4M) which uses the XL4 Page.Setup function to format a sheet in VBA--since it's much faster than VBA's current PageSetup function. (It's explained here: [url]
The problem is that the XL4 Page.Setup function seems to clear all the automatic page breaks. I'll use "MsgBox Sheets(1).HPageBreaks.Count" to display the page break count both before and after the XL4 function. It'll show the correct # of PageBreaks before the function, but shows 0 after.
I need to return the correct # of PageBreaks AFTER the function is run because the formatting of the page can sometimes alter the number of PageBreaks.
how to get "HPageBreaks.Count" to recalculate and return the correct number of breaks?
The find value will always be the same...its just a placeholder that is randomly inserted. (currently i am using the value "Placeholder"). basically in column B i want to insert the iteration of the Place holder. the attached spreadsheet better illustrates what i am trying to do. starting the count at zero is kind of important, but not the end of the world if it is impossible.
View 3 Replies View RelatedI have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.
Private Sub ParseInjuryPage()
On Error Goto ErrorHandl
Dim strPage As String
Dim webIE As SHDocVw.InternetExplorer
Dim myURL As String
Dim tableBeg As Long
Dim tableEnd As Long
Dim RowBeg As Long
Dim rowEnd As Long
Dim cellBeg As Long
Dim cellEnd As Long
Dim strBeg As Long
Dim strEnd As Long
Dim myCell As Range
Dim rowNum As Integer
With Sheets("INJ")
Set myCell = .Range("A2")
.Range("A:F").Value = vbNullString
rowNum = 2
Set webIE = New SHDocVw.InternetExplorer
myURL = "http://www.sportsline.com/nfl/injuries"
webIE.Navigate myURL
Do Until webIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
strPage = webIE.Document.body.innerhtml...................................
I suddenly can't adjust my page breaks in any Excel spreadsheet while in Page Break Preview. Is there some option to turn it on and off?
View 7 Replies View RelatedI have a sheet that changes the number of columns based on data from other sheets. This is the only sheet in the workbook that I will print out every day. I have it set right now to fit all columns on one page, however sometimes this will result in the bottom 1/3rd of the sheet not being used. If I use fit all rows on one page, then some of the columns will get cut off. Is there a way to scale in both directions? My guess is there is not, but I thought I would ask because it would work great for me if I could.
View 4 Replies View RelatedI have a report that I use and right now I have a command button that prints the report x2. What I would like to do is to have it print one in color and one in grayscale. I could do this I think with two different buttons, but I would like to utilize just one. I tried creating a macro but it did not pick up the fact that I changed the properties to black and white.
View 2 Replies View RelatedI am looking for a formula that will take data from one page and graph it on another page. The data I am trying to graph is arranged like so:
A B
1 q 2009.05.01
2 w 2009.05.02
3 w 2009.05.01
4 q 2009.05.01
I am looking for something that will look at column A and if the answer is "q" and then look at column B and if the answer is "2009.05.01" Excel will take that and count it in a specific cell on another page. In the case above, I want Excel to give me the answer of "2" in a designated cell.
Is it possible to Change Start Page Of Multi Page UserForm Based on 8 CommandButtons. on a WorkSheet, for example.
CommandButton1
UserForm1.MultiPage1.Value = 0
CommandButton2
UserForm1.MultiPage1.Value = 1
CommandButton3
UserForm1.MultiPage1.Value = 2
CommandButton4
UserForm1.MultiPage1.Value = 3
Could I use 1 multipage UserForm Instead of 8 UserFoms. Is it better to use 1 multipage UserForm with 8 pages or 8 UserForms. I dont know if this make sense or not?
I have a workbook that has 9 different tabs in it. Once all the information has been completed throughout the workbook, is there a code that could transfer specific data on each page to a different colum on that same page? I need to do this for 9 pages at one time. The information is listed differently on each page. I need to transfer data from the current data column to the previous data column once the entire workbook has been filed out.
View 2 Replies View Relatedhow to get rid of the page numbers that appear when using page break preview mode. The page numbers appear in large grey font in the middle of each page and sometimes makes it difficult to read cells. Can I use this view with all the same functionality without that one feature?
View 3 Replies View RelatedI need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
Working with 2000 Excel is bringing some new chalanges. I see things are done different. I am trying now to keep my header to repeat from page to page.
For example I have a header that may say something like:
People, numbers, date, ect. And there are so many new rows they go on for pages long. How can I keep my header on the top of each page when it prints out on paper.
Assume that calculation is set to automatic and not manual, then is every formula on a page updated when any value on the page is changed, or only when the values in the cells pertaining to a particular formula change?
For instance, if cells a1-a10 have values and in a11 is a sum a1-a10 formula, and in b1-b10 are values and a sum b1-b10 formula in b11, if I change a value in range b1-b10 but do not change any values in a1-a10,then as well as the formula in b11 updating will the formula in a11 recalculate or does excel keep track of which cells have changed and thus is aware that the formula in a11 does not need to be recalculated?
I have a dataset that is as follows:
Col1 2 3 4 ...... 45
123 456 xxx xxx xxx
100 234 xxx xxx xxx
221 543 xxx xxx xxx
112 234
112 689
122 956
122 234
This goes on from left to right for 45 odd columns.
I need to copy each set i.e set 1 is 123 down to 122 to say cell a25 and then copy the next set i.e. 456 to 234 below the first set.
I need to somehow write a loop code that knows where to get the 2nd, then 3rd set, etc and copy it to the bottom of the preceeding set
What I want to do, is to populate the Lois, Beth, Kelly and Shelley pages with the rows from the Que page when they get assigned to one of them. I tried to program then off of a different example in the forum. It didn't work. Will include the copy.
Tracking(HELP).xlsx
In my header I am trying to show the current page number and the total number of pages in the workbook. So if I have 10 pages in my workbook, page 5 would read "5 of 10".
I have this in my header "&[Page] of &[Pages]". All worksheets with 1 page read "1 of 1" and worksheets with 2 pages read "1 of 2" on the first page and "2 of 2" on the second page.
The code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.
For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".
I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :
Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
I need to remove all existing page breaks in a document and add a page break every 72 rows. I've tried some similar codes from this forum with other functions that I don't need in it.
View 7 Replies View RelatedI have a booklet I want to print from Excel 2000. I'm not seeing how to have the HEADER on page one only. The header is coming up on all pages. Is there a way to tell Excel to print the Header on page one only?
View 2 Replies View Relatedsee my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.
View 2 Replies View Related
