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

**0**

votes

**2**answers

14 views

### How can I select and output the first and last row of a worksheet or a filtered list?

I am new to VBA and I have no clue where to start with this.
I have two columns of data:
Team Member Time
A 1
A 2
A 3
A 4
I am only interested in ...

**0**

votes

**2**answers

25 views

### Check if any names are present in two columns

I have two lists on names, first list of names just under 140k (Column A), second under just over 7k (Column B).
I want to see if there any matches from column A & B.
My Excel skills are basic ...

**1**

vote

**1**answer

19 views

### Look up a name from row in a timetable and output the column header in Excel

I'm making a timetable for interviews which comprise of 6 stations plus a rest.
(FYI in the timetable time is progressing downwards)
Sample of timetable with names omitted:
I'd like to create ...

**1**

vote

**0**answers

13 views

### Excel/Googlesheet: Anyway to include a comment in a referenced cell?

I have two sheets, the master sheet which has tables of data and a second sheet which references certain cells from the master sheet in order to collate certain data. I wanted to know if theres a way ...

**0**

votes

**1**answer

24 views

### I just cant figure out the formula for this excel sheet

i hope someone can help me with a formula for excel.
On my sheet i have on column A - D's for Dog, and C's for Cat, and O, for other.
I'm trying to figure out the formula for: If 8 or more of the ...

**2**

votes

**1**answer

25 views

### Increase a cell's value based on other cell's value

What I am looking for is an "IF" formula that can solve my problem:
I have a single sheet with 2 columns and an unlimited number of rows.
On the first column (Column#1), I have a drop-down with ...

**3**

votes

**1**answer

31 views

### In Excel, how would I calculate the number of Mondays between dates that excludes a list of holidays?

I'm currently using this formula:
=INT((WEEKDAY(E2-2)-E2+E3)/7).
I know how to use the NETWORKDAYS function, which allows you to a specify a list of holiday dates, but I can't figure out how to ...

**1**

vote

**1**answer

23 views

### Retrive row number of MIN IF formula

I would like to know the row being used in a MIN(IF()) formula so I can use it to grab a another value in the same row..
I have this formula:
Between the two sheets
A is an Id
B is a Date
C is ...

**0**

votes

**1**answer

10 views

### transposing and collating text from different sheets into separate sheet

I am using the following formula to collate text from 3 sheets into a 4th.
=$'sheet 1'.A1&CHAR(10)&$'sheet 2'.A1&CHAR(10)&$'sheet 3'.A1&CHAR(10)
this collates the text with a ...

**1**

vote

**1**answer

30 views

### Applying text formula only if text is in the cell in Excel

I am using 4 Excel sheets for collating text.
The formula I currently have in sheet 4 is:
=$Sheet1!A1&CHAR(10)&$Sheet2!A1&CHAR(10)&$Sheet3!A1&CHAR(10)
This allows me to write ...

**-1**

votes

**1**answer

20 views

### Auto-populate calendar with an “X” in Excel

I have have a set of due dates for preventative maintenance on equipment and I want a calendar to auto-populate with an "X" under the correct month it is due.
For example I have machine 1 due 1/1. ...

**0**

votes

**1**answer

17 views

### How to use conditional formatting to search a specific value in a range in ms excel?

I have values in the range K5:K333. I want the value in cell S5 be searched in the range and the corresponding cells of the range being formatted.
I am using conditional formatting's "use a formula ...

**0**

votes

**2**answers

21 views

### If multiple cells are with the same text, return this text(value) to another cell

I need your help with this:
If A1:E1 is "Red", return "Red" to C3;
If A1:E1 is "Blue", return "Blue" to C3;
BUT
If A1:E1 are not the same, return False or "X" to C3
What function to use ...

**-2**

votes

**0**answers

15 views

### Excel vlookup multiple value return

I am shooting a short film and i need to allocate shots to be edited to my other 4 friends.
My initial approach is to have a master sheet where all the shots are kept and then a column headed ...

**0**

votes

**1**answer

25 views

### Prefix/Suffix Data Validation List

I'm finding it really difficult to understand how to prefix or suffix a set of words based on a what I have chosen in a 'Data Validation List'.
CLICK HERE TO SEE/DOWNLOAD AN EXAMPLE SPREADSHEET
I ...

**3**

votes

**1**answer

81 views

### excel ISTEXT returns True where a formula contains the answer “”

I have a formula that returns the value "" blank.(correctly) However when testing for ISTEXT, I get the result "True" I understand "" is seen as text, however Im looking to test for actual words that ...

**3**

votes

**1**answer

24 views

### Excel Large Function with Duplicates

I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In ...

**0**

votes

**3**answers

41 views

### IF function with multiple criteria

I am trying to use an IF function to return a "1" if TRUE and a "0" if FALSE.
I need cell A1 or B1 AND C1 > 0 in order for the statement to be TRUE,or 1, else FALSE "0".
I cannot write the function ...

**0**

votes

**1**answer

31 views

### Formatting data in excel [on hold]

I am being provided information to organize and don't have options of how it is delivered. The data is editable within the "text boxes" and excel doesn't recognize any values in the cells. Almost like ...

**0**

votes

**1**answer

37 views

### Change the cell value depends upon the values in the 8 neighborhood cells in Excel [on hold]

My excel sheet contains 400 data points,its like a image pixel values,but some of my data points are missing so the value of that cell is zero,the data consist only four values 1,2,3 and 4. I need to ...

**1**

vote

**1**answer

29 views

### Make an array formula in excel calculate only on active rows

When excel calculates formulas over a column (e.g. a lookup), it's smart enough to only calculate until the last active row, rather than also running on all the blank rows.
With array formulas, it ...

**0**

votes

**1**answer

19 views

### sum values based on match criteria to another table

I would like to sum prices in one table based on a match criteria from another table
example table 1
Grocery Items Price
Apples $2
Oranges $3
Bread ...

**0**

votes

**0**answers

12 views

### Excel 2010 merge rows where data in duplicated except 7 columns

I have a xls file.
I has some duplicate rows of customer names and other data that is identical.
I want to be able to find the identical rows by customerid and copy data from one row to another ...

**0**

votes

**0**answers

12 views

### Filtering Pivot Table

I have this Pivot Table, and what I need to do is: Filter this data and show only those records whose date (3rd column) is equal to its parent date (2nd column) or just 1 day difference is there.
And ...

**0**

votes

**1**answer

25 views

### Change column references by dragging down rows

I have this equation in excel =COUNTIF(A2:A10001,"<0.5")
Is it possible to make it so that when this formula is copied down the formula becomes =COUNTIF(B2:B10001,"<0.5") and so on?

**-2**

votes

**2**answers

30 views

### Excel - Finding and Allocating

I need to find the same values in column A (e.g. column A can contain buy a dog, white dog, cute cats, cuddly cats - I would need find all "cats" and then assign them to certain categories, e.g. "CAT ...

**1**

vote

**1**answer

14 views

### Convert formula to a comment

When programming, I often comment code out whilst I'm debugging.
I have a formula, which works fine, but I want to comment it out as I don't need it at the moment but will do in a few weeks time.
...

**0**

votes

**0**answers

8 views

### Count data from one column if other column is blank

I have a table which contains data read from a database and imported into the spreadsheet. What I am attempting to do is create a "live" report that will collect this info (on refresh) and calculate a ...

**1**

vote

**2**answers

35 views

### I need to find a number pattern in a string and copy it to another cell In Excel

I need to look at cells in column A to find the number pattern of NN-NNN and then copy that to cells in column c. e.g.- Column A has "SPREAD_11_PLAT_SHTS_22-315_IFR" and I need to copy the 22-315 to ...

**6**

votes

**3**answers

41 views

### Alternate If() function in Excel/OOCalc

Countless times over years, I've wanted to use an If statement in Excel/OpenOffice Calc in the form of:
If(A (comparison operator) B, A, C)
or, equivalently useful:
If(A (comparison operator) B, ...

**-1**

votes

**1**answer

33 views

### How do I populate cell based on value from two separate cells?

Can anyone help me write the below formula the correct way?
=IF(C13="North Dakota","$2,500",IF(C13="Montana","$2,500",IF(C15="Yes","$5,000","")))
What I wish to achieve is: If value selected in ...

**1**

vote

**1**answer

14 views

### Format Number with Leading & Trailing Zero

How can I convert 32.5 to 000003250 in Excel 2010?
I have tried TEXT(A1,"#000000.000000") and =A1&REPT("0",6-LEN(RIGHT(A1,LEN(A1)-FIND(".",A1))))
Both examples return with decimal point ...

**0**

votes

**1**answer

15 views

### Calculating number of complete boxes and loose items for Scout Popcorn Sale

I'm working on a table to calculate popcorn orders for my Boy Scout Troop annual sale. I want to calculate how many cases and loose item count of a specific item I should be receiving.
For example, ...

**0**

votes

**2**answers

36 views

### Excel: check if two cells have specific content and show true/false in another

I have a large datasheet with multiple columns. Long story short, my columns A and B (of text type) are lists of codes. For simplicity's sake let's say column A is days of the week, while column B is ...

**0**

votes

**1**answer

41 views

### VLOOKUP cells that contain a certain text in Excel

The challenge is as follows: I have a column with names extracted from a source. The names come in different ways each time it is extracted. For me to be able to use it, I need the names in a specific ...

**0**

votes

**1**answer

25 views

### COUNTIF and SUM in Excel with different ranges

Is there a more efficient way of making the following function?:
>=COUNTIF(A1,">0")+COUNTIF(C1,">0")+COUNTIF(E1,">0")+COUNTIF(G1,">0")
I want to count if the cell is bigger than 0 ...

**0**

votes

**1**answer

30 views

### Need unique count of text from multiple columns by category

My data looks something like (assume begins in A1):
Category,Label1,Label2
Dogs,blank,doe
Dogs,zoo,blank
Cats,zoo,yup
Cats,doe,hgg
Cats,blank,doe
What the result should look like:
...

**1**

vote

**4**answers

34 views

### Excel 2013 - Fill in numbers from 2000 - 65000 without using the drag to fill handle mouse method

I need to fill in numbers from 2000 to 65000 in Excel 2013 and using the fill handle and dragging is a very long process.
Is there another way to do this?

**2**

votes

**2**answers

80 views

### Excel - return multiple values for each of a list of values

I've been trying to solve this for a good few hours now, but with no result...
My setup:
I've got excel sheets of cosmetics product recipes.
Each recipe contains dozens of ingredients, each given ...

**24**

votes

**3**answers

1k views

### How do I hide the #DIV/0! error while a referenced cell is blank?

In Column C I have Production. In column D I have Goal. In Column E I have variance %. My formula is =(D11-C11)/D11
However, how do you hide the cells down the sheet until you put something in D11 ...

**1**

vote

**2**answers

23 views

### Apply same formula to large column?

I have 928 numbers in one column in excel. I need to apply the same conversion formula to all these numbers. I've been looking around here and most of the answers to similar questions tell me to use ...

**3**

votes

**1**answer

28 views

### Sum hours and minutes from separate columns into one grand total

This is the result that a client of mine requires. My own sense is that this approach is not practical. How could we write formulas to achieve the following grand total?
The Hours column has 31 ...

**0**

votes

**0**answers

13 views

### Use COUNTIF to define a calculated (dynamic) address

I'm trying to find a way to use a COUNTIF formula with a calculated address as the first and last pointers to the range of data. Normally, one might specify something like this
...

**0**

votes

**1**answer

27 views

### What Excel Formula will count the number of responses which match a condition but only once for that person?

I am trying to count survey responses. I would like to be able to answer how many people have ever answered the question with the answer value "1". However, people have answered the survey multiple ...

**1**

vote

**0**answers

13 views

### I am trying to turn named ranges into named tables while keeping Auto populating headers

I have a spreadsheet with 32 individual named ranges and the Section Title (B338 for example) is linked to the equivalent value in the Table of Contents (B70 for example). What I would like to do is ...

**-1**

votes

**0**answers

9 views

### Formula or any other method Numeric to words [duplicate]

I want to make a formula or any method for convert numeric to words.
like (280000.61) = (Rupees two hundred eighty thousand and one only)
280150 = Rupees two hundred eighty thousand one hundred and ...

**0**

votes

**0**answers

33 views

### Excel VBA code to remove duplicate row however copy past unique values in Column A seperated by commas [closed]

we have about 3k rows where if there is a duplicate in the address column (column C) we will need to:
1) If duplicate address in column C, copy paste all the uniques (ID) values in column A and ...

**0**

votes

**0**answers

37 views

### Excel 2003 - Matching and summarising data across multiple worksheets

I am looking for help regarding my roster calculations.
I have built a roster over 13 worksheets plus a calculation worksheet.
On each sheet column D has a list of names populated from an offset ...

**1**

vote

**2**answers

61 views

### Excel: using 2 cells to collect data from a list

My Example is this:
(workbook name"Rev)
letter weekly bi-weekly monthly
a 100 200 400
b 110 250 500
c 111 300 600
d 112 350 700
e ...

**0**

votes

**0**answers

11 views

### Forcing link formulas to be relative rather than absolute in Excel 2013

I am working with a simple "paste link" situation and I have set up a separate button to make it easier (I have about 500 different pastes to make). How can I force the formula to be relative ...