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

**0**

votes

**1**answer

20 views

### How to match Reg No. in one workbook to another

I want to find out a formula to match Reg No. for two workbooks.
First work book is a master book I update everyday almost, next workbook I have employees name and ID numbers only, nos are in ...

**1**

vote

**1**answer

198 views

### Dragging a cell reference in intervals

I have data as follows (simplified) in two worksheets on a document:
Sheet 1 data:
A1=1 -- B1=(Blank) -- C1=(Blank) -- D1=2 -- E1=(Blank) -- F1=(Blank) -- G1=3 ...
Sheet 2 data:
A1='Stage ...

**0**

votes

**0**answers

165 views

### Excel: Finding comma delimited values in reference range

I have a two-sheet excel file in which I want to see if specific values are present in some column, based on comma separated values in some other cell. The idealized case would be:
Sheet1: Cell A1
...

**-1**

votes

**2**answers

301 views

### Spreadsheet for Daily Equipment Hours

My goal on this is to keep track of equipment hours and have a daily report to reflect total hours and run time.
I want to make a file with 31 days of worksheets in the workbook for the complete ...

**0**

votes

**1**answer

188 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

52 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

71 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

84 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

107 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

361 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

86 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

93 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

2k views

### Indirect function for worksheet names in Excel?

I am wondering if there is an equivalent to the indirect function for worksheets or workbooks instead of cells. What I mean by that is I am updating a rather large and unwieldy workbook for payroll ...

**0**

votes

**1**answer

90 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

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

**0**

votes

**1**answer

32 views

### Excel assigning different dollar values and adding them

I am creating a spreadsheet and I want to assign different dollar values and add them together. For example I want B to represent 10.00 per hour for each hour I type in then I want C to represent ...

**1**

vote

**2**answers

928 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

220 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

251 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

29 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

130 views

### Excel: What formula to use to return the minimum or maximum of a set of lookup values? [duplicate]

I'm looking for a way to calculate the values shown in cols D & E in the below screengrab. Column D contains the earliest date from column B where the name in column A matches. Similarly, column ...

**1**

vote

**1**answer

436 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

49 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

97 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

327 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

110 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

111 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

55 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

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

**-1**

votes

**1**answer

289 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

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

697 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

50 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

37 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

**1**answer

19 views

### Can I apply conditional inputting on Excel?

In excel,I want to input unique text in every cell of a column.suppose I adding name of some people on that column,and I don't want to add one persons name twice by mistake.so I want that if I input a ...

**1**

vote

**2**answers

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

138 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

87 views

### Excel If formula

I am trying to run a simple formula in Excel:
=IF(V3>419, "Pass", IF(V3>459, "Merit", IF(V3>499, "Dist", IF(V3>519, "Dist*","Fail"))))
I have 2 columns; Overall and Grade
Overall ...

**0**

votes

**2**answers

89 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

38 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

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

**1**

vote

**1**answer

267 views

### Can you define an array formula across multiple columns?

I have a table of Stores (well, for this example anyway):
Store_ID Store_Name ...
1 High Street
2 Low Street
...
I have another table of Employees:
Employee_ID Store_ID Is_Active
1 ...

**0**

votes

**1**answer

519 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

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

**1**

vote

**2**answers

207 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

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

709 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

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

104 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

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