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

**0**

votes

**1**answer

8 views

### Excel help to lock formulas from adjust on cell insert

I have a excel workbook where there is a hidden sheet that basically pulls data from other sheets in the same workbook just using the = formula.
The problem I am having is if I add a column or cells ...

**1**

vote

**1**answer

9 views

### Use IRR function with discontinuous cells in EXCEL

Is there a way to use a range of cells that are not next to each other with the IRR function?
I'd like to use IRR this way:
=IRR(A1:A3 AND B2) meaning read A1, A2, A3, B2 the same way you would ...

**-1**

votes

**0**answers

19 views

### 2 Excel Tabs: Find matching values in column As and match the B columns based on A. (Only one tab has B filled)

I have two tabs in excel; one working with technologies and one with patents on those technologies. The technology tab has each tech ID in column A, and the patent tab has the same tech ID numbers but ...

**1**

vote

**1**answer

13 views

### Using OR condition in SUMPRODUCT

I have three columns of data - a sum column (A) and then the two columns that form the sum (B and C). Sometimes, just one of the B and C columns is filled in; occasionally the B and C columns are both ...

**1**

vote

**2**answers

27 views

### add same value to range of cells in microsoft excel

I want to add the same word from cell B1 to B40000
I do not want to use the click and drag box method to apply it across multiple cells/rows.
I know I have to type in the formula bar B1:B40000 but I ...

**0**

votes

**1**answer

11 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

55 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**

votes

**0**answers

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

**0**

votes

**1**answer

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

**0**

votes

**0**answers

28 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

**3**answers

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

**1**

vote

**2**answers

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

**2**

votes

**4**answers

58 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

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

**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

**1**answer

22 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

**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, ...

**1**

vote

**1**answer

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

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

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

**0**

votes

**1**answer

25 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

17 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: 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 ...

**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

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

**1**answer

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

**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

21 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

26 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

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

26 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

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

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

**1**

vote

**1**answer

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

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

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

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

**0**

votes

**1**answer

22 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

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

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

**0**

votes

**0**answers

16 views

### Convert COUNTIFS formula to SUM(IF) to enable external workbook references?

I need to convert the following formula to one that uses SUM(IF), as I am given a #value error when the external data source is closed.
...

**2**

votes

**1**answer

42 views

### Replace cell references with values without F9 using macro

This is an Excel macro question. Given a series of formulas such as the following:
= A1 + A2
= B2 + B2
etc., ...
I would like to replace each of these cell references with the values without ...

**0**

votes

**1**answer

31 views

### How to I use SUMIF or SUMIFS to automatically add the values of different cells when they meet they meet multiple criteria

Basically I have a spreadsheet with three criteria columns.
I would like to be able to recognize duplicate HTS codes that also have the same country of origin and add the sum of those values, which ...

**0**

votes

**1**answer

49 views

### How do I merge data from other excel files to one master excel worksheet?

I have an uniform ID system throughout all the files, except with multiple entries and differing data named columns. I would like to create a master excel sheet with specific data columns from each ...

**0**

votes

**1**answer

37 views

### Hide columns based on content (sum of rows)

I'm trying to hide columns based on the sum of selected rows (i.e., cells) in those columns.
For example - if the sum of E9:E12 is 0, then hide column E.
Additionally, and probably a little harder - ...

**0**

votes

**2**answers

43 views

### Selectively find duplicates across columns

Column A has the product ID. There are 102 other columns with other data. Column A will be unique unless some data was changed. In that case, there will be 2 rows with 99% of the columns are ...

**0**

votes

**1**answer

17 views

### excel - inserting a value based on a range of values

I need to link a batch code to a specific unit number. The batch codes are the same for a range of unit numbers. The first table below shows the unit numbers; the “?” marks are where I need to enter ...