I am trying to split the points given to our anglers. here is my formula for assigning the points (column I) based on the place (columnH):
=VLOOKUP(H5,'Supporting columns'!A2:B33,2,FALSE). I tried adding +IF(H5=H5:H33,-1) but this affected all other rankings. if 2 anglers tie for a place, the points for that place are awarded -1.
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit
Sub PointName()
Dim Ws As Worksheet
Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet
Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy
Sheets.Add
With ActiveSheet
.Paste
.Name = Trim(Cel)
.Range("A1").Select
End With
Next
Ws.Activate
End Sub
I turned it off for a while and when I turned it back on I am getting an error
Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
View 9 Replies View RelatedI'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################
Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################
I have the following code, which works perfectly:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TotalDays As Integer
TotalDays = Range("C65536").End(xlUp).Row + 1
The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.
However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.
How do I add the following, to my previous code?
Range("E8").Value = Cells(7, 6) * 2.5
How do I split numbers!
I have two problems/challenges!
Part I...
I got the answer 1987, and now I want Excel to take the numbers out and display...
1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.
How do I split 1987 and put the numbers in different boxes?
Part II...
I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.
So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)
(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)
So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac
String Value
aa 1
ab 1
ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
Looking for the syntax that will allow me to code an event sub routine, based off the event of a specific function e.g. findnum being run.
View 14 Replies View RelatedI want to pay salespeople a bonus when they sell things at a higher Gross Profit. There is a minimum markup. I want to say for every additional point above the floor (F) well pay (B*C). There is also the actual attainment filed (A). For this example lets say the floor (F) is 22% and the actual attainment is 26%.
Illustrations:
.22 = 0(B*C)
.23 = 1(B*C)
.24 = 2(B*C)
A = .26
F= .22
What I want is:
if A >= F THEN for EACH POINT above the floor add 1*(B*C)
Its actually buried in a larger IF(AND function and I need to be able to drag the function down a long column and have it scale with volume sold. But its just this bit of logic I'm hung up on.
Does anyone know if Mappoint 2006 has automation support? I know that 2004 does. I am wanting to use the automation from excel's VBA.
View 6 Replies View RelatedPicture 2 rows and 4 columns.
The top row is just 0, 1, 2, 3
The bottom row is something like: 850, 790, 200, 250
Here's the problem: I want to find the midpoint of the data set in row 2, based on the values in row 1. So, the sum of the data in row 2 in this example is 2090, meaning the midpoint is 1045.
If we were traversing along the top row as a line, we'd find that midpoint somewhere in the 0.3 range... but how do we get that value?
is there any way to use the right function to return any data to the right of the decimal point?
eg 117.22 would be .22
108.1225 would be .1225
102.358 would be .358 etc
1.jpg2.jpg
I am trying to create a database for part stock but with only one entry point that is in image 1 you would select the machine and part from drop down lists and hit add. It would then lookup the corresponding machine and part and subtract 1 from the current stock database.
I’m using this code to run consecutive programs, which works really well, apart from one little glitch.
View 3 Replies View RelatedSee here:
http://www6.uniovi.es/~antonio/uned/...-754hex32.html
This site allows me to enter a 32-bit Hexadecimal Representation and get Decimal Floating-Point result.
For instance, 4224A012 gives me 41.2.
Is there a UDF available to do this inside of Excel?
Is there a way to delete all rows down a sheet until Cell A contains ""
or if the criteria "" would work?
i only want to keep the data below that point.
i will have to do it 11 times for different files
If I am trying to calculate the breakeven moment from a project that has an initial cost and a series of quarterly returns. This is easy to do visually but I cannot figure out how to arrive at the breakeven point from a data set such as this.
e.g. 50,000 spent at time 0
5,000 received in q1
10,000 received in q2
10,000 received in q3
10,000 received in q4
10,000 received in q5
10,000 received in q6
In which month will I break even?
I'd like to point a cell to a subtotal on another sheet but that sheet will periodically have the subtotals removed, more data added, then sorted and subtotalled again. Is there a way to have my other cell find the subtotal that matches a certain criterion (in this case, cost center)?
View 9 Replies View Relatedthe rate and inventory management system my hotel uses rounds up to the next .95 currently. Most rates are based upon a 'Rack Rate' in the form of a discount percentage. So, if the Rack Rate is $129.95 and the AAA discount is 20%, the resulting $103.96 rate is rounded up to $104.95. I'm trying to fix my spreadsheet to account for the various rates and discount percentages of several different types of discounts.
I've tried a couple rounding methods:
=ROUNDUP(B$30*(1-B32),0)+0.95 which returns the correct value only when the value to the right of the decimal point is .96-.99
=ROUNDDOWN(B$30*(1-B32),0)+0.95 which returns the correct value only when the value to the right of the decimal point is .00-.95
=CEILING((B$30*(1-B32)),0.95) which returns some odd bits, sich as .85 and .50
So, is there a way to get Excel to shift the rounding base point from zero to .95? Or some other way to accomplish this?
Would like to do the following for our school,
In cell A1 there is a number, for instance: 12.12345
Based on that number, the 3rd number after the decimal is important.
If the 3rd number after the decimal is “x” then the contents returned in b2 is a text value ( like high school ) .........
I have a column of data and I want to average the figures from a certain point onwards.
I have the starting point value in a cell which is used as the lookup value to find the matching position in the list using the Match(lookup value, array) formula.
How do I then use that formula as the row number in a cell reference for the averageing formula?
eg Average(C(match(B1,C4:C100)):C100) - what is the correct syntax?
I am trying to position a userform to my own coordinates, but I don't see how to do this in any of the form properties. I do realize that there is a startUpPosition property but none of these positions work for me.
View 4 Replies View RelatedIf I check the box labeled catagory name on the intial creation or within chart options on the right click menu each data point gets labeled with the name of the catagory.
I would like to label a significant event at one of these data points does anyone know how to do this? If this isn't a built in feature does anyone have a work around that would look good?
During finalization I have to convert the numbers into millions. But due to rounding factor, the total doesnot match with the displayed fig. how can I sum only upto two digits of the fig? A sample file is attached.
View 9 Replies View RelatedGoing off this excellent thread : [URL] ....
I'm looking to replace the words in
VB : myWords = Array("dog", "cat", "hamster")
with a named range.
Here is what I did. I added the lines:
VB:
Dim myArray As Variant
myArray = Range("colorbold")
And replaced mywords with
VB:
myWords = Array("colorbold")
Something is clearly wrong.
I want to know if it's possible to calculate the probability that a certain range of values, reach to a certain point.....
The range is this (example):
4
3
9
7
14
22
15
20
42
46.....
What I want to know is if this tendency will reach to 1000. Or how many steps more will be need to reach 1000.
Like I said, and don't know if this is in the right place, but if you put this values in a graphic you will see that the tendency is growing. I assume that this tendency will reach to 1000. But will be in the next 10 steps or in the next 30.
I am supposed to make an xy scatter graph like the one drawn of paper. The average point need to have a "cross" around it. So far I can just plot the points. I have added the average point as secondary series. I just need to put the "+ sign or cross around" it.
View 1 Replies View RelatedI need to add a decimal point to a column of numbers. For example, where it says 126 needs to be changed to 1.26, 3035 changed to 30.35, 13593 to 135.93 and so on. Can this be done automatically or with a formula?
View 2 Replies View RelatedHow can i change this code to point to a file which is on my desktop which is in a folder called "cell n1" and the file is called "Cell n1"
View 2 Replies View RelatedI have a set of % score values (e.g. 88%, 94%, 82%, 67%)
I would like to know if there is a relatively simple formula that can be used to calculate the AVERAGE DEVIATION from 100%, as opposed to the AVERAGE DEVIATION from the mean, which is what the excel function =AVEDEV will return.
E.g. The Average Deviation from the mean for the the above set of values is 8.25%, whereas the Average Deviation from 100% is 17.25%
I can create a complex manual formula which sums absolute values of the variation from 100% of each score, and then divides by the count total of the number of scores, however this is flawed for several reasons, including the fact it is very time consuming when used across a large set of scores, and it is corrupted when a non score exists in a cell (some of the 'scores' may be '-', in which case they should be ignored and not included in the calculation of the average deviation from 100% by treating as a 0% score (and a deviation of 100% from 100%).