# Excel 2011 :: Giving Text Fields Numeric Value For Use In Function?

Feb 15, 2014

I am creating a spreadsheet for results of a survey. I am trying to give numeric values to text fields so that I can place the cell values later into a SUM function. The text fields are entered into the spreadsheet via a drop down list in each cell I created by utilizing the validate button. The text field contains choices like The text field contains choices like “1 Very poor,” or “4 Very much.” The source for the list is on a separate sheet.

EX: If cell C5 equals the choice “1 Very poor,” and cell C8 equals “4 Very much” I want the cells to have numeric values of 1 and 4 respectively so that I can utilize an equation like =SUM(6-C5+6-C8) later in the spreadsheet to calculate aggregate scores.

I am using a 2011 for Mac version

## Excel 2010 :: Giving Scores A Numeric Rank

Aug 13, 2014

I have attached an Excel 2010 spreadsheet to show the question.

Essentially, I need to have a formula to indicate the numeric rank of each grade for each student.

I could sort the columns by NAME and SCORE.

However, I would prefer to have a formula that ranks each student's subject grades - some students have multiple numbers that are the same.

## Counting Unique Fields Containing Text And Numeric Values

Sep 19, 2012

I have used the advanced filter option in excel 2010 to display all the unique occurences of a list of names (column B) and then used the count function =COUNTIF(A\$2:A\$21,B2) to count the occurences of each name (column C).

However, excel did not manage to take into account the last 3 entries in column A (mir-23). I assume this is because it is a mixture of text and numeric values. How can I include this information in the grouping and counting?

excel1.png

## Substitute Function Giving TEXT Output?

Oct 2, 2012

I've in cell A1 an entry like this: 123*456*7890

=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890
=ISTEXT(A2) in cell 'A3' gives me: TRUE

BUT, =A3+1 gives me: 1234567891

Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?

## Excel 2011 :: Find Multiple Text Strings In Another Text String

Mar 8, 2013

How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.

A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.

After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.

New words will be added to the sets of words continually as required.

Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.

I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.

The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.

If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.

Happy to consider a different solution if the task can be done better a different way.

Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.

## Excel 2011 :: Text To Columns VBA (Mac)

Feb 27, 2012

My VBA which works fine on Excel 2007/2010 does not work on Excel for Mac 2011.

how I would convert this to work on Mac 2011?

Code:
Columns("AI:AI").Select
Selection.TextToColumns Destination:=Range("AI1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _

[Code]....

## Excel 2011 :: Extracting Data That Is Text Based (Mac)

Jul 26, 2014

I have a workbook with 2 sheets.

Sheet 1 will act as a summary for sheet 2, showing only the last 5 entries from sheet 2.

Sheet 2 is based on football results of a season which will have a total of 34 league games plus cup games.

Sheet 2 has 4 columns:

Game Number Home Team Result Away Team
1 United 2-0 City
2 Sportive 4-2 Uniao
3 Real 1-0 Ateltico
4 Villa 1-3 City
5 United 2-1 Real
6 Atletico 0-0 Villa

So sheet 1 will show the last 5 results as the season progress's and new data is added.

I have been able to extract column 'GAME NUMBER' using the following formula:

=INDEX('Back end 1'!A:A,MATCH(9.99999999999999E+307,'Back end 1'!A:A)-ROWS('Back end 1'!A2:A\$6)+1)

I am struggling to find a way to pull the last 5 data that is text based.

I am using Excel Mac 2011.

## Excel 2011 :: Conditional Formatting Based On Text Match From If Statement?

Oct 2, 2013

I want to highlight a cell if the text displayed from an IF formula is equal to the cell content.

CELLS BK10:BN10 are merged and have entered into them the following text "SELL"

Cells BJ20:BP20 are merged and contain the following formula which currently results in the cell displaying "SELL"

=IF(BM22<=-0.08,"SELL", IF(AND(BM22>-0.08,BM22 < -0.03),"NO INDICATION", IF(BM22>=-0.03,"HOLD")))

Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%

When I set up a CLASSIC Conditional Formatting using a formula (="If(\$BJ\$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?

I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.

## Excel 2011 :: Copy Cells From Consecutive Worksheets And Paste Sequentially Underneath Each Other On Output Page With Loop Function?

Feb 18, 2014

I have data from (row 1, column 1) to (row 53, column 5) on 283 consecutive worksheets in a singular excel file that I would like to be presented on a singular worksheet starting from the data on worksheet 1 and descending to the data on worksheet 283.

I am looking for a copy and paste loop solution that will copy the data from each page and sequentially paste the results on a singular output page in descending order (worksheet 1 data, worksheet 2 data... etc) so that I can sort the data.

## To Check 102 Fields If They Are Numeric

Mar 26, 2009

I have got 102 quantity of TextBoxes in my form.

Each field represents the number of "SALE QUANTITY" , "FREE GIVEN" , "RETURNED" products. We have got 34 PRODUCTS range. (34 x 3 fields = 102 textfields)

The name of the TextBox fields are as follows :

PR01S , PR01B , PR01K

PR02S , PR02B , PR02K

...

...

PR34S , PR34B , PR34K

All of the fields are required to be numeric data only. How can i check this fields to be numbers on the same time of the data input (or when jumping to the next field. There will be a message box mentioning that the field can only contain numbers + SetFocus to the wrong input field) ?

I dont want to write same code for each item, its too hard and long..

If i can solve this problem, then I will need help to enter the values from this fields to the worksheet because I must find a way to enter the values with a for next loop or something.. I dont want to do it one by one.

Here is how the form looks :

I dont know if there is a possibility to exclude the product to be entered in the worksheet if all 3 boxes are Zero (0) for that specific item.

Example : If all fields are "0" for item "Coke" then I dont want this products information entered in the excel worksheet.

0 Sale , 0 Free, 0 Return

## Counting Non-numeric Visible Fields

Aug 3, 2007

I searched for a solution to my problem, but couldn't find one that matched. Is there a formula that counts the number of visible fields. The data in my sheet is non-numeric. I tried COUNT, but it didn't limit the number when I used autofilter, so that formula doesn't seem to work.

## Automatically Update Autofilter For Change In Numeric Fields

Jul 1, 2009

I'm trying to use " autofilter". I put the code as
Worksheet_Change(ByVal Target As Excel. Range)

so it'll update by itself. My criteria are on row 5 and all my data is below row 7. Row 7 contains the headers.
This all works fine for text-fields. My question is if someone can get it working with numbered fields?

## Excel 2007 :: Find Text In A Range Of Fields - Case Insensitive

Mar 2, 2012

I am trying to find a way to see if a range of cells contain (not exactly, just contains) the text in another cell.

For example:

A1 Fred Flintsonte 2012 B1 Barney Rubble C1 Barney Rubble
A2 Barney Rubble 2012

If a cell in range A1:A2 contains text in B1 (CASE INSENSITIVE), then C1 equals B1

I've tried entering this formula in C1, but it does not work:

=IF(ISNUMBER(SEARCH(B1,A1:A2)), B1, "")

## Excel 2007 :: Combobox Result Coming As Text Not Numeric

Feb 10, 2011

I created a activex ComboBox (under form controls) to show numeric figures.However, the combo box linked cell is showing the numbers as text.

How can I correct this issue?

I run Excel 2007 version.

## Sumif Function Not Giving Correct Total

May 24, 2012

I'm trying to add the total product sold by a group of sales person for example : Chad + Javier. My actual worksheet includes 600 rows of salespeople and 20 rows of product, i'm using this simple example to get the message across.

I was using =sum(sumif(A1:A9,"Chad',D1:D9),sumif(A1:A9,"Javier",D1:D9)), the formula should have given 91, but instead is calculating 113...

Salers PersonProduct AProduct BProduct C

[Code] .....

## Payment Function Giving Negative \$ Result

May 24, 2006

I'm building a real estate payment calculator. I'm using the Payment (=PMT) function in Excel 2003. When I add the information that it asks for in its wizard, I receive the correct numerical figures but with a minus sign in front of it making the result negative, i.e. "-\$630.13" instead of "\$630.13."

Is this file corrupted or have I put something in incorrectly?

## Excel 2011 :: Save As XLS File VBA On Mac OS X?

Aug 15, 2013

I am running Excel 2011 on Mac OS X. I am trying to save a file (.xls) to a server (Autumn). I have tried several configurations to try to get the file saved properly. File name is "Testfile.xls"

1. folderpath = "Autumn:Tim:TestFolder:" & filename --Activeworkbook.Saveas filename:=folderpath

2. folderpath = "Tim:TestFolder:" & filename Activeworkbook.Saveas filename:=folderpath

3 Activeworkbook.Saveas folderpath

When I try this, I get an Error 1004 indicating that it cannot find the file. Do I need to eliminate the .xls and then specify that when I do the saveas?

## Excel Formulas Giving 0.00 Value?

Feb 4, 2013

I have a very large excel spreadsheet with many formulas in it. Some of the formulas are giving a 0.00 value even when there should be a \$value. I don't know why this is happening? It is not happening to every formula and it is not consistant within the same row or even the same column. Most of these formuals have been used in this spreadsheet for years by many different people with no problems.

## Excel 2011 :: Copying Calculated Hyperlink

Sep 26, 2013

For Mac Excel 2011, though I think my question would apply to any modern version of Excel.

This is a little odd. I have a sheet where, for various reasons (see below ### if interested), I need to copy a hyperlink, replace a substring, and then return the new hyperlink (with old friendly text) to the original cell. (I actually have to do this with an arbitrarily-long column of links.) This was my plan of attack:

* Pull the URL from the hyperlink cell.
* Use FIND and RIGHT to strip out what I need and replace it
* Use HYPERLINK to build a new cell with the original friendly text and the new hyperlink.
* (Manually) copy this new link (really, a column of links) back to the original cell (really, column of cells).

Here's where it breaks down:

a) If I simply cut-and-paste, the formula used to pull the hyperlink becomes circular (as do several others), and Excel complains.
b) If I cut-and-paste-special (values only), I get just the friendly text, no link info. Likewise if I try to reference the "fixed" hyperlink.

The erroneous hyperlinks are in column K. My calculated correct hyperlinks are in column S. (There are similar errors and fixes in L and T, respectively.)

## Excel 2011 :: Loop Through All CSV Files In A Folder

Nov 23, 2011

I want to open all csv files in a folder and just copy and paste some columns into another file. I am having a bit of trouble with the following code which can be found at ozgrid and I have modified slightly so it looks in the current directory instead of a fixed path...

Code:
Sub RunCodeOnAllFiles()
Dim lCount As Long
Dim MyDir as string

[Code]....

For starters, if I don't use on error resume next I get a runtime error 445: object doesn't support this action. So when I use the on error resume next, then by the time it gets to the foundfiles line, there are no variables set, so on the next line (workbooks.open) nothing happens.

Is there an easier (more up to date?) method to open all csv files (or excel files) in a folder?

## Excel 2011 :: Creating Button On Spreadsheet?

Dec 12, 2011

In MS Excel 2011 for Macintosh can I assign a macro to an object, such that if the user clicks the object it runs the macro? That is creating a button on the spreadsheet that will execute a macro.

## Excel 2011 :: Drop Down Menu With Results?

Jan 25, 2012

So i am working on an attendance tracker here. I already have aligned on a separate tab all of the works aligned next to their manager

EX
Column A: Column B:
Worker Name Manager

What i would like to do is create a drop down menu with all of the Managers name listed to where someone can select that manager and when they do all of the workers names show up in Column A and then that Manager Listed next to them.

Is there a way to do this... I am using office 2011 btw. Once when i get this last part completed with this drop down menu i should be complete!

## Excel 2011 :: Column And Row Headings Missing (Mac)

Jun 5, 2012

I have a friend using Excel for Macs 2011 and the column and row headings (ABC & 123) are not showing on the spreadsheets on the screen. How to turn them on. I fgure they got turned off somehow?????

## Excel 2011 :: Exact Some Data From A File

Sep 15, 2012

I use mac and I have "Excel for Mac 2011".

I need to exact some data from a file. I can open the file, but can't see any data of it. I am afraid I need to do some setting so that I can see the data, but I don't know what should I do

## Excel 2011 :: Using Macros To Export / Archive

May 2, 2013

I'm using Excel 2011 on a MacBook Pro. Here's my situation:

I'm running a monthly budget report that's got three spreadsheets within the workbook. Spreadsheet #1 is summary sheet with a few pivot tables. Spreadsheet #2 is the data sheet, where all of the information is entered, and is what feeds the pivot tables on Spreadsheet #1. Spreadsheet #3 is just a list page that populates the drop-down list on the second sheet.

As this is a monthly budget report, I'd like to find some way to archive the first spreadsheet (the summary sheet) every month. Ideally, I'd like to be able to create a macro-driven button that, when clicked, does the following actions:

1) Creates a new spreadsheet, with each click, named for the current month and year.
2) Archives the exact contents of Spreadsheet #1 by making an exact copy of its current contents, and places this copy into the newly created spreadsheet.

To summarize, I'm trying to use a button that, when clicked, runs a macro that archives the current month's data into a new spreadsheet, and then clears the data from Sheets #1 and #2 so that I can start each month with a clean slate. If there's a way to only clear the data from certain cell ranges (as opposed to a complete wipe of the sheet), that would be even better.

## Excel 2011 :: Insert Calendar Dropdown For Mac

Dec 5, 2013

How to insert a Calendar Dropdown for Mac Excel 2011? Also, do I have to readjust this (for lack-of-a-better-word) 'add-in' everytime the calendar year changes? This will be used for a payroll template.

## 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?

## Excel 2011 :: Looking For PV Formula That Accommodates Delay Period

Aug 2, 2014

My software is Excel for Mac 2011 version 14.4.3 (140616).

I am using the present value formula =PV(rate, nper, pmt, [fv], [type]), with the pmt expressed as a negative number, to determine the present value of a lump sum that would generate specific, equal, regular annual payments, exhausting itself in the process. I fill it in this way: rate is an annual rate, nper is the number of years the payments run, pmt is the annual amount paid each year, fv =0, type =1

I would like to be able to have a similar formula in Excel that would do the same thing except that it would take into account a time period where no payment is made, ie a "delay".

For example using the PV formula I know that if I have \$49,019.96 invested at 1%, it will generate \$10,000 per year for 5 years, leaving a net investment of 0. I would like to know the formula that would tell me how much I would need to invest at 1% to yield \$10,000 per year for 5 years, again exhausting itself completely in the process, with the first \$10,000 payment starting 3 years from now. I know it would be less because I would have the full amount earning interest for 3 years at 1% before any payments need to be made.

## Excel 2011 :: Multiple Dependent Dropdown Lists

Feb 26, 2013

I am working on a spreadsheet for my Building company. I'm building a tool to price for Fascia, Soffits and Cladding.

I'm using Macbook Pro Microsoft Office for Mac 2011

The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.

The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.

Example:
Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33
Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"

So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C

The Value column would then show a value based on the options selected.

## Excel 2011 :: Shuffle Row Position After Data Refresh?

Jul 12, 2013

I am using Excel 2011 and we have a link data source to the outside SQL Server.

For the 1st step, I linked to the Database and retrieve the data.

P1.png

2nd step. Sort the data by using the column "Description" Descending. Add another column to the table and put the 1, 2, 3, etc in that column.

P2.png

3rd step. I clicked on "Refresh" and the new column values are not consistent. I mean they are moving. So, I added another row in the database to test and it becomes very obvious. It can be clearly seen at the following image. The value of "A" should always be "10", but it has been changed to "1" after the refresh.

P3.png

how I could retain the position of another column after the data refresh?