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

**1**

vote

**1**answer

228 views

### Is there a way to do a partial text vlookup?

I am trying to vlookup partial text which will return a specific value if found within my data. I have a list of postcodes (zipcodes) and would like a vlookup to look up the first part of the postcode ...

**0**

votes

**1**answer

33 views

### how to get the count of column data based on some condition

I have the excel with two columns like below
ID Address
GID1 address1
GID2 address2
GID1 address1
GID8 address1
GID2 address8
so i want to get , for all same ID's , count ...

**0**

votes

**1**answer

516 views

### Sort & Ranking Functions in Excel for Time AND Number Values in one Column

I have a spreadsheet that is being used to track and score a fitness competition, each workout has a time cap, so athletes that complete the workout under the time cut off are given a time as their ...

**1**

vote

**1**answer

83 views

### Excell formula for multiple pasts without repeating previous cells

I have a data set for 12 years of 4,380 days. I want to take the sum of each week. when I make a formula {=SUM(A1:A7)} and drag to drop button to the next cell it shows {=SUM(A2:A8)}. This is not what ...

**-1**

votes

**2**answers

235 views

### From date of last event, calculate number of days until next regularly scheduled event

I need to calculate the number of days since last occurrence.
For example, I have to go to doctors every 90 days. My last doctor visit was on 1/9/14. I need a formula to tell me how many days until ...

**0**

votes

**1**answer

65 views

### Count Unique values based on criteria within 2 columns

I hope someone out there has the brains to help me with issue.
I have a spreadsheet that has multiple columns and duplicates. What i want to do is count all the unique values in column A, only if ...

**0**

votes

**1**answer

141 views

### Make formula impossible to find / read

I have developed a spreadsheet for performing various calculations which our employees will complete and then distribute to clients / competitors. To protect the "intellectual property" of the ...

**0**

votes

**2**answers

72 views

### Converting comma seperated values in multiple rows

I am in urgent need of importing huge data from excel to SQL.
one of the excel sheet contains data as follows.
ID Name Category Section
1 Books Stationery KG1,KG2,1
2 Pencils ...

**0**

votes

**0**answers

60 views

### Search rows containing a certain value for other values and comparing them

Here is what I'm trying to do:
This spreadsheet contains 40k+ component serial numbers. These assembly serial numbers are in column c.
These components contain sub assemblies and those also have ...

**0**

votes

**1**answer

43 views

### Formula to determine available options based on prerequisites

Example:
A spreadsheet for a single studient has four columns: Course|Completed?|Prerequisites|Available
The "Course" column lists the names of courses.
The "Completed?" column indicates a "yes" if ...

**0**

votes

**1**answer

161 views

### Excel - Find a specific column in a CSV file, and copy to the current sheet

I get a regular data export in a CSV format, which I like to pull into excel and report on via helper columns and pivot tables.
I do this by linking to the csv file, and copying the cell contents ...

**0**

votes

**0**answers

39 views

### In Excel 2013 how can you make every distinct number value have a matching distinct colour?

I have a table of computed numbers in excel (different every time it is computed), for example:
A B C D E
1. 9 8 9 9 1
2. 7 1 1 1 9
3. 1 6 5 2 9
4. 6 6 3 2 2
5. 3 3 2 3 7
In the example I have ...

**0**

votes

**0**answers

50 views

### I need to import data to an excel sheet

I originally went through a spreadsheet and created a new column called regions next to an existing column that had suburb data in there. It took me ages as there was 8000 rows of data. Anyway its ...

**0**

votes

**0**answers

18 views

### How to embed an icon from one worksheet to another, within the same workbook

Does anyone know how to embed an icon referencing another worksheet page onto another worksheet ... all within the same workbook. I have been trying this out but keep getting information on how to ...

**0**

votes

**1**answer

57 views

### Can I use a worksheet as a function in Excel?

I have a worksheet set up such that I can input a value, do some math to give me a bunch of values, repeat math a few times, and add up all the values and return an output value. It is complex enough ...

**0**

votes

**2**answers

48 views

### Auto numbering based on repeating cells

I need help auto numbering a spreadsheet that has submissions with inventors but some submissions have multiple people listed, but need to be numbered the same.
e.g.:
No. Count Name
0011 ...

**0**

votes

**3**answers

53 views

### Can't work out the logic for formula

I have two cells and I want to write a formula to check two conditions and return "Error" if either condition is met. The conditions are;
1.Are both the cells empty?
2.Are both cells NOT empty?
(It ...

**1**

vote

**1**answer

44 views

### Compare data cells from two independent file

I would like to compare the cells values from two different Excel files.
I have already done a program which is working if it's on the same sheet. Now, I would like to "external" the first cell.
The ...

**0**

votes

**1**answer

186 views

### Copy a specific cell on all sheets to master sheet

I have a master sheet with the format as below:
Master sheet
Column A Column B Column C
No Description Price
And multiple sheets with the format below:
Sheet ...

**3**

votes

**3**answers

964 views

### Convert a formula in Excel into an Array Formula without Ctrl-Shift-Enter?

Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?
Normal Formula
Array Formula

**0**

votes

**1**answer

172 views

### Match first and last name to 2nd column then output corresponding 3rd value

I'm trying to create an import csv. the sheet has the following data
ImportID, 1stname, lastname, FirstName, LastName, inport_ID
1, bob, Abraham, bob, Abbott,
2, sue, Abraham, bob, Abraham, 1
3, ...

**0**

votes

**3**answers

2k views

### EXCEL - How to have a formula output and user input on the same cell?

I would like help from someone as I believe this problem requires VB. I basically have a CELL(A4) where people will be entering values to find out the corresponding calculations displayed below.
...

**1**

vote

**1**answer

116 views

### In Excel, how do I replace all instances of text in a cell if any of the text matches values from a range of cells)

To make things easy for users of this workbook, I've setup column J with data validation (pulling values from Sheet2) that allows for multiple selections of event types (Academic Calendar, Art ...

**0**

votes

**2**answers

48 views

### Indexing a sum of values by row from multiple columns

I'm struggling with this one...
In row 2 I have week numbers up to week 9 in this example. Row 1 has a formula that determines to which month a week belongs. In this example, week 5 is in month 1 ...

**1**

vote

**2**answers

200 views

### Perform a calculation n times and sum the results (without VBA)

I'm looking for a way to sum the result of a calculation performed a given, arbitrarily large, number of times.
Suppose I have 15,000 customers each of whom will spend between $100 and $200, and I ...

**0**

votes

**1**answer

44 views

### Formula to incorporate Highlighted text & Loop function

I'm trying to analyze over a 1000 line items,
with different information in all columns ,although certain columns might have duplicate information.
I can do conditional formatting and highlight the ...

**0**

votes

**2**answers

79 views

### Drag the cell to create an array of Roman numbers

I need to create an array of cells of roman numbers. So I placed my entry like this
cell A1: I
cell A2: II
cell A3: III
cell A4: IV
I selected the entries and dragged the corner down, hoping that ...

**0**

votes

**1**answer

31 views

### Microsoft Excel 2013 on Windows 7, creating cost based on drop down values

Hi I am need of some assistance and was wondering if anyone can help me. I am creating a spreadsheet for quoting jobs on site and am stuck with a particular part.
I have a cell containing a drop down ...

**1**

vote

**2**answers

716 views

### Look up a value in a list and return ALL multiple corresponding values [closed]

The basic answer to my question is provided here: How to look up a value in a list and return multiple corresponding values. However, once the formula is entered, I need to drag down, or copy/paste ...

**-2**

votes

**1**answer

38 views

### Need an “IF” function that shows whether the difference of a simple subtraction is greater than a certain value

I have values in cells A3 and A2. I will be subtracting the value of A2 from A3. What I need is to be able to highlight or flag the result if the difference is greater than an absolute value of .01 ...

**1**

vote

**2**answers

58 views

### Find largest number in one list and smallest number in another list to index one cell

![enter image description here][1]Is there a way to identify the highest number in a list and the lowest number in a list to index to a cell?
I have a list of players, each player has total number of ...

**0**

votes

**1**answer

528 views

### Spreadsheets Formula Check In/CheckOut Autopopulate Data

I am working on a spreadsheet (I am doing it in Numbers since it will easily let me work on the file on my mac, iPhone, iPad and online at icloud.com)... but this is essentially also an excel question ...

**1**

vote

**2**answers

813 views

### Auto-fill rows from columns in another sheet?

I'm using Google Doc's Spreadsheet at the moment and I'm attempting to create the same view in another sheet.
First Sheet:
(yes I need these merged cells)
Second Sheet:
Unfortunately Google ...

**1**

vote

**1**answer

160 views

### How do you auto update row count in a formula that yields a percentage?

How does one get a formula to auto update when a row is added when this formula is meant to yield as a percentage, which is directly linked to a pie chart? For example: my formula reads
...

**1**

vote

**2**answers

2k views

### Excel- How do I find common text values from multiple (>2) columns?

I have 9 columns of text data and need to know the common terms that are found in all nine columns (or possibly different combinations of columns). I can do 2 columns ...

**0**

votes

**2**answers

91 views

### How to use SUMIFS function for summing values if criteria range exists in a range?

My data:
A B C
a 3 d
b 1 a
c 8 e
d 5
I want to use SUMIFS function, sum range is B1:B4, if the corresponding value in column A exists in column C, sum the values in column ...

**0**

votes

**1**answer

38 views

### Ignoring value IF “[Value_if_false]”

I am trying to figure out the average difference in monthly sales versus previous years, and I have an IF function within an Average function. I want to skip zero values in the average function if the ...

**2**

votes

**1**answer

73 views

### Can I use table relationships in formulas?

Are table relationships only for pivot tables & charts, or can they be used in formulas too? The introduction of tables made it easy to write really readable sumifs like this:
...

**0**

votes

**2**answers

55 views

### Excel SUMIF Advice

I was hoping you might be able to help. I'm collected data in excel from a data source. The data source has 3 headings
Active,Occurrence,Time (seconds)
TRUE, 1, 0
TRUE, 1, 12
FALSE, 1, 12
...

**1**

vote

**1**answer

68 views

### Formatting cells in EXCEL based on real world date

I am trying to format cells in EXCEL 2007 based on the real world date for due dates on my bills. I know how to do this using Open Office, however EXCEL does not use the same conditional formatting ...

**2**

votes

**2**answers

45 views

### I would like to copy data from 1 row to another in Excel

I am trying to solve the following issue in excel: I am working in two sheets, Sheet 1 and Sheet 2. There is a column of data in sheet 1 (say from A5 to A20). I need every row from that column to be ...

**0**

votes

**1**answer

30 views

### Excel 2013. Address from Value

I have two spreadsheets, one with a Calendar, and the other one with Data Chart.
I'm trying to make it so that the Data Chart will only start using Data from Today's date in the Calendar. (Note: ...

**2**

votes

**1**answer

92 views

### What does “--” used in Excel function mean?

I came across this reference in a spreadsheet I'm looking at and don't understand what it means.
=SUMPRODUCT(--NOT((ISBLANK(CONSTRUCT))))
Can anyone tell me what the -- in front of NOT refers to?
...

**0**

votes

**1**answer

39 views

### Excel 2010 Cell Formatting

I have a crew schedule made up in excel. There are 4 different shifts A B C or D. There are 5 people on the schedule, and the schedule is laid out like this in excel:
Each person works the same ...

**2**

votes

**3**answers

81 views

### Problems figuring out weekly scheduling and hours past midnight

This is a screenshot of what I have
What I'm trying to do is have the hours for each individual person posted as shown or a way that the hours can be posted like this and then have the total amount ...

**0**

votes

**1**answer

2k views

### Make the background/text colour follow another cells background or text colour

Ok so I have a large spreadsheet (so I don't want to have to set each cells colour individually or manually) and I have many cells copying other cells. So for instance I have cells N9, N
3, N80, N117 ...

**0**

votes

**0**answers

47 views

### 2 EXCEL (array?) questions

I am trying to perform some lookup calculations on a pivot table and having some abysmal luck finding the correct function/formulas to use. I've searched the Interwebs and can't find something that ...

**1**

vote

**1**answer

34 views

### Excel Deleting duplicate entries

I want to delete duplicate entries in the following table:
The problem is that I only want to delete the duplicates in the left hand column, and the right hand column entries must stay with their ...

**0**

votes

**2**answers

71 views

### OOO: Increment sheets for fill down

I have a row of cells on "sheet1" referencing a set of cells on a different sheet. I want to fill down identical sets of cells while incrementing only the sheet.
Another way to describe the problem: ...

**-1**

votes

**1**answer

85 views

### In Excel 2013 Is there a way to code an auto-complete function ibased on values in another tab

I want to create an auto complete cell in Excel for the resource name. The list is too long for a custom list and I really do not want a drop down list as in data validation. Does someone have an ...