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

**0**

votes

**1**answer

50 views

### Excel count all rows that contain certain values for certain fields

I have a sheet with a couple hundred rows. Each row has an 'owner' and a pass/fail field. I am making a second sheet that has statistics for the users. So for each user it will count the number of ...

**0**

votes

**1**answer

43 views

### Summing of adjacent cells in a single column that are <>0

I have a list of numbers (8780 values in one column -- a sample is posted below) some of them are <> 0 and some of them are 0.
List of numbers:
0
0
0
0
0,127227198
0
0
...

**0**

votes

**2**answers

37 views

### How to put conditional formatting in a cell from another excel worksheet [closed]

It's hard to describe what I want, I'm not really familiar with Excel condition terms but it is basically just like this:
Worksheet= Dr. castro patient consultations
A B C D E
...

**0**

votes

**0**answers

45 views

### “Filter by Selected Cell's Color” is so slow

I have a row includes like 60-70 thousand lines. 30.000 of them colored. And I'm trying to filter them by "Filter by Selected Cell's Color" but it's so slow. It take forever to filter just 70.000 ...

**0**

votes

**1**answer

46 views

### How to CONCATENATE with variable rows

Sorry in advance if I don't explain this very well, definite newbie here when it comes to spreadsheets.
I have a Text only spreadsheet with 4 columns, first 3 are Student's Last name, First name, and ...

**0**

votes

**1**answer

110 views

### Can Excel's Find formula have multiple find_text criteria?

I have a list of names on a NAMES tab range A1:A100.
On another tab 'Data Export', in column A I have an export of thousands of comments where one of those names from the NAME tab can be found within ...

**0**

votes

**1**answer

41 views

### Apply a function/formula to a range in excel

I have many operations to perform on a set of data in excel. The data is in columns, and the functions are a lot of variations on things like =(A1*C1/B1)+(A2*C2/B2)+...
Right now they are spelled ...

**0**

votes

**2**answers

40 views

### vlookup with blank look up value

I have a Vlookup running and I want it to return 0 if the look up value is empty.
example:
=(VLOOKUP(A6,F:G,2,0)+1)*C6
+-------+--------+-------+-----------+-------+-------+
| | | ...

**0**

votes

**1**answer

57 views

### Can't compare a cell to a column in excel

I'm trying to compare a column to a cell in excel several times.
The columns are copy pasted from a PDF (only way to get the information). The results are bad, with trailing spaces, some completely ...

**0**

votes

**0**answers

34 views

### Excel changes formula to numbers randomly

I have had recently a number of instances where Excel has changed formula to the numerical results of the formula :
Formula Formula
Formula Formula
Number Formula
Formula Formula
I have ...

**1**

vote

**2**answers

63 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

71 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

104 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

24 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

211 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

30 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

31 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

**1**answer

85 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

69 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

44 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

50 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

42 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

71 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

126 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

170 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

30 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

23 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

675 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

65 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

58 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

34 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

140 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

168 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

77 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

1k 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

117 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

586 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

51 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

76 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

30 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

44 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

74 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

91 views

### Getting data from closed files with concatenate formula

Each day a 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 that.
Based on all ...

**1**

vote

**1**answer

57 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

27 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

49 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

329 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

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