Create Rank Base On 2 Variable

Feb 3, 2008

I hv following table :-

Summary Report  FGH1how urgent How ImptRank2highHigh 3midhigh 4LowHigh 5highmid 6Midmid 7Lowmid 8Highlow 9midlow 10lowlow Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4

I need code , when run it will fill in the ranking number :-

Summary Report  FGH1how urgent How ImptRank2highHigh13midhigh24LowHigh35highmid46Midmid57Lowmid68Highlow79midlow810lowlow9Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies


ADVERTISEMENT

Return Value Base On Top X Highest Rank

Dec 18, 2013

I have done many research online to cater to my require but fail to accomplish it. I need to determine the decision base on ranking of the number in the Rank column. which is than reflected in the calender. But per day basis, it needs to only be green for the top "number of quota set per day" highest ranking. I have also attached the sample for a clearer understanding

Sample.xlsx

View 4 Replies View Related

VBA- Create A Macro That Will Change 1 Of The Base Salary

Jan 28, 2010

Lets say I have

Admin:
Base Salary
US Duties

Consultant:
Base Salary
Senior Advisor

Is there a way to create a macro that will change 1 of the Base Salary(s). I need one of them to be Base Salary - Admin, or Base Salary ' or something different for my vlookup.

Will this macro work for each different tab I have?

View 9 Replies View Related

Create A Scoreboard - VLookup Are Totally Blank When Try To Rank It?

Apr 9, 2014

I am trying to create a scoreboard or leaderboard for my job. I am using VLookup to pull information from another worksheet and that working fine. And when I get that information I am ranking it. Here is my problem... Some of the data cells that are being pulled using VLookup are blank totally blank so when I try to rank it, it say #Value!. Basically I want that blank cell to be a zero instead so that I can rank it 0 being the lowest score 3in the ranking.

View 3 Replies View Related

On Weighted Percentages - Create A Report That Rank Top Sales Agent For The Month?

Jul 29, 2014

I have been asked to create a report that rank top sales agent for the month with the following variables:

Total # of sales-35%
Total $ of sales-45%
Number of calls made-20%

Given that there is more than one variable they have placed percentage weight on each. Thus my task is to cull said percentage and add their total thus getting a figure which would represent their ranking depending on how higj or low the total is.

View 3 Replies View Related

Create New Variable According To The Formula

Jun 18, 2012

I need to create a new variable according to the formula:

Suma( Foreign_Owenrship_Plant * Number of Full Time Employees)/ Suma(All Full Time employees in the sector)!

However previosly I need to filter the firms within a sector within a country according to a three digit ISIC code. Lets say in Albania, in year 2002 in the Transport sector there are 5 groups of 3 digit ISIC codes. Once I have filtered them then I can proceed with calculating the new variable. How can I do this using programming?

View 1 Replies View Related

Create Variable Name Using Cell Value

Aug 4, 2012

Using VBA how can I make a variable name using the cell value.

Like if Range A1 value = "X1" and Range B1 = 1001

I would like to end up with Variabel (X1 = 1001)

View 1 Replies View Related

Create A Constant Variable

Apr 14, 2009

I want to basically set a varible that can be called back into a multiple of documents I will try and explain this as best as I can but it is a fortnightly pay date... i.e. 22/04/2009 Wednesday... as the payroll is fortnightly I want to be able to use the current date or todays date in a future proof macro.

i.e. if todays date is the 14/04/09 then the next pay date has to be the 22/04/09, if this was run in two weeks it would be 06/05/2009.

I was thinking a long the lines of lock in one start date... 14/01/09, then adding increments of 14 days until todays date - variable + 14 * r = "-"

View 9 Replies View Related

Create A Count Of A Variable Set Of Data

Jul 11, 2014

I am working on a set of Books to be published together. I am attempting to count the number of times a Book contains a glossary word that only appears once in the whole series. Conceptually I need to check if a word in A1 == any word in Sheet2 A:A, if so then does the value in Column B == 1, if so increase count by 1; if not proceed to A2 == Sheet2 A:A, and so on.

If this were a basic program I would do as follows:

C==2 **place count
s==size(A:A) ** check for length of column A (which I know is 179)
if C<=s
match (A'C', Sheet2 A:A)
if B'C' = 1
COUNT == COUNT + 1 ** COUNT is the number of times a book contains a word that only appears in that book.
Else

[Code]...

Here is a sample set up

Screen.jpg

View 5 Replies View Related

Variable StrArray - How To Create A String

Oct 24, 2011

I have the following code.

Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array("ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"), Operator:=xlFilterValues

The values ELDU238 - ELDU247 are values in a column beginning at A2. I want to create

"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"

in a string variable strArray which will enumerate whatever values are in column A beginning at A2 and create the above. I can then have

Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array(strArray), Operator:=xlFilterValues

Hence if the number of entries changes e.g. say I have

"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247", "ELDU248"

the code will still work.

View 4 Replies View Related

How To Create 2 Variable Data Table

Nov 25, 2013

I am trying to create a 2 variable data table. I have the variables in a different sheet from the datatable sheet, and I read here XL: Error Message: "Input Cell Reference Is Not Valid" that if I link these variables to the active sheet where the datatable is located it should work, but the table is showing the same result in all the fields.

When I put the put the variables value in the same sheet as the datatable and link to these values from the other sheet the table is working fine, so I know that my design is correct but it has to do with referencing or something else that I need to set but I am not sure what it is

View 1 Replies View Related

Create New Folders In Variable Location

Oct 17, 2006

I would like to create new empty folders from the list in column B.

The number of folders will vary depending on how many entry in column B.

I need the folders to be created in an existing folder in the "current directory" called "Shop_Drawings".

The following code may be able to be modified.

Sub CreateFolders()
Dim MyFile As String
Dim sDir As String
Dim rng As Range

Set rng = Sheets("Matdata").Range("B2")
While rng.Value <> ""
MyFile = rng.Text

sDir = "CurDirShop_Drawings" & MyFile
''above is where I am having trouble...don't know the correct syntax''
MkDir sDir


Set rng = rng.Offset(1)
Wend
End Sub

I have attached a sample workbook.

I have been getting by with code which requires changing the destination in the module whenever making folders in different diectories or drives.

View 6 Replies View Related

Create Hyperlink To Cell Based On Variable

Dec 8, 2012

VB:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Work_Log!" & Sheets("Work_Log").Cells(b, 1), TextToDisplay:="View"

I want this to link to Work_Log!.Cells(b,1), however instead of grabbing the specific cell, it is pulling the contents of the cell and creating a URL of that. For instance if b = 1 and cell A1 contains the value "yellow" then this code is giving me the hyperlink to WorkLog!Yellow when I want it to read WorkLog!A1

View 2 Replies View Related

Create A Range Variable From Cell Text

Oct 26, 2007

Let's say I have cell A1 on Sheet 1 of Book 1. I need this cell to be a range variable for a macro in Book Two, but cell A1 varies from time to time (A1 this time, A7 next time).

In Book Two, I write "A1" as the contents of cell C3, for example. How do I turn that string into a range variable for use, not the containing cell? Since the cell needs to manually changed each time I run the macro, having it as a text field somewhere on my Book 2 Sheet seems the only simple way to identify it but IS there another way? Selecting it as the active cell and using another subroutine defeats the use of the macro.

View 4 Replies View Related

Rank A List Then Re-rank The List While Excluding Certain (or By Criteria) Items

Dec 30, 2013

I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items

Vendor Co
Cost Fee
Rank

Vertox
500
4

BV
1520
3

[code].....

View 4 Replies View Related

Macro To Create Columns By Referencing A Variable Column

Jul 11, 2014

I've only done a small amount of macros with VBA.

I want to create a macro to insert 12 cells left of a column labeled "This Year" As the spreadsheet grows (by 12 columns @ year) the "This Year" column moves to the right. Thus I need to reference the range off of that column and then insert 12 columns directly to the left of it each year. Can I somehow reference the label "This Year"?

I then need to enter the month labels in the new columns row 8.

My problem is trying to reference off the "This Year" column.

View 8 Replies View Related

Create Search Facility That Will Run On Variable Functions And Display Information On Screen

Dec 23, 2013

I have an excel file with a large amount of employee data in it and want to create a search facility that will run on variable search functions and display the information on the screen

I want to be able to enter variable search functions as follows:

Employee Number; shows all information on employee
Division: shows all employees in division (possible from a list of all divisions)
Appraisal Eligibility : Applicable shows all the applicable employees
Job Role: shows all the employees with the same job role (possibly from a drop-list of all roles)

I know its easier in Access, but all records in Excel as a legacy and don't have time to create an access database currently.

View 1 Replies View Related

Addition In Base 6

Jun 14, 2009

I've got a column of numbers that represent the number of overs bowled in games of cricket. Whilst these are whole numbers (eg. 34 overs + 34 overs) the addition isn't a problem, but when they are incomplete overs (eg. 34.4 overs + 34.5 overs) then the addition if out of kilter as it sums them in base 10, and not in base 6. (As there are six balls in an over, not ten for anyone who doesn't know!)

View 2 Replies View Related

How To Sum Column Base On Few Criteria

Feb 17, 2014

I would like to sum the amount column of unique date that is in active status of each account.

Account Date Status Amount
11 jan 1 A 200
12 jan 1 A 5
13 jan 1 A 1000
11 jan 1 A 200
11 jan 3 A 100
12 jan 3 A 8
13 jan 4 A 1
13 jan 4 A 1
11 jan 6 I 50
12 jan 6 A 10

Below is the expected result
Account Amount
11 300 (200+100) [200 is only add once since jan 1 of 11 has appeared more than one time]
12 23 (5+8+10)
13 1001(1000+1)

View 5 Replies View Related

Hyperlink Base Does Not Work

Mar 7, 2012

I have entered this value into a cell on my worksheet. It is referencing a cell in another worksheet on a shared drive on my network.

='\servershare$[doc1.xls]Sheet1'!$A$1

All is fine until I re-open the document, whereby it has changed to:

='\serverusers$share$[doc1.xls]Sheet1'!$A$1

I have been advised to set the hyperlink base as \servershare$

...but this clearly does not work.

View 4 Replies View Related

Array In Data Base

Apr 21, 2006

i have used the database from j & r solutions. i have altered it slightly to suit me. the database works fine apart from when using the find all button it will only return 4 entries. if there are more than 4 entries it returns runtime error 9.i have zipped up the code and marked where the error is shown when i debug

View 2 Replies View Related

How To Change Base Unit Of Charts

Dec 11, 2013

I'm working on a charting application that uses Office charts. I'm wondering what are some of the main reasons people change the base unit from the default, and how common that is.

Some reasons I've identified on my own:You're comparing different monthly metrics, but they could be recorded on different days of the month.Get a zoomed-out view.Compare the max value of each month.

View 3 Replies View Related

Transferring Data From A Row Base On Value Given From One Sheet To Another?

Jul 17, 2013

We have database (attached here). I want to copy the name of candidates base on their Status to their designated tab. example Diana Longoria name will be copy to "Waiting" Tab and if I change the status to "Rejected" it will be copied to "Rejected" tab and remove name from "Waiting" tab.

View 12 Replies View Related

Use Access Data Base For Project?

Jan 12, 2009

I am working on a spreasheet that will automatically calculate the interest rate, loan to value advance, and other parameters from user inputs.

I have found a way to get the calculations to work correctly, but I have about 50 lenders to input - all with different rates and lending guidlines with respect to loan to value advances.

I am sure that I am going about it the hard way and I have no problem going at this to get it right.

I have attached the spreasheet I've started. I've only got one lender completed thus far. So if you need help sleeping at night, go ahead and see what I've done (yes it's boring).

Edit note: I don't know if using Access would make this an easier project to tackle but it is an option (I'll just have to learn
Access if that's the case - I've never used it).

View 3 Replies View Related

Time / Frame Fps Formula (base 75)

Jan 18, 2010

I have a formula that calculates only seconds and frames. The frame rate is 75 fps (0-74).

e.g., 49.50 is 49 seconds 50 frames. It will not parse Minutes.Seconds.Frames, e.g., 1.49.50 is 1 minute 49 seconds 50 frames.

What my existing formula does is converts the number to all frames then converts the answer back to seconds and frames.

I need this formula to include minutes in its calculation.

A copy of the spreadsheet is here: ...

View 14 Replies View Related

Formula - Calculate New Base Pay Rate?

Dec 27, 2012

I am trying to calculate a new base pay rate, but I need it make sure it is at least brought up the new minimum and capped at the new max of the range where applicable. The increase is based on 10%

So here are the columns used:

L = Current Base Pay
Q = New Min of the range
S = New Max of the range
U = where I want to calculate a 10% increase of L, but ensuring it is brought up to at least the min (Q) or not over Max (S). In other words if my min is $12 and Max $18 and my new base pay is $16 -- then I am good. However it if is $11.50 I need the formula to return at least $12. And visa versa -- if the new rate would be $18.50, I need it to return no more than $18.

View 3 Replies View Related

Delete Row Base On Cell With Specific Value?

Oct 4, 2013

I have a macro that delete's a row when a cell in that row contains a specific value. I am unable to make the macro work automaticly when the file opens.

Sub Delete_Rows()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A1:F400"), ActiveSheet.UsedRange)
For Each cell In rng

[Code].....

View 3 Replies View Related

Open Worksheet Base On Cell Value

Feb 11, 2009

I have multiple worksheets that call one userform.

Each worksheet has a specific word in cell J1 that matches a worksheet name.

How do I select a a sheet based on a cell value in J1?

Code: .....

View 9 Replies View Related

Macro: Base Row Number On Cell Value

Nov 20, 2006

I have a macro that has following line

Range("C38'").Select

I want to dynamically change the C38 to may be C37 depending on a value in a cell i.e. F1

so if the value in cell F1 is 31 then I want this statement to look like

Range("C31'").Select

View 3 Replies View Related

Base Pivot Table Off Different Ranges

Dec 7, 2006

I need to read consantly changing shift time/ covered data from a Pivot Tables pivot chart and populate this data into number of shifts covered/ uncovered. This information is then put into a chart over a 24 hour period (from 0700 to 0700). I have been populating the data from the pivot chart by hand by referencing the number of shifts in the covered line and dragging it to correspond to the shift time data part. I then have to do this for the uncovered shifts. As the data in the pivot chart is constantly changing, i need to do this data ransfer 'automatically'. I have started to look at and learn VBA, but i am getting nowhere fast. I enclose a worksheet (blank) to give you an idea fo what i am trying to do.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved