# Conditional Formula: If Text, Repeat As Number To Sum, Else Sum Numbers.

Dec 29, 2008

I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In"
IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))

How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).

## Conditional Border Formatting Formula (after Repeat 5 Data)

Apr 9, 2014

how to make conditional formatting for border line (top & bottom) after repeat 5 data/text ....

it's possible with CF formula and do not vba?

see my excel file attached..

## Conditional Formula Based On Another Cells / Rows Text / Number?

Jan 25, 2013

I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance

I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)

Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..

Is there a possible way for the formulas to "find" the correct line to start calculating data?

## Adding Rows With Control Number That Only Repeat 3 Times: Formula

Apr 1, 2009

I'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.

Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.

So I need something that can take each control number that has three cycles and add their # of 0:15 together.

The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.

Ultimately I would like the results on a separate sheet.

This is only a part of the file, there are over 1000 different control numbers.

## Conditional String: Repeat The Same Account Number In The String

Jan 10, 2009

I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?

## Conditional Formatting Text To Numbers?

Jun 26, 2013

I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levelsStatementSchool Action PlusSchool ActionMonitorIn year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.

I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula

What I'm trying to achieve
ExcelHelp.jpg

My Options sheet
Excelhelp2.jpg

## Greater Than 3 Conditional Formats For Text And Numbers

Feb 29, 2008

I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then
Select Case Target
Case "0.5", "1", "U"
icolor = 38
Case "C", "M", "P"
icolor = 40
Case "A", "S", "D"
icolor = 36
Case "L", "UP", "C/E"
icolor = 35
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).

* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.

* When the cell contains no value, the borders should return to how they were previously.

## No Repeat Numbers

Nov 26, 2009

I'm trying to pick 10 random numbers from a list of 23, with no repeat numbers. I'm using the formula "=INDEX(\$A\$1:\$A\$23,RANDBETWEEN(1,23))"
This will give me 10 random numbers with repeat numbers most of the time.

## Sorting Repeat Numbers

Apr 14, 2008

I'm trying to develop this formula for days now.
how can I not list repeated nums from a array example:

675 863 765 982 856

no repeat nums: 675, 863, 982, 856

as 765 was repeat of 675 it should not be used.
keep in mind same goes of 3-4-5... repeats,

## Conditional Formula Across Numbers Worksheet

Sep 30, 2009

I have 2 worksheet.

Worksheet 1 contains raw data.

Worksheet 2 contains 2x variables that I need to 'look up' in Worksheet 1 and tell if they are 'true' or 'false'.

I thought this would be easy, have tried lookup, and count etc and nothing is working.

## Conditional Minimum Formula In A Series Of Numbers

Jul 18, 2013

What is the syntax for the formula for the minimum number, greater than zero, in a series of numbers.

I have a row of numbers, including some zeros, but I want the minimum number excluding the zeros.

## Conditional Formatting Formula For Decrease In Numbers

Oct 16, 2013

I have a table that has labels of departments. Then each of those departments will be tracked per week on a number. I am looking for a formula for conditional formatting that if cell C5 is less than B5 that cell C5 (with the smaller number) fills green. Then depending on the next weeks number it will have no fill or a green fill if the number has decreased in cell D5.

Is there anyway that I can do an array of cells or do I have to do it individually per column?

## Generate Repeat Numbers When Compared To Previous Row?

Apr 16, 2012

How do I generate repeat numbers when compared to previous row?

E.g.

Column No A2, B1, C1, D1, E1
Row no... A1 5 12 23 50 3
Row no... A2 10 21 5 25 6

Repeat number for above is 5

Given:

1) 5 cells only. All numbers are integers, no fractions

2) In a row same number is not repeated.

3) It is possible to repeat all numbers in a new run.

## Repeat Data Based On Adjacent Numbers?

Dec 28, 2013

I would like to copy rows based on the value of another cell.

For example turn this
code qty
apple 3
banana 4
orange 2

into this
apple
apple
apple
banana
banana
banana
banana
orange
orange

## Conditional Summation Formula: Set SUM Code To Grab All Numbers Next To Codes

May 6, 2008

I am creating a billing sheet that has a column where charges are coded (to determine what they are for) and I want a "Grand Total" for everything followed by a breakdown "Total" for things with the same code. Is there a way to set SUM code to grab all numbers next to codes?? (EX. Column A is Billing Code, column B is Cost in \$\$; I want a total at the bottom that takes the different amounts in column B based on the various information/codes from column A).

## VBA Conditional Format Range Of Cells Based Upon Criteria In One Cell / Repeat For All Rows

Apr 3, 2014

Code is trying to

1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.

Recieve run time error 1004: Method 'RAnge of object_GLobal failed

===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3

[code].....

Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells

## Conditional Formatting Text Equaling To Number Formats?

Apr 20, 2013

Conditional Formating Text equaling to Number formats:

Example:
C20 is '436.59 (its pasted as text often is must remain as text) and D20 is 436.9535

I want D20 to have only 2 decimals and turn green if it equals C20. And turn red if it doesnt equal C20.

## Macro To Repeat Number Set?

Mar 9, 2014

I have many set of numbers in column A and Coulmn B each set separated by space.I need to repeat each set to n number of times.

## Conditional Format For Formula Or Number

Oct 2, 2009

I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet.

At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula.

I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done.

The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.

## Conditional Formatting Multiple Number Values But IGNORING Text?

Sep 6, 2013

I have a spread sheet where there are multiple rows of price quotes from different suppliers.

Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".

SupplierA
SupplierB
SupplierC
SupplierD

1.3
No Bid
2.3
No Bid

How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.

=G31=MAX(\$G\$31:\$J\$31)

This does not seem to work, I don't know how to exclude the text values from the MAX function.

## Show Only The Number Of A Cell Containing Text And Numbers

Nov 23, 2009

Lets say cells A1 to A5 contain these lines of information:

Till 174 (T0215) - till keeps turning itself off.
Till 245 - stuck on windows screen
116 - keyboard is unresponsive
Berkel Scale is constantly beeping
ped not reading cards Till 156

How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.

## How To Come Out With Random Number From A Field That Have Text And Numbers

Nov 7, 2013

I need excluding text from a cell and come out with a random number up to 3 numbers. Please see the attached image;

If you notice in column D there is text and a number that starts with a decimal, for example on the first row is .437 the next row is .215 next row is .63 etc... Is there a way to come out with a random number mixing it up to 3 digits and output those number on column "E"? Im only interested in filtering and coming out with the random number i mentioned .437 .215 .63 etc... not the +/-.010in.

There is no right or wrong number, i just need to come out with random number using up to 3 numbers up.

Maybe for .437 three numbers up could be .440 for the next row .215 maybe a number up .216..etc..

Hope there is a way to do this, my excel file is too long to type a number manually.

## Extracting Specific Number From Text Containing Other Numbers

Apr 27, 2007

I have a text field which contains multiple numbers ( reference #s and phone #s). I need to extract the reference # which will be either a 7 or 8 digit number. That number will not fall in any particular place in the string.

## Repeat Macro Set Number Of Times?

May 11, 2012

I'm trying to repeat a macro a set number of times depending on the number a person inputs into cell B3.

Here it is currently:
Columns("C:D").Select
Range("C4").Activate
Selection.Copy
Columns("E:E").Select
Range("E4").Activate
Selection.Insert Shift:=xlToRight
Range("C3:D3").Select

## Concatenate Text And Numbers And Then Sort On Original Number

Jun 26, 2013

I have a data set that shows numbers for categories over time. I would like to add the data label to the numeric value and then sort the data by the original number. Is this possible?

Here's an example

A 1 2 3 4
B 5 7 4 1
C 8 0 2 1
D 3 4 9 5

I would like each column sorted independently based on the data in that column, but I want the results to have the label from column 1 concatenated with the value in the other columns such as this:

A1 C0 C2 B1
D3 A2 A3 C1
B5 D4 B4 A4
C8 B7 D9 D5

Each column is sorted by the numeric value, but has the name label from column 1 attached to it.

## Converting Text To Numbers To Useable Number Format

Sep 29, 2008

Is there anyway of converting the output (which is 7 and in text form) to a useable number format so that I can take advantage of the VLOOKUP function (instead of getting a #N/A)....

## Cannot Convert Numbers Stored As Text To Number Format

Dec 14, 2009

There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message:

"The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab.

The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries.

## Repeat List Of Names X Number Of Times?

Jan 10, 2014

I'd like to be able to repeat a list of names X number of times.

Example: Repeat count = 3

Name1
Name2
Name3
Name4

becomes

Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4
Name1
Name2
Name3
Name4

## Macro To Repeat Row Specific Number Of Times?

Apr 19, 2014

I have never built or used a macro before and need very clear step by step instructions. I am just assuming that a macro is the answer to what I am looking for but if there is a simplier solution that would be fantastic.

I need to be able to repeat a row of data a specified amount of times.

Example:

3 Yellow James
2 Green Mark
5 Purple Sue
1 Orange David

Needs to look like:

3 Yellow James
3 Yellow James
3 Yellow James
2 Green Mark
2 Green Mark
5 Purple Sue
5 Purple Sue
5 Purple Sue
5 Purple Sue
5 Purple Sue
1 Orange David

## How To Repeat Number According To Previous Cell Using Macro

Jan 6, 2014

I am new to macro. I am trying to convert the following from column 1 to column 2.

1
1

0
1

3
3

[Code] .....

In the left column, the numbers who are greater than 0 can have the same number of rows of zero.

Is there anyway I can do it to convert Column 1 to column 2.