Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (3)

1
vote
2answers
27 views

Excel compare cells in columns A and B, show in column C cells only unique to column A

I know there's a formula for this in Excel but I can't get it right. I've got a list of 7000+ names in column A, and a list of 4000+ names in column B. Some are duplicates, appearing on both column A ...
0
votes
1answer
24 views

Return the maximum value to the left of the cells containing a given string

For an Excel spreadsheet, I am trying to find the maximum number that corresponds to a cell with a certain string. For instance, if the data is: | A | B | 1 | 3 | Red | 2 | 5 | Blue | 3 | 1 ...
0
votes
1answer
48 views

Count instances of a letter in a row if a certain cell in that row contains a specified date

I have a spreadsheet containing a column of dates (week commencing) with columns of data to the right of it, and a conditional formatting rule that highlights this week's row based on the date. I ...
0
votes
1answer
22 views

Merge 2 sheets, always keeping the value from sheet 1 if there are duplicates?

I have 2 Excel sheets that I'd like to merge. They are an inventory file and are roughly 5,500 rows each, with each row being a product and each column being an attribute. The first column is the ...
1
vote
1answer
53 views

Excel 2010 VBA only affecting last row in range

I have some VBA code written in Excel 2010 to force padding around the rows in a table, but part of the code only affects the last row in the range. Here's the full code: Private Sub ...
-2
votes
1answer
22 views

I want to have one cell that can result in 3 different values based on it's own value

I am attempting to solve for a business problem via an excel document. In this document, I am trying to solve for: "Within a cell, If the value = Option 1, then Answer 1. If the value = Option 2, ...
2
votes
2answers
24 views

Looking up last two entries in a column

This might be impossible but I hope not. I am trying to pull the last two entries from a column and add them together and then divide by two to get a percentage. These entries are updated daily so the ...
0
votes
0answers
43 views

Excel - find and remove text, surrounded by varying characters

I've got an Excel file with ~5,500 rows of inventory items. One cell in each row is a description field and contains HTML. Some (but not all) of these cells contain a link formatted something like ...
0
votes
1answer
34 views

Use INDEX and MATCH function to find value and get a corresponding value from the returned rowindex

I want to combine columns [country code] and [admin1 code] with a "." in between and then lookup that value in Sheet1 in the [admin1code] column. When a match is found I want to get the corresponding ...
0
votes
1answer
25 views

How to have excel paste a value in a specific cell according to another data table

I'm working with excel 2010, I have an excel sheet with two tables. The primary table contains a calender and each calender day has a specific numeric value in the cell below the date. The numeric ...
0
votes
2answers
48 views

When to use “0” and when to use “1”

What is difference from 0 to 1 in the following formulae? Example: =IF(ISERROR(MATCH(B2,A:A,0)),"No Match","Match") =IF(ISERROR(MATCH(B2,A:A,1)),"No Match","Match")
2
votes
1answer
29 views

When copying formulas from one workbook to another, how to make the formula to use the data from the new workbook?

OK, I know it sounds complicated, so I will try and elaborate: I have a lot of files (one created every day) - let's call them W1, W2... Every file has the same number of sheets and their names are ...
0
votes
1answer
38 views

Share cost of activities between participants in spreadsheet

Say you have a spreadsheet you use to fairly share the cost of activities between people (example google doc): A B C D E 1 Activity Cost User1 User2 User3 2 1 ...
2
votes
1answer
42 views

Horizontal Index Match Excel 2007

i have a list of customers rows 1-500 column B, with their purchases showing in columns u-AB and based on the maximum purchases done under a specific column (u-ab), the customer will be tagged as ...
0
votes
2answers
74 views

Excel 2010 formula, How do I write this formula? (VBA)

I need a bit of help from the more experienced Excel VBA users. Basically I have 2 groups of 3 cells (6 cells total) If one group of 3 cells is filled in it needs to accept it and not return a ...
1
vote
2answers
24 views

Testing a cell for multiple condiction before calculating value

I am working on a workbook that has a formula =IF(ISTEXT(G4),0,IF(ABS((G4/H4)-1)>=0.2,0,1)) Now if H4 has a value of zero I get the Excel error #DIV/0!. I want to use the ISERROR formula to ...
0
votes
1answer
16 views

Removing whitespaces from data in a column

I have two columns of data in excel spreadsheet say column A and B. There are more data in column A than in column B. I am trying to find which of the data in column A is not present in column B. I ...
1
vote
2answers
111 views

How do you make a set of cascading dropdown lists in excel?

I'm working on a system to try to take inventory more quickly instead of filling in each row by hand. The reference data is several hundred rows. The first 3 columns in the reference sheet are Class ...
3
votes
2answers
54 views

Why is this Excel formula returning two different values?

I am attempting to use Excel (2013) to look up whether the value 1 turns up in a given set (using the CHOOSE() function, then, if so, printing the value in the cell directly to the right of it using ...
0
votes
2answers
52 views

How can I return a list of specific words found in a cell's contents?

I have two columns, one with a string of text (A1), the other blank (A2). A1 looks something like this: "Briefs on Southern Africa, Rhodesia, East of Suez (Including Soviet Naval Expansion), ...
-1
votes
1answer
30 views

Conditional Formatting from Helloh, for a Noob

I'm going to try my best to explain what I need to figure out. Also, for you to understand exactly how mind bending this is for me, I haven't used an Excel formula since 2005/2006ish. We're doing a ...
0
votes
1answer
39 views

Advanced filtering leaving on unique values and removing duplicates

On the spreadsheet there are two subsets of data that are separated by color based on where I pulled the information from. Within these subsets are duplicate entries. I do not want to simply remove ...
0
votes
0answers
9 views

Pointing to another excel worksheet in a 3d table dynamicaly

I have a 3d table for some stocks of products, all sheets identical and all. If product name is "OIM", the worksheet the data is on is called "ROIM" and so on. Now, I would like to find a dynamic ...
0
votes
1answer
76 views

Excel - Script that search a sheet for a cell containing specific text, and replace the text with a specific value

I want to split a cell with data into other cells based on the data in the original cell. The data from the original cell looks like this (all content in a single cell): ...
1
vote
1answer
25 views

Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour. However, I'd like to generate a personalise replica of the first ...
0
votes
2answers
33 views

How do I refer to a cell in another worksheet with a long name?

As explained in this other Answer, SheetName!A1 = cell A1 in the worksheet called "SheetName" (without quotations). Say the name for my worksheet is very long. Without shortening "SheetName" and ...
0
votes
3answers
76 views

Formula =IF(VLOOKUP(…)=Cell;…) does not work [closed]

According to this and my own testing, using VLOOKUP can be compared to a specific value inside of IF. E. g.: = IF(VLOOKUP(...) = 20, [then value], [else value]) works. But if I compare the result ...
2
votes
1answer
33 views

Excel/Libreoffice returning TRUE for IF() instead of expression

See the following situation: For some Here cells A1:E3 have plain text data (no formulas), and C4:E4 have the same formula (i.e. typed in one cell and then autofilled to another ones). I wanted ...
2
votes
2answers
95 views

How to add or subtract to, or increment, column letters in Excel?

I'm looking for a simple versatile formula that allows simple mathematics to be done to column positions, returning the appropriate column letter code. For example: Given column A, set to +1, it ...
1
vote
1answer
38 views

Excel 2013 Column value determines another columns letter assignment.

I have 5 columns but I need to define column E by column A, If column A has a number that isn't zero in the array, column E should read "Overdue", what formula can I use?
1
vote
0answers
45 views

Using getpivot in excel to create flexible reports based on user inputs

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but ...
0
votes
0answers
24 views

incorrect IF formula evaluation

I have the following formula: =IF(SELL_INVESTMENT="YES",1,2) It returns not 1 or 2 but 0. I verified that the cell contains YES and it should return 1. The file contains over 1000 cells. "Trace ...
0
votes
2answers
38 views

Indirect turns up with #ref error

I want to use INDIRECT in a formula like =INDIRECT(CONCATENATE(A1,"+",A2),0); where A1 = 7 and A2 = 8. It comes out with #REF error all the time, but if I use the INDIRECT formula like this: ...
1
vote
1answer
64 views

IF statement with multiple answers

I want to make a table and c4 will be radius required and the minimum weight for that radius requirement will be in D4 The chart is; radius quantity 0,040 - 0,06 : 33 0,061 - 0,10 : ...
0
votes
0answers
40 views

Getting data from closed files with concatenate formula

Challenge is this: Each day program is creating an excel file for me with some data for the current day. Like what is the price for products, how many people are available today and things like this. ...
1
vote
1answer
52 views

Conditionnal Formatting with Dynamic Custom Formatted Date

My problem is that I want to color in red a list Custom Formatted Dates when the date is overdue. For instance, I got two actions: Buy Bread : Must be finished before 27/01 Buy Milk : Must be ...
1
vote
1answer
19 views

How to connect a date with data on a second tab in excel

I am wanting to know how to connect data with a date on another tab. on the first tab I have the date using =TODAY() in cell A1, then in cell A3 I have Passenger numbers.. On the second tab I have ...
3
votes
2answers
38 views

Define value of a column by another column in Excel

This seems like it should be so simple! I need to define column B by column A If column A is equal to any value in the array "taco,burger,spaghetti" column B should read "food", if column A is equal ...
0
votes
1answer
45 views

Updating another Excel spreadsheet from current spreadsheet

I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...
0
votes
0answers
9 views

Generating ID#'s in GIS attribute table

In the attribute table within GIS, I have a certain column with ascending values, and I want to assign ID numbers (for easier readability and display) to a corresponding column. So for instance: Feet ...
0
votes
1answer
33 views

Count from a cell with value to the end of row and dividing total by number of number of months regardless of blank

How do I write a formula that will auto start sum from the first cell with value though to the end which including blanks, and then divide the total by number of cells from the one with value through ...
0
votes
2answers
81 views

Compare two columns in two sheets

I have another question, even though the last one i asked is still pending. I have a large data of over 100,000 records. I am using the code below to loop through the data so that any duplicate ...
0
votes
0answers
40 views

Using Excel to calculate time differences with blank cells and calculate monthly totals

I have a sheet that looks like this: Level 1 Level 2 OFF Date Time Date Time Date Time Total T Total L2 12/3/2013 12:50 ...
0
votes
1answer
71 views

Formula to display text from multiple cells from one sheet in another

I have a list of sales reps and the names of the customers they've sold product to, on one sheet. I have tried VLOOKUP, but it will only return one set of text from the cells. I would like to know, ...
0
votes
1answer
45 views

Automatically format currency based on adjacent column - Excel

Using Excel and need a little help with a simple formula. I know conditional formatting should do the trick here, however, I need help creating the exact formula for my need. In column A I write the ...
1
vote
1answer
157 views

Change Value of Excel Cell when another cell contains specific character

I created a multi-sheet Excel workbook to track workouts. Page 1 is a calendar with clickable links to each other page. Each other page is a specific type of workout. So if you click today's date on ...
0
votes
2answers
30 views

CountIf()-data from different workbooks

I am trying to take data from two separate workbooks laid out like so: picker | lines --------+------ zf | 4 ag | 22 jw | 8 zf | 7 cr | 39 ag | 10 ag ...
0
votes
2answers
45 views

want to extract email address from a string of characters — pretty close

I am close to the end, I just need to add something in to remove the open and close parenthesis and then add a semicolon. I got this far by googling; I just need one more piece to the puzzle. :) ...
1
vote
2answers
48 views

Excel: return name of column with highest value in a non-continuous array

I have looked up many answers to find the highest value in a column, and return the column name. However, every answer is dependent on using a continuous array and uses the INDEX function. This is ...
3
votes
2answers
60 views

Count first occurrence of one criterion based on a second criterion

I am trying to write an excel formula (or formulas) to count the first occurrence of a value in a given year for each Person in my table. I've searched for related questions on Superuser to no avail. ...