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

**1**

vote

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**3**answers

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

**1**answer

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

**2**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**2**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**0**answers

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

**1**answer

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

**2**answers

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

**0**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**2**answers

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

**2**answers

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

**2**answers

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. ...