Excel 2010 :: If Statement With Text As Range Of Values And Numbers Needed As Output?

Apr 23, 2013

Excel 2010, I have the following list (showing part of it):

T-Shirt Sizes


The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.

Thus, I've been trying to use the IF statement with the following logic but to as no avail

IF Size in Column is XS
Add 1 to a particular Cell
IF Size in Colum is S
Add 1 to a particular Different Cell

and so on for the other sizes...kind of like a counter for a for-do-loop.

View 2 Replies


Excel 2010 :: Compare 2 Columns In Different Sheets - Unique Values Output To 3rd Sheet

Feb 4, 2013

I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.



Excel 2010

View 9 Replies View Related

Excel 2010 :: Formatting Numbers Written In Text To Numeric And Pulling Only Numbers Out

Feb 20, 2013

I am extracting data from a website to excel 2010. my problem is the data contains both text, numbers, and sometimes a combination of both.

e.g. hi im ron for more information you can reach me at 6 five 6 four 5 seven 7 three 2 two..

I need it to look like this 6564577322 or 7 * 0 * 2 * 4 * 5 * 6 * 8 * 6 * 2 * 1...i need to look look the same

How do i first format the numbers written in text into numbers and then show only the numbers in a cell minus the text?

View 3 Replies View Related

Excel 2007 :: Userforms - Input Cell Value (via Text Box) And Output Corresponding Values From Resulting Row

Jun 24, 2013

I have a simple spreadsheet of an inventory. Each row on this spreadsheet represents the data related to an item. I would like for a user to be able to INPUT a serial number via a userform. Then, I would like for the program to OUTPUT some information about that particular item (a few cells that should be on the same row as the serial number).

I attached an image diagram that may better represent what I am trying to do.

View 9 Replies View Related

Excel 2010 :: Breaking Out Numbers Out Of Text?

Jan 13, 2013

I have this text at J22 and i would like to break out 13 and 95 000 out of this:

<td><span>13</span></td><td class=""right_align""><span>0-0-2</span></td><td class=""right_align""><span>95 000

The numbers and lenght of the numbers where 13 and 95 000 are may vary in lenght. I need them to parse the values at those spots to separate cells.

Im using excel 2010

View 12 Replies View Related

Excel 2010 :: How To Calculate Different Factors Values Which Are Numbers

Apr 21, 2014

I am using Excel 2010 and I am trying to calculate 4 different factors' (columns) values which are numbers. Based on my analysis I will assign the value from 1-5 for each factor and at the end I have to decide the priority (a new column) adding all 4 columns' numbers. My Priority column has three criteria (High, Medium, and Low) but I don't know what formula/function to use and how. I have tried IF function but did not work.

View 9 Replies View Related

Excel 2010 :: Formula That Will Work With Text And Numbers?

Dec 10, 2013

I am working in Excel 2010 (Windows 8) and am having real problems trying to find a function/formula that will work with both numbers and text. I am trying to identify if the contents of Col A cells and Col C cells on each row are duplicated within a worksheet, and if they are, to add a 'J'.

(Column A is a list of numerical company codes and Column C is a list of adviser codes using the letter A-G. If a company ID code has more than one of the same adviser codes referenced to it, it is termed 'Joint', hence the 'J').

The formula I've used is: =IF(A2:C2, FREQUENCY>1, "J")

But it tells me 'numbers only'.

View 4 Replies View Related

Excel 2010 :: Moving Numbers From String Of Text To New Cells

Feb 8, 2013

I need to remove numbers from a string of text and put them into new cells in Excel Starter 2010. There are two different values which need moving. The first is in parentheses and the second follows the parentheses.

Here's a few examples of what I mean:

Original text
RED008 - Wickaman and Hoodlum bandwidth (0.06Gb @ 2.00) 0.12
MFR005EP - Various Artists bandwidth (0.19Gb @ 2.00) 0.38
RAZORS010 Future Cut bandwidth (0.01Gb @ 2.00) 0.01

would like to split into...

Cell 1
RED008 - Wickaman and Hoodlum bandwidth
MFR005EP - Various Artists bandwidth
RAZORS010 Future Cut bandwidth

Cell 2
(0.06Gb @ 2.00)
(0.19Gb @ 2.00)
(0.01Gb @ 2.00)

Cell 3

View 4 Replies View Related

Collect Date & Output To Cell In Needed Format

May 2, 2008

I have created a standard input box for the date (UK)

datereceived = InputBox("Please enter the DATE the text message was received, please enter in format DD/MM/YY", , Date)
Sheet1. Range("e" & x).Value = datereceived

I have formatted the cells for row 'E' for 'Date' and DD/MM/YYYY in English UK, But when I have inputted the date into the input box ( e.g. 01/05/2008 ), it inputs into the spreadsheet 05/01/2008 (American Obviously),

View 5 Replies View Related

Excel 2010 :: MACRO - Copy All Worksheets Into Workbook Output

Aug 26, 2013

I need to copy all sheets from a workbook to a another workbook (create a copy of the one I am using) and this is the cod I have:

Sub NewReport()
Dim Workbook1 As Workbook
Dim Workbook2 As Workbook
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With


The problem I have is that the output only have the sheet1!

I am using Excel 2010

View 4 Replies View Related

Excel 2010 :: Calculate Most Common Text Values By Groups

Mar 14, 2013

I want to calculate the most common text value for groups. I have a table with a lot of records like an example below.

ID Code
1 HB
1 KU
1 KS
1 LV
1 KS
2 LM
2 KS


I want a table like below:

ID Code
1 KU
2 KS
3 KS

How can I do it in Excel 2010?

View 9 Replies View Related

Excel 2010 :: Building Text File Based On Values Of Cell?

Feb 26, 2014

I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.

View 1 Replies View Related

Excel 2010 :: Sumproduct With Multiple Criteria And Ignoring Text Values

Jun 19, 2014

Using Excel 2010, I am trying to do a Sumproduct formula with two criteria, one of which needs to ignore text values.

Here is the set up:

Column AColumn BColumn C

I'm trying to find the sumproduct of Qty * Price if the side equals "Buy" (or "Sell") but ignoring the "null" value in column C. The formula I have is =SUMPRODUCT(--($A$2:$A$20="Buy")*IF(ISNUMBER($C$2:$C$20),--($B$2:$B$20*$C$2:$C$20)))

The result in the cell is 0, but if I open the Insert Function dialog box, I see the correct value being returned.

View 5 Replies View Related

Excel 2010 :: Procedure To Write Values To Range Using Array

May 23, 2012

Im using excel 2010 As it's 60 times quicker I was trying to speed up my code and replace all loops by putting the value into an array, and then transfer the array to the worksheet

It seems to be straightforward for math calculations like in this example:


But no luck with the one below. I was trying to test it on a simple loop which replaces two types of string into the 3rd one:


Dim lastrow, lastrow2, i As Long
With Worksheets("KPI5")
lastrow2 = .Range("N" & Rows.Count).End(xlUp).Row
.Range("T7:T" & lastrow2).Value = .Range("F7:F" & lastrow2).Value
For i = 8 To lastrow2
If .Range("T" & i).Value = "Modification" Then


View 4 Replies View Related

Excel 2010 :: Load Values From Named Range Into Array?

Jan 15, 2014

For Each clsName In Array("Africa", "Europe", "Asia")

If I have the above array values in the named range _lstRgns in Sheet 1 of my workbook, how would I write the code to pick this up?

The named range is expandable, as it is an offset formula in the name manager.

clsName is the name of my defined array in my code

View 1 Replies View Related

Excel 2010 :: Use One Set Statement To Set Second Statement?

Feb 14, 2012

I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.

My purpose is trying to look up


from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if


matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.

Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.


Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False


Windows 7 with Excel 2010

View 9 Replies View Related

Excel 2010 :: Expand Text Number Range Into Separate Cells

Feb 20, 2014

Using Excel 2010.

I have data in excel which looks like this:

Column 1 has 1200-1209,1300-1350,1523-1563
Column 2 has 1400-1409,1600-1650,1823-1863

I would like to take the range of e.g. 1200-1209 and have excel put 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 into separate adjacent cells for me. And be able to do this for each column/cell of data I have like this.

Column 1 1200
Column 2 1201
Column 3 1202

Like that only. Is it possible?How?

View 4 Replies View Related

Excel 2010 :: Condensing Dim Statement

May 7, 2013

Windows 8, Excel 2010,

What is a condensed way of saying;

Dim x As Integer
Dim y As Integer
Dim z1 As Integer
Dim z2 As Integer
Dim z3 As Integer
Dim z4 As Integer
Dim AA As Integer

View 9 Replies View Related

Excel 2010 :: ElseIf And Or Statement In VBA

Jun 27, 2013

I have tried to shorten my code by combining common tasks, however it runs but selects the wrong data. In the example below,

The code returns the expected if I select "M135X" and "300 hrs", but unexpected results if "M135X" and "1500 hrs" is selected

My variables are models and hours - the code below is for one model.

'Next Model
ElseIf Range("D3") = "M135X" And Range("E3") = "1st Service" Then
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

[Code] .........

View 5 Replies View Related

Output Used Range To Text File

Oct 9, 2007

I would like to output the text I perpared in " sheet 1" into a text file, "output.txt"

The imformation I like to output start from coulmn C, and column D then column E.....
The numbers of rows in each column could be different, and also there may be some single empty column in between. The totals numbers of coulmn is unknow.

the text in the output.txt should contant all the imformation in the sheet started from column C, coulmn D, column F.... (if coulmn E is empty). and the text in the output.txt file is continues with no gap in it.

To make it even more powerful for later use, The column that begin with NOT should not be output.

Excel Data-


View 4 Replies View Related

Text Output Based On Combination Of Cell Values?

Mar 9, 2014

I'm tying myself in knots when trying to combine logical formulas to return a text value based on multiple cell values.

Cell A1 can equal 1-10, and cell B1 can also contain the values 1-10.

I want C1 to display the text "YC" if B1 value is equal to 9 or 10. I want C1 to display the text "SC" if B1 value is 5-8, and A1 value is 4-10. Any other combination of values should return a blank cell.

I tried splitting this into two components before trying to make it fit into a single equation, and i can achieve the first condition of display "YC" =IF(AND(A1>=9,A1

View 3 Replies View Related

Excel 2010 :: Change Color On If And Then Statement

Nov 20, 2013

I tried Conditional format and no luck. (Office 2010)

What I'm trying to do VB is:

If Cell B:B has value of "402" or "up"
and Cell C:C is less >100
Then Change color on Cell C:C to "yello, red, etc" (if C value is <100 no color change)

View 6 Replies View Related

Excel 2010 :: Runtime Error When Using Like In SQL Statement

Feb 5, 2014

Excel 2010. Windows 7

Run-time error '-2147467259 (80004005)':
An unexpected token ""20*"" was found following "1' AND
PERIOD = LIKE*. Expected tokens may include: "". SQLSTATE=42601
code died at RS.Open SQL, CN
Dim sn as String, osi as string, saposi As String


View 2 Replies View Related

Excel 2010 :: Determine Row To Calculate Based On Statement

Jul 5, 2014

I need some different columns within a row to calculate based on a statement found in an earlier column in the row. something similar to below. there will only be two statements total which are noted on the table below.




So basically If column A = disposition calculate D and E in column B but if Column A = Receipt calculate in column C

The formula for D and E is not exactly as shown just using that as a quick example. I'm using office 2010.

View 1 Replies View Related

Excel 2010 :: Change Several Cells If Statement Is True?

Oct 11, 2013

I am using MS Excel 2010 want what to change a number of cells if a value is true e.g.

Row one has the headings Prz, Stat, Val, andPts
Row two has the following values :-
(Prz) a2 =2, (Stat) b2=1, (Val) c2=$30, (Pts) d2=10
How do I do the following in Excel :-
IF B2=1 THEN A2+1 AND C2+30 AND D2+5
so that the above cells are changed to
(Prz) a2 =3, (Val) c2=$30, (Pts) d2=10

View 4 Replies View Related

Excel 2010 :: Execute Statement ONLY When Macro Run From Button

Aug 27, 2012

1) I have a VBA macro MyMacro() which works fine.

The macro is assigned to a button on the w/s, and also runs from w/b Open() event and other locations within the w/b.

2) The macro displays a message just before exiting whenever it is called.

3) I’d like the MsgBox statement in the macro to be executed ONLY when the macro is run from the button.

In other words, skip the MsgBox statement when the macro is run from anywhere else in the VBAProject.

Can this be done ? possibly by inserting a statement or two before MsgBox in the MyMacro() code ?

View 4 Replies View Related

Excel 2010 :: Hyperlink With IF Statement - Cannot Open Specified File

Aug 30, 2012

I Have a CheckBox Thats Linked with CA22

In Cell CB22 I have the Following Formula:


In Cell CC22 I Have...

=IF(CB22="1","RM-4E ADD1",HYPERLINK('RM-4E (Add - 1) (2)'!A1,"RM-4E ADD1"))

The Sheet namne its supposed to Hyperlink to is called

RM-4E (Add - 1) (2)

When the Check Box is Unchecked it Changes CA22 to False and Puts in CC22 Text: RM-4E ADD1

When the Check Box is Checked it CHanges CA22 to TRUE and is supposed to create a Hyperlink to that sheet...Only when i click it it comes back with

'Cannot open the specified file."

I even went as far as typing out the Word Hyperlink( clicked the sheet and selected Cell A1 on that sheet but it still comes back with the error!

Excel 2010

View 9 Replies View Related

Excel 2010 :: Count Distinct Text Values In One Column If Another Column Equals A Value

Mar 5, 2014

I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.

For example:
A, B

The results shoud be: (2) Because I only want to count the P25 one time.

I'm using Excel 2010

View 2 Replies View Related

If Statement For A Range Of Numbers

Aug 28, 2009

I have tried over and over to get a point value from the percentages posted below but my formula will not return consistent results. Am I doing something wrong.

94% - below 1 Point
95%-96% 2 Points
97% 3 points
98%-99% 4 Points
0% or 100% 5 Points

Reference Cell is AG2 and is .99 but for some reason I return a 5
=IF(AG2=0,5,IF(AND(AG2>0,AG2<=0.94),1,IF(AND(AG2>=0.95,AG2<=0.96),2,IF(AG2=0.97,3,IF(AND(AG2>=0.98,A G2<=0.99),4,5)))))

View 6 Replies View Related

If Statement With Range Of Numbers

May 2, 2012

Need all numbers in between the ranges set. So if I input for example 8765 then the cell still comes up with 18.

IF Cell E25 is 0-7200, then 12, If cell E25 is 7201-14400,
then 18, if cell E25 is 14401 - 21600, then 22,
if cell E25 is 21601-28800, then 28, if cell E25 is 28801 - 36000, then 34.

View 4 Replies View Related

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