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

**2**

votes

**2**answers

25 views

### Microsoft Excel - Sum drop down boxes with text

Good morning everyone. I require some help with my spreadsheet.
As you will see by the screenshot below, i have added drop down boxes with text that i pick based on different combinations. I then ...

**1**

vote

**2**answers

22 views

### How do I identify duplicate rows in an Excel cell?

I have an excel file with thousands of rows of contact info.
I need to get a list of all the rows that have duplicate first and last names.
I have been able to use conditional formatting to ...

**-4**

votes

**1**answer

38 views

### Formula to find the upper case in the sentence as true [on hold]

i have one worksheet which has a sentence in-between uppercase words, i want to know which cells has a uppercase word in the sentence. Please someone help me out with the right formula to find.
I ...

**-6**

votes

**1**answer

25 views

### Deleting some cells in Excel 2013

I have 30,000 contacts on my excel sheet and some have not included their emails. I am a beginner at excel, and would like to know how to delete all cells without emails as well as the corresponding ...

**1**

vote

**1**answer

30 views

### Excel Copy Formulas Horizontally

On Row 1, Column A I have =Sheet!1K2, on Column B I have =Sheet!1K3, Column C =Sheet!1K3 and so on.
On Row 2, Column A I Have =Sheet!1L2, on Column B I have =Sheet!1L3, Column C =Sheet!1L3 and so on.
...

**1**

vote

**1**answer

43 views

### IF formula incorporating sequential answers

I am trying to write an IF-THEN formula that outputs sequential numbers (See the example at the bottom). If you need further clarification on the process, just let me know. Here is what I am trying to ...

**0**

votes

**2**answers

28 views

### Numbering Identical Items in A Column

Suppose I have multiple, repeating names as follows:
John
John
John
John
John
Tom
Tom
Tom
Tom
Tom
Tom
Anna
Anna
Anna
Anna
Anna
Anna
I would like to find a way to tell Excel: while the name is ...

**0**

votes

**1**answer

30 views

### Excel-function IF

Column C calculates the earliest date from column A and B.
What would be the shortest function to display Row A1 or A2 in Column D as per the value of Column C?
Overhaul 1 Overhaul 2 Earliest due ...

**-3**

votes

**2**answers

41 views

### How do I get Excel to look up customer names from another sheet?

I have two sheets in Excel i.e. sheet1 and sheet2.
In sheet1's column A there are account numbers of the customer and in column B there are names of the customers.
Now I want that in column A of ...

**3**

votes

**1**answer

127 views

### How to automatically colour cell background with value selected from custom dropdown list in MS Excel 2010

In MS Excel 2010 Professional is there a way to automatically copy the background colour of the custom dropdown values from somewhere else in a spreadsheet?
I have some Data Validation on a row of ...

**-2**

votes

**0**answers

13 views

### macro to collect data from number of sheets to main page and need to add the sheet name that where the data collected

Please help me..,I need a macro to collect data(Student names) form number of Sheets(Class Name).all student names collected from sheet1, then a new column added with the sheet name (i mean ...

**-2**

votes

**0**answers

28 views

### please make excel sheet [closed]

Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:
Sheet 1, Available List:
Area tower Br# Siza Rent Sale Height ...

**-1**

votes

**1**answer

49 views

### Using Microsoft Excel 2013 to Calculate Work Hours

I am trying to develop a table that will calculate our techs' response times.
In column C is when the call was received and in column F is when the tech responded. Columns D and E are to be ...

**0**

votes

**1**answer

25 views

### Adding Index to Match formula

I have the formula below working correctly, it is returning the max value in column G when the other conditions apply (valumes in B and F). However now I know what the Max number I want to return the ...

**0**

votes

**0**answers

10 views

### Keeping cells in the same row lined up when columns in the same row have 2 different data sources

I am working on a worksheet that references data on a different worksheet (within the same workbook). Each row in the referenced worksheet represents an order. Data from the referenced worksheet ...

**1**

vote

**1**answer

27 views

### Excel return a list of stringe where 'TRUE'

Ok so I have couple of cells listing warnings:
A B C
1 Warning1 TRUE
2 Warning2 FALSE
3 Warning3 TRUE
Now I want to return list of warnings that are set to ...

**0**

votes

**1**answer

21 views

### Looking to total cells, but leave some blank in between

I've written a worksheet for selling work. I have one column "C" for the cost, and the column next to it "D" for a subtotal. Depending on the work to be performed, not all rows will have values to ...

**0**

votes

**1**answer

29 views

### Google docs - click cell to open data in popup

Is it possible to have a cell that just says "click me" or whatever, which opens a popup so you can see the full data? I want to keep all my data contained in one row per item, but one cell per item ...

**0**

votes

**2**answers

36 views

### Conditionally Formatting Across Cells

I am organizing an event and would like to do the following:
I have a General sheet with all the invitees names, addresses, etc. I also mark here if they replied and how many people will be ...

**0**

votes

**1**answer

16 views

### How to use excel 2010 functions to caculate the sum of distinct unique values?

How to use excel 2010 functions to caculate the sum of distinct unique values?
remove the duplicate values
sum of distinct unique values
but I don't need to know who are the distinct unique values, ...

**0**

votes

**0**answers

16 views

### How do I filter multiple values that are contained within a cell in Excel? [closed]

Excel lets you filter up to two values contained within a cell using the standard filter. I know Advanced Filter allows you to specify more criteria, but how do you search within a cell (ie. contains ...

**0**

votes

**2**answers

15 views

### How can I write-protect a worksheet, while permitting link jumping to another worksheet to enter comments?

There is a very important MS-Excel sheet that I maintain, which I need to share with other colleagues. For every row in the particular worksheet that I maintain, multiple colleagues could leave ...

**-1**

votes

**0**answers

32 views

### How do I SUM values in rows if several columns meet particular criteria?

Each week our company receives standard report which can't be modified.
Description 01/11 02/11 03/11 04/11
"All-in-one C340" 10 1 20 4
"All-in-one C345" 10 ...

**0**

votes

**1**answer

16 views

### Change background color of rows which are present in another column

I have data in the A and B column. B column's data is mostly duplicates of A's data, but not always. For example:
A
Budapest
Prague
Paris
Bukarest
Moscow
Rome
New York
B
Budapest
Prague
Los Angeles
...

**0**

votes

**2**answers

26 views

### Using COUNTIFS for a series of values at once

Working a step higher then COUNTIFS, I appose a challenge to write a formula without VBA code. The basic data is combined from 1000s of rows with:
Column A: rows with values from 1 to 3
Column B: ...

**0**

votes

**2**answers

42 views

### Excel 2013 - How to remove duplicates from 1 column but leave empty cells in place of duplicate?

I have a sheet with 2 columns. first column has unique values, while the second column has many duplicates.
I would like to remove duplicates from the second column , however the cells that ...

**0**

votes

**1**answer

37 views

### Excel formula to count unique values in two columns

I have two columns and I want to get distinct count for each of the account codes:
Account 12 12 34 56 90 90 91 91
Account code a b a a a b a a
...

**0**

votes

**1**answer

26 views

### What is this argument of AGGREGATE function doing in my formula?

I need to understand this part ($B$1:$B$15<>"") in the following formula used to represent a column of data with blank cells removed:
...

**0**

votes

**3**answers

80 views

### Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...

**0**

votes

**0**answers

17 views

### microsoft excel 2010 formula writing

I am writing numbers in a cell, but need the value of these numbers to change according to the letter being assigned. For example:
70 if I add a Q on the cell before (or after) it, I want it to ...

**2**

votes

**3**answers

37 views

### How to compute the number of occurrences of a string in the entire workbook?

I want to compute number of times a specified string appears in my entire workbook in some column. I have tried this formula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") But it renders the #VALUE! ...

**1**

vote

**1**answer

23 views

### Excel 2010 : How to compute the number of non blank cells in a column among the entire workbook

Let's say I want to compute the number of non blank cells in some column (A for exemple) among the entire workbook, for a fixed range of cells. How can I do this ? knowing that for just one worksheet, ...

**0**

votes

**0**answers

45 views

### Check if substring of cell in one row matches with another cell on column values in another row

I have a table with 2 columns (ID number, Description) as shown below:
ID no Description
1001 A letter to george william
1001 ...

**3**

votes

**1**answer

51 views

### Excel Formula to Check if a Word Begins with a Keyword

As the picture shows, I have 3 keywords from cell A2 to A4: a, bb, and ccc.
I have 5 words to check, listed in B2:B6: aa, bb, bc, ccand d.
I'd like to check, for each word in Column B, that if it ...

**0**

votes

**1**answer

17 views

### MS Excel: Count the max spaces between a number frequency

I have a column of numbers (i.e. 7,2,5,2,6,7,5,3,7,8,9,2,3,5,6,7) What I am trying to do is count how many numbers are between each 7. Not just the first, second, third, etc. but each instance. So, ...

**0**

votes

**1**answer

26 views

### Excel logic AND and OR functions

Does my formula below need to read (Occupancy=1)*(Gender=2) to consider the occupancy for both genders?
=(MIN(IF((Occupancy=1)*(Gender=1)+(Gender=2),Gross_Rev)))
I guess as a general question, ...

**0**

votes

**0**answers

15 views

### Excel 2010 “lastcell” issue with formula in complete column

I'm currently using a formula throughout a column in an Excel 2010 worksheet. It works fine, but I'm not really using the entire worksheet, I only don't want to have to worry about adding the formula ...

**0**

votes

**2**answers

35 views

### Make Excel marks the repeated rows with a lot of data in each row

I have this Excel archive with 5 columns of data, and then, about 4,000 rows of different data for each column, but I can’t find a way to make that Excel marks the repeated rows?
I have tried using ...

**0**

votes

**1**answer

28 views

### Sum values in range if lower than a threshold otherwise sum the threshold value

As per the title, the best I could come up with is this, however it yields an error. My unremarkable knowledge of Excel prevents me from making further progress.
This example formula should add all ...

**0**

votes

**2**answers

35 views

### Excel: how to remove leading characters of a string with a function?

I have a text column in Excel full of hexademical values like this:
0000000000000c10
00000000000036f0
00000000000274da
00000000000379e0
Which function can help me strip the leading 0's up until the ...

**0**

votes

**0**answers

14 views

### Spreadsheet Function Help - From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And There About

I have posted this question before, and got half-solution. Pls have a look at this link to get an idea.
http://yogi--anand-consulting.blogspot.in/2014/11/yogifrom-table-of-names-and-birth-dates.html
...

**0**

votes

**1**answer

20 views

### Excel formula - Return value from lower row

I'm looking for a way to search for a value in a single column and return a value in that same column two below it. Example:
A1: Apples
A2: Bananas
A3: Oranges
A4: Pears
If I search for ...

**0**

votes

**2**answers

21 views

### Why is using this code to replace a formula giving me an error?

In Excel I use the following to clear cell E5 and replace it with the word name.
Sheets("Invoice").Range("E5") = "Name"
I can also use the following to replace G11 with the VLOOKUP code I have here ...

**0**

votes

**1**answer

26 views

### Excel Conditional sum on same row

I have a rather basic Excel question, but my Excel skills suck.
What I want is a conditional sum, but I'm not sure how to do it.
Example:
A B C D E
...

**0**

votes

**3**answers

40 views

### How do I get the fiscal year to start on a specific date each year in Excel?

I used the formula listed here to generate a fiscal year based on the current month. I have an odd requirement where she wants the fiscal year to start on 6/20. For example, any dates ranging from ...

**0**

votes

**1**answer

20 views

### Subtract between two sets if one has value in the same row

In one row I have two possible places for times to be completed, Day Shift and Night Shift.
I want to do a simple subtraction where the cells are completed (Example =O9-S9 or =K9-G9 where there are ...

**0**

votes

**3**answers

30 views

### Excel Averageifs with multiple conditions in one column

I am looking for some help for how to tidy the second formula below in bold. I am looking to calculate the average 'Pupil_Score' (this is also my first try with named ranges) based on race, I am ...

**0**

votes

**0**answers

3 views

### How Do You Replace a Formula With Its Result? [migrated]

I've been searching and searching but haven't found what I think is the right answer. I'm not an advanced user of excel. At best, somewhere between beginner and advanced.
I created a Google ...

**0**

votes

**1**answer

32 views

### how to create persistent random numbers &letters in excel?

Below is link for example sheets https://docs.google.com/spreadsheets/d/1D-D29csRGAAZl_h7fyMwLJ6am0FpZAtCZ5r0XlmQVqo/edit?usp=sharing. This is a live spreadsheet, which can't be pasted in this ...

**0**

votes

**0**answers

12 views

### Google Docs Formula [migrated]

I have a problem creating a formula for my sheets. I want google docs to put either "yes" or "No" in a specific cell depending on the values in other cell. in other words I want it to put "Yes" when ...