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

**0**

votes

**1**answer

24 views

### Return Non Null Cookies And Count

I have two worksheets in my workbook. The first worksheet is titled SalesPerson and should show a rundown of the salesman that is entered in cell B1 and the type & quantity of cookies sold. ...

**-1**

votes

**0**answers

5 views

### Conditional format — copy format of another cell (Google Sheets)

I'm using Google Sheets spreadsheet, hope it's ok to ask about it here (in superuser). Chances are the methods to accomplish this might be different from Excel or LibreOffice, if it can be done at ...

**-1**

votes

**0**answers

7 views

### Google sheets: Automatically including new rows on separate sheet

I have a google sheet document with two separate sheets, Sheet1 and Sheet2. I want column A on both sheets to stay the same. Column A of S2 simply has ='Sheet1'!A2 for cells A2 on down. Let's call ...

**3**

votes

**0**answers

25 views

### Sorting data without mixing rows

I have three sheets. First sheet renamed General:
Dave Henderson 21/05/2014
Elton Rus 22/06/2014
John Smith 20/05/2015
Second sheet renamed ...

**0**

votes

**0**answers

3 views

### SharePoint data connection to Excel - “refresh all” turns all formula results to zero

I have a SharePoint 2013 list that is connected to an Excel spreadsheet. Data comes into the Excel sheet on the first tab.
I have formulas on the second Excel tab (specifically, SUMIFS-based ...

**0**

votes

**1**answer

26 views

### Excel search for substring and display count

This is an extended question from this one.
Suppose now I have a following table with strings:
A
1 CHI111
2 SEA222
3 SEA333
4 LA444
...
I also plan to have a table to keep count of ...

**-2**

votes

**1**answer

27 views

### VBA Macro help needed [on hold]

I have two files , each file i have a column called subsystems. i need to check if my file A subsystems are there in File B.. this i can do with the help of Vlookup, but the in each single cell i have ...

**0**

votes

**1**answer

25 views

### Trying to create a SUMIF or SUMPRODUCT with one positive criteria and one negative criteria

I need to perform a SUMIF, SUMPRODUCT, or similar formula with TWO criteria, one a positive match and the other a negative match. Refer to the very shady table below:
A........| B.............| ...

**0**

votes

**1**answer

17 views

### Excel - Reference cells with same value and SUM

The excel data sheet I have takes this form:
XGTI, 10
XGTI, 12
BIOC, 5
BIOC, 5
BIOC, 1
BIOC, 10
XGTI, 5
BIOC, 1
MGT, 2
MGT, 4
I would like to total all the values separately from each other.
e.g.
...

**0**

votes

**1**answer

16 views

### Excel search for string in multiple cells, compare and display string with most counts

Excel 2013
I have a table as follow:
A
1 CHI
2 SEA
3 SEA
4 LA
...
How do I write a formula that can check cells A1 ~ A4 and display on another cell the string with most count? In this ...

**0**

votes

**1**answer

22 views

### Excel Formula that autofill an array with a list, based on an array

I'm trying to do a formula that within a range makes a list based on info on a specific array.
What I need is an array that aufills this list based on the following conditions:
1)It has to be the ...

**0**

votes

**1**answer

25 views

### How Can I make an excel column of Hyperlinks open in NON default browser

I have a ms excel 2007 worksheet with a column (A) of hyperlinks using the excel function in this way =HYPERLINK(A2,"url".
How Can I make (if this is possible) open all cels on that column in the NON ...

**1**

vote

**1**answer

25 views

### SUMIF based on multiple criteria

I want to get the sum of a range, but I want to change how the sum us calculated based on critera.
Say I have a table as follows
Name | Value
DP | 2
DP + BC | 4
DP | 4
DP + BC | 6
So ...

**1**

vote

**3**answers

4k views

### Display multiple cells if data on another workbook contains “X”value

I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 ...

**1**

vote

**3**answers

71 views

### Excel - How to return the first number only string that is between 7-10 characters long in a large body of text? (preferably Formulas only)

I'm trying to extract some numbers from a few cells that each have a large body of text.
The number strings are accompanied by certain words that precede the number string I'm trying to extract.
...

**0**

votes

**0**answers

36 views

### Excel 2010 - Combine specific sheet in >200 separate files into single worksheet?

I have tried solutions in other posts like this and so far nothing has worked :/
I have >200 separate excel files, and essentially I would like a specific sheet from each of these files combined into ...

**4**

votes

**2**answers

125 views

### Sort by Formula (without VBA)

I am attempting to sort a short column of values (descending) by using a formula in a different column. So if A1 through A10 contain numbers, in B1 I enter:
=LARGE($A$1:$A$10,ROWS($1:1))
and copy ...

**0**

votes

**1**answer

36 views

### Name in output column should be present only active member is “A” Else should check next row also now cell should be empty or Zero

I Have two workbook one have data as shown in image Sheet 1
and second workbook is output sheet2
The problem is I want data in output sheet from sheet 1 only if active column in sheet 1 Have " A" ...

**0**

votes

**1**answer

61 views

### Excel formula to find lowest value above a given number and return the column name

I'm trying to write an Excel formula that will look at 5 columns of data (for example, labeled SEL1, SEL2, SEL3, SEL4, and SEL5) and return the column name of the lowest column with a value above a ...

**0**

votes

**2**answers

3k views

### Lookup Multiple Results for Multiple Criteria

I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs ...

**2**

votes

**2**answers

208 views

### Excel vlookup reference purposely missing shows #N/A - stop it

I have a cell which does a vlookup.
But the table to which it refers is always changing and when the specific value is there is shows fine.
But when the value isn't there it shows #N/A - how can I ...

**0**

votes

**1**answer

27 views

### Formula to reference a cell value that contains text and retain the cell refences formatting

I have a bunch of cells containing text. The text in those cells have multiple font sizes and styles. I need to replicated those cells in a different order further down the worksheet and if I make ...

**-1**

votes

**2**answers

23 views

### Sum with multiple criteria and vlookup from another table

In the table below, I want to sum all of New York ACT in Q2 after the sales have been multiplied by the corresponding rates. Is there a one-shot formula I can use without needing to multiply all the ...

**1**

vote

**1**answer

20 views

### Calculating percentage change over a range of 5 cells where a different two will be populated on each row

I have 5 columns for 5 consecutive years.
I need to calculate the percentage change between the first value present across the column range and the second value present. The other three columns will ...

**-1**

votes

**1**answer

28 views

### advanced if excel formula

I want to write an excel formula that calculates cell x divided by cell y, but if the resulting value is between 0 and -.3 return a value of 0, but if less than.-.3 then return the inverse square of ...

**1**

vote

**1**answer

3k views

### VBA: How to replace entire Excel cell value basing on its content?

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell.
For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", ...

**0**

votes

**2**answers

7k views

### Count from a certain cell until a blank cell

I'm trying to create a dynamic formula which counts all the rows from cell D3 downwards until a blank cell is reached. I want to be able to add new data everyday and have the range automatically ...

**2**

votes

**4**answers

177k views

### How to Merge Data From Multiple Excel Files into a Single Excel File or Access Database?

I have a few dozen excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 master file which must have just 2 of the 4 worksheets. The ...

**0**

votes

**1**answer

31 views

### Excel - sum multiple items with multiple criteria

Given the following table, let's say I want to sum the New York "ACT" sales in Q2. The answer would be 59. How do I do that with a formula?
table image here

**-3**

votes

**0**answers

20 views

### Need a chart type to display turn around time for code requests received during the month [closed]

I need to display on the dashboard the following details every month need some idea on how to plot the data and use the best fit chart for this data
Data to report - Number of EAP and EDG requests ...

**0**

votes

**1**answer

136 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

3k views

### Defining Variables in Excel

I have two lists of data, x and y, one in a row one in a column. I would like to populate a chart of rows and columns with a complex formula that is the same for each chart point, the only difference ...

**0**

votes

**1**answer

23 views

### Sum of specific cells, up to a given amount

I am attempting to create an overflow spreadsheet using formulas. The "Starting Petty Cash" (on the right) is the carryover from the previous term, and is manually entered. The "Count", just above ...

**0**

votes

**1**answer

36 views

### Data validation to give text a value that's used in a formula elsewhere on the sheet

We have a group planning to charter a sailboat as a vacation for 10 days. I'm trying to show the total costs of the trip. It's a simple spreadsheet listing many semi-fixed costs like food, beer, ...

**0**

votes

**1**answer

20 views

### Use SUBSTITUTE formula to remove text from string in Excel, with text to remove being referenced from another cell

I have the following sheet setup
Column A - Brand Names
Column B - Product Names
The product names are structured as:
Nike Air Force White
Addidas Trainers Black
I want to remove the brand name ...

**0**

votes

**1**answer

3k views

### Updating another Excel spreadsheet from current spreadsheet

I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...

**0**

votes

**3**answers

5k views

### Excel sheet becomes very slow

I am working on an Excel 2007 workbook (less than 1gb in size). There is one sheet which has formulae linked to other two sheets in the same workbook. The problem is that it becomes really slow ...

**0**

votes

**1**answer

11 views

### If statement with indirect problems

My sheet is set up with I1 as a drop box for other tabs.
A1 and B1 Formula works and pulls the info from the cell in the tab if there's info, with slight changes in formula for cells but you can get ...

**0**

votes

**2**answers

22 views

### Formula for excel (return largest number for subtraction of different cells and which cells were used for answer)

I'm trying to return the largest number for the difference of different cells - ie, return largest number after computing a1-b1, a1-c1, b1-c1. Assuming
a1=3
b1=2
c1=1
then number returned is 2 ...

**0**

votes

**4**answers

3k views

### Create a hyperlink from URL and title in Excel

I'm trying to construct a hyperlink from a URL and a name in different cells.
a1 = http://www.example.com/
b1 = example
c1 = <a href="http://www.example.com/">example</a>
What I want ...

**0**

votes

**1**answer

21 views

### Count the amount of times a value appears combined with a certain event

I basically have the following list:
How can I count the amount of times John won?
I have tried using both countif and countifs but have find no positive result. I either get "#VALUE!" or a simple ...

**6**

votes

**1**answer

2k views

### Conditional formatting to highlight the whole row

I would like to highlight the whole row 5 if J5="T".
I used to do it with the old Excel, the 2010 conditional formatting ignores it. So my rule is like this and it does not work. I used the formula ...

**11**

votes

**5**answers

51k views

### Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...

**2**

votes

**1**answer

42 views

### Excel : How to output a list of data that satisfies a range?

I have a list of numbers (single column), A,
and two values B and C which are calculated from the list of data, A.
I would like to output all data A in the same sheet (but in a different column) that ...

**0**

votes

**2**answers

3k views

### Excel: AVERAGEIF on multiple work sheets (via 3D Reference)

I have a question when using excel averageif functions:
I have a workbook containing monthly information (stored as numerical values) in 12 different worksheets (named Jan, Feb,..., Dec). In each ...

**-1**

votes

**0**answers

35 views

### Need Excel formula to Match text data, and return ALL column header(s) text data is under

I want a function to match data in column K (a list of single words) to the same in table A2:G90, then return column header(s) A1-G1 in column L. Some words are listed in more than one column. I need ...

**0**

votes

**1**answer

19 views

### index and match 2 criteria with COUNTIF

I know many questions have been asked for using INDEX and MATCH with criteria and returning a unique list without duplicates. And I have that much working. But I ran into a problem.
Here is my data:
...

**0**

votes

**1**answer

9 views

### Ability to perform lookup in a different file based on dropdown

I want to perform a lookup in a different file based on a drop-down list.
Twist - I want the drop-down selection to create the reference to the other file. So if I selected May 2016 from the ...

**0**

votes

**1**answer

34 views

### Run formula on cell click

I'm trying to run a macro that runs the following formula when a cell is clicked and populates it with the rounded figure:
= Round((A1 + "0:02") * 96, 0) / 96
This takes the time from A1 and ...

**0**

votes

**2**answers

21 views

### Excel sum if based on multiple criteria

I need to sum values in one column where two other columns meet certain criteria.
Column A has the item name
Column F has its value
Column G has the date it was bought
What I need to do is get the ...