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

**0**

votes

**1**answer

6 views

### Excel - sumifs and indirect

I am trying to obtain data from multiple worksheets:
Where:
sheet 1 - has an F2 cell that enables you to choose from a drop down list of names and each of these names equals a different worksheet in ...

**0**

votes

**2**answers

41 views

### Calculating time difference between two date/times from two columns in Excel

I have 5 columns: Start Date, Start Time, End Date, End Time and Duration. I need to calculate the duration between the Start Date/Start Time and the End Date/End Time. I tried just subtracting the ...

**1**

vote

**1**answer

32k views

### Can Excel use formula-input data for cell references?

I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell.
For example:
A1=5
A2=10
A3=insert syntax here, using A4 as source for cell ...

**0**

votes

**1**answer

22 views

### Automatically Copy the only Credit rows into a Sheet

I am trying to use a formula by the below, With out use Developer Options.
I have a excel workbook and there is a two sheets
One is -
One sheet is (sheet1)-
TXN. ID Transection Type After ...

**-1**

votes

**0**answers

13 views

### Excel - Timesheet query - find first booking in and last booking out in a day

I have a spreadsheet of about 5000 rows as follow
COLUMN A: EMPLOYEE NAME - Jo Bloggs, Sheryl Smith, etc
COLUMN B: DATE/TIME
COLUMN C: EVENT (IE "BOOK IN" OR "BOOK OUT")
I am trying to find the ...

**1**

vote

**2**answers

24 views

### Filter OpenOffice Calc rows by matching if a cell in one column exists in another column with a certain value

Have a question I cant seem to figure out using the filter.
Column A has 1400 rows with 800 additional rows that are duplicates (so 2200 total rows).
Column B has inventory amount for only the last ...

**0**

votes

**0**answers

27 views

### Microsoft Excel 2007 - Auto Update in 1st Sheet based on What updated in 2nd sheet (2nd sheet Update will be in Fixed Column but Random Row)

Actually I'm facing a problem now.
I am using Excel 2007.
And, I have two different sheet of excel file with me.
My 1st sheet is my report.
My 2nd sheet is my customer name.
I hope that when ever ...

**0**

votes

**2**answers

35 views

### How do I find the next smallest/largest value without sorting?

I have a table from which I need to pull the next largest and/or smallest value, relative to a value in a separate table. However, I also need to allow the first table to be sortable by any criteria, ...

**0**

votes

**3**answers

33 views

### Excel filter duplicate values in column A and filter out rows which have column C blank

Total n00b here.
How to filter duplicate values in column A and filter out rows which have column C blank.
I have 2200 rows in Col A (the first 1400 rows also have data in Col B & C, the next ...

**2**

votes

**4**answers

56 views

### In Excel, how to count enormous number of timestamps, and arrange counts in 5 minute periods sorted by day of week?

I've got just over 300,000 rows in a spreadsheet, and each of these rows contain a timestamp (HH:MM:SS AM) and a date (DD/MM/YYYY).
I'd like to get counts for these timestamps and group them in 5 ...

**1**

vote

**1**answer

28 views

### A formula to check if the date is older or equal to 60 days and another columns cell is equal to a value

I have tried using conditional formatting and managed to get the first part of the formula to work.
=D2:D999<=TODAY()-60
I want to then also check if the value of what is in column G is equal to ...

**1**

vote

**1**answer

18 views

### VLOOKUP not interpolating all cells?

My VLOOKUP formula is not interpolating all cells
AD AE AF
11-6-2014 15:20 90,4 #N/A
11-6-2014 15:21 89,1 #N/A
11-6-2014 15:22 90,4 Good
11-6-2014 15:23 89,1 Good
AF column is my ...

**0**

votes

**1**answer

3k views

### Copy/move values matching a pattern to another column

I have a worksheet where we need to move all the values matching a pattern to different columns.
For example, we have a B column with values that are one or more letters followed by one or more ...

**1**

vote

**1**answer

15 views

### How to remove one column of a calculated item in Pivot?

I have two columns. One with data from this year and the other with data from previous year. I now have added a calculated item in Pivot to display the % delta of these two years. Now when doing this ...

**0**

votes

**2**answers

2k views

### Comparing columns in two different excel sheets and if they match copying third column

I have limited working knowledge of Excel and I hope someone will be able to help me with my problem.
I have two different work sheets (say F1 and F2) with last name in Column A, first name in Column ...

**0**

votes

**1**answer

84 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

21 views

### In Excel I need to find data on sheet3 from data on sheet1

I have an Excel workbook with several sheets. The column 'SheetA'!W:W lists text that can be found within a column header on row 'Sheet3'!1:1. For example, SheetA'!W42 contains "B16". Cell ...

**0**

votes

**1**answer

16 views

### Last occurence in a “right vlookup”

I don't know if the question must be put here or on SO. Sorry if i make a mistake.
I have to search the first and last occurrence of a certain value within a column(array).
I have managed to make use ...

**4**

votes

**3**answers

209 views

### Suggestions for building and managing complex functions in Excel

Far too often, I've got a formula that wraps functions inside of functions inside of functions, and when some new condition requires that I wrap another function around it, I often find myself ...

**1**

vote

**2**answers

2k views

### Comparing Two Columns in Excel, Returning a Third

I've hit a bit of a brick wall in my Excel-ing. Below is the example and explanation.
Sheet 1:
Column A | Column B | Column C | Column D
_ Name _ | _ Date ._ | _ Cost .._ | _ Code _
...

**1**

vote

**2**answers

16 views

### Rolling up the status of an excel column to a single cell

I am an intermediate excel user at best. I've looked everywhere back can't seem to be able to find this specific formula how-to.
What I need to do is in a status worksheet I have a series of tasks ...

**5**

votes

**4**answers

22k views

### How to calculate hours and minutes between two time periods?

I’m working on a very simple time sheet for my work in Excel 2007, but I have run into trouble about calculate the hours and minutes between two time periods.
I have a field: timestart which could be ...

**0**

votes

**1**answer

24 views

### excel spreadsheet average with prioritization

I need help how to write excel formula (or any excel solution)
to sum with priorities.
I have transaction data like this:
Date Unit Price Buy/Sell
7/11 3 10 Buy
7/12 2 9 ...

**0**

votes

**2**answers

14 views

### Using Index Match Conditionally to Indicate Whether

I feel pretty comfortable with INDEX MATCH, but I came across a problem I'd like to use it on where I'm not quite sure it works. I have two sheets: the first smaller sheet has a small subset of ...

**1**

vote

**2**answers

19 views

### Single formula to search column and return corresponding fields

Am having issues creating a single formula to search the billionaires list by name and autofill the rank, age, net worth, and citizenship field in the search spreadsheet.
This is the formula I have ...

**0**

votes

**2**answers

28 views

### Excel - How to compare columns for equal values

I have an Excel sheet which has two columns. Both columns have numbers. The first column contains more numbers than the second column. I want to find which all numbers in the second column are there ...

**0**

votes

**2**answers

27 views

### Excel: complicated SUMPRODUCT formula needed

I'm in search of a SUMPRODUCT formula, or a similar sort of formula which does the same thing. It should do the following:
On worksheet A it needs to ignore incorrect zipcodes, meaning zipcodes ...

**0**

votes

**1**answer

36 views

### Conditional Formatting for one cell value in a list, another cell blank

Looking for how to do Conditional Formatting for the following rule:
If a number in a cell equals one of a list of particular numbers, then information MUST be entered into an additional cell. ...

**1**

vote

**0**answers

15 views

### Looking up the same value in a row and returning multipe corresponding values

I am trying to lookup value 1 in the example below and return corresponding column name. When I use index match, it only looks up the first column which corresponds to 1 in the below example, it looks ...

**0**

votes

**2**answers

21 views

### Populate one column with data from a list and match other column data

I have an Excel 2013 Workbook that has a worksheet (sheet 1) that contains two columns (one column for number and the second column for description, they are separate columns, but 1:1).
On the other ...

**0**

votes

**0**answers

19 views

### EXCEL: I have a column of single-word strings and would like to see if any of these strings are contained in a column of multi-word cells

Example:
In column A I have a list of words "Apple, Banana, Orange, etc." each in their own cell.
In column C I have a column of multi-word strings like C1="I ate an apple", C2="Bananas are gross" ...

**0**

votes

**1**answer

25 views

### Excel 2013 Averageifs

I want to calculate the average of time in a column of cells if the time falls within a range in another cell.
E.g. If a patient comes in between say 6 and 8 pm I want to average data in another ...

**0**

votes

**1**answer

16 views

### Error outputting a string to cell

I am using a vba macro to automate part of an excel spreadsheet. The following line keeps giving me a runtime error 1004# application or object defined error
Sheets("Material").Last.Offset(0, ...

**0**

votes

**1**answer

29 views

### sumif(s), sumproduct for many to many relationship

I am looking for a formula/function that will allow me to essentially SUMIF(s) across a BOM. I know my part to FG relationship (Part, FG) and I know how many FG's I need(FG, Qty).
How can I ...

**0**

votes

**0**answers

23 views

### Using Query Function in Google Spreadsheet where {data, query, [headers]} is determined in another cell

For example in cell T2 contains the following text generated with the =CONCATENATE function
DW004!$A$11:$H, "select H where (C contains """&$E2&""")", 0
This was created with the format
...

**0**

votes

**1**answer

15 views

### Deleting rows that contain a keyword in the column

I have an excel file that has a id as one of the columns, each id has multiple entries and follow a set of survey questions. I need to delete all of the rows that have that keyword (which is in a ...

**2**

votes

**4**answers

11k views

### Excel 2010: Add text to each cell of a column

How can I add @gmail.com or something similar to a column of usernames without having to type this into each cell? I am using Excel 2010. I have a list of several hundred usernames that need to have ...

**1**

vote

**1**answer

23 views

### How do I allocate different text cells in a row a number and then sum total that row

I have a spreadsheet setup to monitor absence in the office. The columns are the days of the month 1 - 31 and rows are the month/employees
Mon Tue Wed Thu Fri
January 1 2 3 4
...

**1**

vote

**1**answer

3k views

### Automatic copy rows with matched column to other worksheet

Now I have a worksheet named sheet1 like the left side of image
I want to automatic copy all rows of sheet1 that have the column C = NO to worksheet sheet2 like the right side of image. And each ...

**13**

votes

**5**answers

44k views

### How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C.
In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.
How can I do so I don't have to type in the new formula in ...

**0**

votes

**1**answer

20 views

### How to automatically delete rows that contain certain numbers in the ID column

I have a column of numbers in another workbook and I would like to use that set of numbers to delete rows in the main workbook. I will give an simplified example of what I'm talking about below. My ...

**0**

votes

**1**answer

2k views

### Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2). My problem is the percent change from -4 to +4. A change ...

**0**

votes

**1**answer

23 views

### Excel Formula Issue Countif to Sumproduct

Having a nightmare at the moment with some formula's I've been asked to 'fix'. It would seem the users expected them to work with closed source spreadsheets but they don't. So they end up having to ...

**-1**

votes

**0**answers

22 views

### comparing 2 columns in excel and return the same result in a group

hi m new in excel function,
i have a problem like this. i have 2 lists of data
DATA I
NAME SCORE
----- -----
ANA 10
BUDI 20
TINA 30
DATA II
NAME SCORE
----- ...

**0**

votes

**1**answer

21 views

### Embedded IF(OR(IF) statement

I have made a formula that basically looks for a string of text inside a cell and if it finds it puts it in another cell. I needed to check three cells so I did three separate formulas (because I'm ...

**-1**

votes

**1**answer

23 views

### Excel formula: identifying highest weights

I need some help with the following assignment:
Task:
In the product Weight tab, you can see each product is weighted with a value per customer. Please create a Product Code for each customer based ...

**0**

votes

**0**answers

9 views

### summing values associated with names in multiple columns

I'm trying to sum up total values with names. There are 8 total columns rotating between a list of names and then values. I believe a sum if function would work, but I'm too novice to find the right ...

**2**

votes

**4**answers

8k views

### How to add/subtract months from given date in Excel

Here is my scenario:
I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each ...

**0**

votes

**1**answer

20 views

### Excel 2010 Formula of 3 variables with 3 results

I haven't been able to figure out how to combine these logic & date formulas in 1 cell.
My parameters would be:
A2 will always have a manually entered date in it
B2 could have one of three ...

**0**

votes

**1**answer

25 views

### Excel: portable formula to reference data on previous (relative) worksheet

I need to create a fairly simple Excel spreadsheet (workbook). I need 12 sheets (tabs), one for each month.
Each sheet references cells in the previous sheet. For example, the "May" sheet is ...