Amend MAX CF Formula To Only Show The 1st Identical Number?

Dec 2, 2013

I have a conditional format which uses this format MAX($N$84:$N$94) to colour the cell containing the number but I now wish to amend this formula so that if an identical number also appears further down in the column only the first of the cells containing the identical number(s) will be coloured but none of the others,

View 4 Replies


ADVERTISEMENT

Amend Code To Sort Sheets By Number

May 8, 2008

I tried this macro, written by Leith, to organize a workbook of over 100 worksheets, all named as a 3 digit number. (001, 002, 007, 004, 018, 12, etc.)

For some reason, there was no order to the sorting. Can the macro above be modified to sort my sheets in numeric order?

View 9 Replies View Related

Amend Ranking Formula For Sports Results

Oct 15, 2009

I am attempting to upgrade a scoring system in excel 2003 which I use to rank wins in a sports results grid and i have to amend a formula based on a new version. The current formula in the attached part-completed worksheet Draw9 of 1 to 9, which I wish to amend, is in cells AM42:53 one of which is:

=IF(AL44="","",RANK(AL44,AL44:AL53)&CHOOSE(AND(RANK(AL44,AL44:AL53)<>{11,12,13})*MIN(4,MOD(RANK(AL44 ,AL44:AL53),10))+1,"th","st","nd","rd","th"))

I have now added an extra column of data in cell AN44:53 under “LSD” and I would like to use this to improve the accuracy of the original ranking. In other words I wish to Rank teams using the “Wins” in column AL42:53 plus the “LSD” data in columns AN44:53 and this will ensure that where teams are tied on the same number of wins then the numbers in the “LSD” column will enable a clear ranking i.e. say 3 teams on 3 wins ranked 6th = will now be ranked 6th,7th & 8th.

View 4 Replies View Related

Amend Formula So Its Counts Both Text And Numbers?

Apr 23, 2014

I have the following SUMPROCUCT in place and it works fine for current purpose:

=SUMPRODUCT(VLOOKUP($E$9,Rota!$C$4:$FU$369,{5,30,53,78,103,137},FALSE))

columns 5, 30, 53, 78, 103, 137 all contain text. However I need to amend some of these to text but I still need the SUMPRODUCT to add up.

Is there a way to amend the above formula so its counts both text and numbers in 5,30,53,78,103,137 ?

View 2 Replies View Related

Automatically Count And Amend A Formula As New Data Is Added

Jun 7, 2006

[please refer to attached sample data]

The formula in K3 is

"=J15/10"

This formula calculates the average mailing return rate of all the mailings added together. J15 is currently white text to hide it from the end user.

Everytime I add a new row of data I need the formula to update itself automatically. So for example, the next time I add a row of data the above formula has to change to

"=J15/11"

Or if I were to add three rows of data the formula would have been

"=J15/13"

View 9 Replies View Related

Left & Right Formula To Show Number Not/as Well

May 22, 2007

I have numbers in a cell such as 1/25, 4/31, 12/35 etc I have the current formula to extract the left or right number but when there is only a single digit it also includes the / is there a way of eliminating this.

The numbers vary from 1 to 2 digits.

My current formula is =IF(C15<18,LEFT(E8,2))+IF(C15>18,RIGHT(E8,2))

I only want it to show the number not the forward slash

View 9 Replies View Related

Formula Or Macro To Show All Number Combinations?

Nov 19, 2007

as an example i will use the national lottery. numbers 1 to 49 inclusive. i need a formula that will list all the possibile 6 number combinations not repeating any.

View 6 Replies View Related

Date Range (formula Show A Week Number In The 2nd Table )

May 6, 2009

I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates.

Example:
1st Table
FROM TO WK
3/27/2009 4/2/200914
4/3/2009 4/9/200915
4/10/2009 4/16/200916
4/17/2009 4/23/200917
4/24/2009 4/30/200918

2nd Table
DATE
03/28/2009
04/11/2009
04/26/2009

Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.

View 5 Replies View Related

Formula To Show Project Size Based On Number Of Hours

Jul 8, 2009

I need to show the size of a project in a cell based on it's number of hours, e.g.

Between 5-80 hours = SMALL
Between 80-200 hours = MEDIUM
Over 200 hours = LARGE

How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?

View 3 Replies View Related

Make Column Show Result Of Formula As Positive Number Only?

Dec 14, 2013

I am trying to make a column show the result of a formula as positive number only. Right now the column subtracts two other cells and displays the result. I am trying to make it show all the results, positive or negative as positive.

View 3 Replies View Related

Excel 2010 :: Highlight Groups Of Identical Number Cells In Alternating Grey And White?

Jun 27, 2014

I am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.

1

1

2

3

4

[code].....

View 4 Replies View Related

Formula For Counting Identical Values In Cells

May 3, 2007

I would like a formula which will look at a range of cells, for instance a2:g2 and count how many of the cells contents are the same. The cells contain a combination of text and numbers.

View 9 Replies View Related

3-D Formula Syntax: Consolidate Certains Cells In 30 Identical Worksheets In A Workbook

Jun 7, 2006

I want to consolidate certains cells in 30 identical worksheets in a workbook. I understood that if I use the 3-D formula method, that I could insert new identical worksheets into the range of worksheets and the consolidated totals would update. Is that correct?

I cannot seem to get the syntax to work. When I read the Excel Help file it gives an example of =sum(sheet2:sheet7!B3) but I don't understand how Excel could answer this ( if the worksheets were named Sheet2,sheet3,sheet4 etc up to sheet7)
In my example, the formula is clearly wrong. Why does it not use the single quotation marks '? I cannot find references to the proper syntax anywhere. I have your training program but this does not spell it out for me either. If I use Data>Consolidate then I have to labouriously do this for each cell that I want to consolidate.

View 2 Replies View Related

Show Text & Number With Number Format

Aug 9, 2007

I have a standard block of text with numbers in it pulled from various calculations in a financial model. I have done this through a formula

e.g. ="You gross profit percentage is " & D9 & "% and your gross profit is $" & D10 & "." Problem is i'd like to format the numbers that pull through so they are easier to read. At the moment in the above example D10 results in $-600000000. I'd like it to look like $(600,000,000).

View 4 Replies View Related

Amend Match For Min & > 0

Jun 8, 2009

I need to amend this formula so match only cells with min values > 0.

View 3 Replies View Related

Amend Code To Run Faster

Sep 5, 2008

I have the following code kindly supplied to me by someone else and I'd like to know if anyone can suggest any ways to speed up the running of it. It currently takes about 4 minutes to run (there are about 5000 cells to loop through)

Basically it looks for a value in Col C - works out the number of rows to fill up by searching a range in Col D - then copies value in Col C up by that number of rows.

View 10 Replies View Related

Amend Macro To Run Through All Sheets Except Two

Feb 5, 2009

I have found and adapted a macro (below) that searches all worksheets for a specific value (entered by the user in a specfic cell) then displays a list of hyperlinks to the matching cell locations.

I now have a couple of sheets I don't want to be included in this macro ("Master List" and "Pivot Table") but I'm really struggling to find a way of amending the macro to exclude these. I've searched and tried a couple of ways but I get errors and I think I'm not knowledgable enough to know where to put each bit of code.

View 3 Replies View Related

Amend To A Database Daily

Dec 31, 2009

I have a daily log for work that keeps track of purchases and returns among other items and I was wondering if there was a way I could have all this information get put into a log that will amend everything for each week, month and year.

View 14 Replies View Related

Amend Csv File From List

Jun 7, 2009

I have a csv file (Import.csv) containing rows similar to the following:

001032,117.5,19/09/2005,Ref1,Ref2-1,100,44100,#Sales#,,Import routine
001033,129.25,19/09/2005,Ref2,Ref2-2,110,44100,#Admin#,,Import routine
I also have a worksheet with the following list (which will grow):

Cost Centre Codes

AB1DepartmentCode2AdminADM3SalesSAL4StoresSTO5PurchasingPUR

Excel tables to the web >> Excel Jeanie HTML 4

The deprtment codes in the csv file are all contained within '#' markings.

I need a macro that will find and replace all department codes in the csv with the relevant Code from the list, and remove the '#'s. Unfortunately, some of the fields are numeric with leading zero's so I dont want to lose these by opening and re-saving the file in Excel as a csv.

The output for the above is as follows:.........................

View 9 Replies View Related

Retrieve And Amend Records From Database

Apr 11, 2008

I am a building a database where each row represents a record. Each record has a unique identifier number.

In worksheet "Record Search and Amend", I have recorded a macro to look up the data.

The problem is once the record is retrieved I need to be able to amend the record and save it back into the database in the right row. This could be another button to initiate this action.

View 14 Replies View Related

Trying To Amend Existing SUM To Make It More Selective

Oct 27, 2009

This is a formula in an existing spreadsheet that I need to amend.

{=SUM(IF(WeekNo=2,IF(Workgroup="REASSIGN - CAP",1,0)))}

‘WeekNo2’ and ‘Workgroup’ are names defined and are offsets that define two separate columns.

I have created a new name ‘realclosetime’ and it is an offset to another column of data.

I have tried to change the existing formula so that it is not actioned if the ‘realclosetime’ is empty. It’s a date and time field and if it is not completed I don’t want to be including that row in the SUM calculation otherwise I end up with N/A’s.

View 10 Replies View Related

Amend The Font Colour Of The Any Captions On The CommandBar

Feb 23, 2010

I have create a Command Bar, my problem I'm trying to understand is this
1.Can you amend the font colour of the any Captions on the CommandBar?
2.How can I get the Caption to pick up from a range and use the range name as the Caption?

I have named a range in a worksheet that I want to pick up and use as the range. Below is my CommandBar code

View 2 Replies View Related

Userform Data Amend/Modify Not Working

Feb 24, 2010

I am trying to develop some data package for my office and created Userforms to add/input data, and amend/modify data. To input its working fine. But when I want to amend/modify the records its not working. I am new to Userforms, Codes. Can anyone help me by rectify me coding, for which I am attaching the sample sheet.

View 13 Replies View Related

Amend Macro To Select All But Last Column Of Data

May 12, 2014

Macro below - it now works and selects all the data in spreadsheet and converts to Proper Case

However I also need a macro to select all except the last column of data and convert all except the last column to Proper Case

It is for Post / Zip code info. so wish to keep it in Upper Case.

What I have already is:

Code:

Sub Propercase()
Dim LastRow As Long, LastColumn As Long
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Dim r As Range, c As Range
Set r = Range("A1").Resize(LastRow, LastColumn)

[Code] .....

I was hoping I could easily amend the above to select all except last column.

View 3 Replies View Related

Amend Current Macro To Protect All Sheets...

Mar 4, 2007

I have a Macro to protect all the Months sheets in my Time sheet to stop users deleting formulas, This year I've added a new sheet with a yearly calender so users can track Hols etc but when I run the Macro it protects this sheet "2007-2008". How can I amend the Macro so it protect all sheets except "2007-2008". My Macro is below:

Sub ProtectAll()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="KITCHENsink19591968", DrawingObjects:=True, Contents:=True, Scenarios:=False
Next ws
ActiveWorkbook.Protect Password:="KITCHENsink19591968", Structure:=True, Windows:=False
Application.ScreenUpdating = True
End Sub

View 4 Replies View Related

Amend Chart Data In Multiple Workbooks

Sep 5, 2007

In my VB6 project I need to loop through several Excel Workbooks with the same chart format and amend the chart data as required from time to time. There are 2 charts in Sheet2 of every workbook. With the following coding, I can only update the chart in the first workbook but not the one in the second workbook and the rest. It appears that there will be no further updates after the first workbook is closed.

Option Explicit
Dim oEquityWB As Excel.Workbook
Dim oEquityWS As Excel.Worksheet

Public Sub AmendChart()
Dim arrWorkbooks() as String'Path & Filename
Dim nn as Integer

For nn = 0 To UBound(arrWorkbooks)
If Not EquityWBOpen(arrWorkbooks(nn)) Then GoTo Next_nn
Set oEquityWS = oEquityWB.Worksheets("Sheet2")
oEquityWS.ChartObjects("Chart 1").Activate................

View 9 Replies View Related

Find & Amend/Edit Data Via Userform

Mar 30, 2008

A.) As a user is entering data into a userform two specific values need to be checked in the existing data sheet.

Textbox1 ( date)
Combobox (operation name) {4 tabs on from textbox1)

They relate to the data stored in columns 'A' and 'E' respectivly.

What I'd like is, as the user exits combobox4 to check if the values already exist. If they do; fill the coresponding textboxes with those values and allow the user to make any amendments, then have it SAVE to the SAME row, would a record number be necessary to accomplish this?

B.) For a future development, Im thinking of applying the same principle to a different project, where 3 values need checking.

Textbox9000 (date) Column 'A'
ComboBox1002 (Staff member) Column 'B'
Combobox1003 (operation name) Column 'C'

Possible complications are that below combobox1003 there will be 12 other comboboxes(a value title, from column header) with associated textboxes alongside(value previously entered by user).

There will be 22 possible values for the comboboxes(the values will be the same column headers) The user will only ever have the choice of making 12 entries though.

Does any one have any spare code laying round for this one..

View 9 Replies View Related

Macro Code To Amend The Status Of Cell To Be Date?

Mar 31, 2014

I have a user form we are using to transfer data into an excel spread sheet. When transferring the date from a text field it is formatted as general, I need this to be a date format DDMMYYY. The cell needing formatting is N2

View 5 Replies View Related

Excel 2003 :: How To Amend Existing Code In Workbook

Feb 3, 2013

How to amend existing code in a workbook that is used by many different users. I am working in Excel 2003. The existing code copies data from certain cells in the active worksheet (which is password protected) into a new worksheet and saves that new worksheet to a temp folder, sends it to a particular email address using outlook and closes it, returning the user to the first workbook. You will see that it also unhides then rehides columns of the first worksheet during this process so that the user never has to see them. Problem here is that none of the users are using outlook anymore and I would prefer that this new spreadsheet that is created just be saved to the users desktop. I have found bits and pieces of what I needed on the web, but I can't seem to put it all together (I usually end up being sent to the debugger just short of having the newly created spreadsheet saved to desktop). Here is the existing code:

Private Sub CommandButton1_Click()
Range("A2:G2").Select
Selection.ClearContents[code]....

Essentially what I need to do is circumvent the whole email bit and save the spreadsheet that is created to the users desktop instead of a temp folder.

View 2 Replies View Related

Amend VBA Code To Change Mandatory Inputbox To Userform Combobox Entry

Apr 25, 2013

I have 4 columns, If column B (Ref No) is filled in, the adjacent columns, C,D and E become mandatory and an input box pops up on screen one after the other for entry into each column.

My problem is that I require a drop down list and not a input box for the final column (status). So i have created a userform with a combobox dropdown. I am having problems connecting the combobox selection to the final column, and for the selected combobox item to go into the right cell like the input box entry currently does.

Attachment 53209test743.xls

I have attached a sheet and also you can see the code below.

Code in Sheet

VB:
Public SaveVal1
Public SaveVal2
Public SaveVal3

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

[Code] .....

View 3 Replies View Related







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