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

**3**

votes

**0**answers

3k views

### Openoffice Calc - use cell font color in formula

I have some openoffice calc sheets where some rows are maked with
the font color red. All other rows are in the default font color.
Can I use the font color of a cell within a formula to react to ...

**2**

votes

**0**answers

37 views

### What function (or functions) can I use to pass worksheet title to a reference?

I have a number of cells on a worksheet that reference another worksheet. Pretty straight forward except I have to type the name of each worksheet in each formula which is becoming monotonous. What I ...

**2**

votes

**0**answers

399 views

### How to get new data to automatically slot into correct column in Excel?

I am trying to move new data into an existing database with set columns. The existing database has a column for every possible type of data, but not all new entries contain all columns. So I can't ...

**1**

vote

**0**answers

29 views

### Excel doesn't create structured formula for all tables

Structured formula case
I am facing 2 weird issues with Excel 2013. I have been mainly using the tables. Initially when I created the sheet and inserted tables and added formulas, they were all using ...

**1**

vote

**0**answers

11 views

### Plot a chart directly from a function in Microsoft Excel

I need an help with chart plotting in Excel (I'm using Excel 2016) let's say we have a simple function like:
y=1/x
Right now, to plot a chart based on this function I use two columns. In the left ...

**1**

vote

**0**answers

28 views

### Show Excel Cell Only If Another Has Data

I am trying to format the below Excel sheet so that when a date is entered into cell J1, cells G24 - G37 populate with a date a specific to what I need. I currently have the cells formatted as ...

**1**

vote

**0**answers

22 views

### Find and replace multiple values, according to table value - excel formula

I would like a FORMULA that change letters according to table of values that i make.
example:
êíöä = LOVE (because i made a table that show: ê=L, í=O, ö=V, ä=E).
example- Picture:
As you can see ...

**1**

vote

**0**answers

104 views

### VBA code to select by a cell value and date range to move data from one sheet to another

I am having trouble getting a VBA code that I have adapted for my program to run. There are things in it that I don't understand and I don't know how to fix.
I am trying to filter data from a "Sales" ...

**1**

vote

**0**answers

31 views

### Optimize row height/column width in Excel = minimize white space

I often create tables in Excel where some cells contain a lot of text while others just contain a single digit or some other short value. With wrap text in cells enabled and then a combination of ...

**1**

vote

**0**answers

22 views

### Aggregating Repeating Labels in Excel Sheet into Proper Table

I have an excel file with two columns: the first contains a repeating sequence of 14 labels, while the second contains the text values associated with those labels. What I'd like to do is aggregate ...

**1**

vote

**0**answers

84 views

### EXCEL formula variable pricing structure

Cell B3 equals a number of Item 1 purchased with a variable pricing structure.
I have a formula that works great and creates an accurate $ cost
...

**1**

vote

**0**answers

39 views

### Calculating averages for every n rows with missing data

I have a column with 30-min averages, and I'd link to make a new column with 24-hour averages. I found this formula very useful:
...

**1**

vote

**0**answers

53 views

### Why doesn't Excel auto-adjust formula when a row is moved?

I have a simple spreadsheet with just one formula, which auto-increments the number in the first column, going from cell A6 down. So the value in A6 is hard-coded 1, then A7 contains the formula ...

**1**

vote

**0**answers

20 views

### Cell auto adjust height when pulling data from other cells?

I've built a spreadsheet with a summary report that auto formats fine, except in situations where the text in one cell and is too long to fit into a cell.
The report pulls info from cells under ...

**1**

vote

**0**answers

12 views

### Removing duplicate values in a cell

I found a great post answering ALMOST what I need.
I want to list values as such; (so that the true line is the only one I want)
Product A Cyan Cyan FALSE
Product A ...

**1**

vote

**0**answers

40 views

### Excel - Can I create a list of values with criteria and have excel make a list with parameters?

In Excel, I am trying to see how many combinations I can make with the allotted criteria and such.
So lets say I want to know how many combinations of groceries I want to buy for a certain budget. ...

**1**

vote

**0**answers

65 views

### 10 minutes to calculate the very simplest excel formula - why?

Well I know partly why, my spreadsheet has about 180,000 rows. But that has not presented a problem for sorting or filtering.
But when I entered =B2 in cell C2 and stretched it down to the bottom, it ...

**1**

vote

**0**answers

24 views

### Control Number Generation

So currently I have the following. When I click a button or hit submit button The form looks on the Ledger Tab. I was trying to get the form to automatically generate a control number (ConNum) that ...

**1**

vote

**0**answers

18 views

### I am trying to turn named ranges into named tables while keeping Auto populating headers

I have a spreadsheet with 32 individual named ranges and the Section Title (B338 for example) is linked to the equivalent value in the Table of Contents (B70 for example). What I would like to do is ...

**1**

vote

**0**answers

57 views

### How can I ensure that a formula that I have written to calculate the difference between time values applies only to the filtered values?

I have two columns of data:
Member Time
A 09/24/2015 09:48:36
A 09/24/2015 09:47:40
A 09/24/2015 10:11:06
A 09/24/2015 10:08:47
B 09/24/2015 10:28:45
B ...

**1**

vote

**0**answers

22 views

### Excel formula to convert self to value?

Is it possible for a formula in excel to return a value and assign it to itself, overwriting the formula?
Example: If column A consists of
=IF(LEN(B2)>0,"1","0")
On cell contents in B2, I want ...

**1**

vote

**0**answers

60 views

### Sum data from muliple cells, multiply by other cell range, reference on other sheet

Ive had a couple issues with this workbook recently this seems like the biggest. As you can see in the pics, this a time sheet that needs F15 multiplied by Q15, then added to F20 which was multiplied ...

**1**

vote

**0**answers

88 views

### How can I make an excel user defined function (UDF) work in an array function (CSE)?

I am using Excel 2010.
I wrote a UDF to create a unique number for each month:
yearmonth = 12*Year(date) + Month(date)
Now I want to use it in a CSE:
...

**1**

vote

**0**answers

40 views

### Excel Formula to autopopulate a cell and mark as Assigned

I am attempting to auto populate a excel cell with an account number with the following formula.
=INDEX(C2:C20001,MATCH(FALSE,B2:B20001,0))
Column B contains True/False and Column C contains the ...

**1**

vote

**0**answers

25 views

### Track workflow precendents using only Excel Formulas

what I'm trying to achieve is the following. I have a production chain, say for example:
A and B are used to make C
C and D are used to make E
and E is used to make F
And I wanna know the direct ...

**1**

vote

**0**answers

141 views

### EXCEL 2011 - Inventory with Matrix and Vlookup

I am creating an inventory matrix, which has on the first column a list of all the items in the inventory, and on the first row a list of the dates for when the inventory gets renewed. Basically, not ...

**1**

vote

**0**answers

53 views

### Search in text for column of words and return the word that matches

I have two columns of data, the first (Column A) contains a whole load of text, the second (Column C) contains the "key" data.
I want to see if the text in Column A is matched by any of the entries ...

**1**

vote

**0**answers

110 views

### Excel lookup value not on same row

What i`m looking for is that the data will be filled automatically from data table by lookup value in not same row.
See the picture
or xls example https://dropmefiles.com/BDlHe
1st table lookup ...

**1**

vote

**0**answers

36 views

### Matching a column range based on a column label

I have written a spreadsheet for Excel 10 and am struggling with the last bit of formula.
I wrote this:
=SUMPRODUCT(--(ISNUMBER(FIND(B7,'PO SHEET'!$K$7:$K$74))),('PO SHEET'!$W$7:$W$74))
However, ...

**1**

vote

**0**answers

185 views

### excel formulas not working in structured table protected cells

Structured Table1=
A B C
QTY Price QTY*Price
1 3 3 =A*B
2
3
Column QTY & Price are not locked
while Column QTY*Price is locked & hidden as it has a ...

**1**

vote

**0**answers

23 views

### which function would add a row to another set of rows, so it behaves as if they were below each other?

Example: having data in A1:E4 and another data in G1:K1 and i need to use them to look like A1:E5 so that G1:K1 was added below A1:E4.
The reason behind this is that excel is asking to give set ...

**1**

vote

**0**answers

21 views

### Return cells with values in a column when we change the date on a different sheet and remove blank cells

I have one excel sheet which I normally record the expenses everyday. Column 1 will contain the money a person is given and Row 1 will contain the date of the month. What will keep on changing is the ...

**1**

vote

**0**answers

57 views

### How do I incorporate IFERROR into this formula?

I need to know how to include an IFERROR statement in this formula in Excel 2007.
=VLOOKUP($E3,HSA!$E$3:$G$20,2,FALSE)*$D3
I want the formula to return a 0 instead of an #N/A.

**1**

vote

**0**answers

309 views

### Auto propagation of formulas down the columns for each new row

How to propagate those formulas down the respective colums for every new row automatically? There is an webhook which creates new rows with data with those columns (A1, X2, Y2) empty, and I would need ...

**1**

vote

**0**answers

67 views

### What does the TABLE function do?

I got a the formula {=TABLE(C7,C5)}.
I cannot find any table name from ribbon Name Manager, so I think there is no table. So, what is this formula doing ?

**1**

vote

**0**answers

59 views

### Return an array from a lookup

I have a table with column A containing vendors and column B containing product codes. One vendor has many product codes.
In another place I need an array of product codes for a specific vendor. The ...

**1**

vote

**0**answers

174 views

### Using getpivot in excel to create flexible reports based on user inputs

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but ...

**1**

vote

**0**answers

188 views

### automatically transfer any data on excel spreadsheet to master spreadsheet

I work in a library and I am working on a workbook to track our orders from one company. I have three sheets where each sheet represents a different branch of our library. I am trying to have it set ...

**1**

vote

**0**answers

114 views

### Microsoft Excel hyperlink function to a specific location in a Word doc

I have a long column of numbers in Excel that I have mail merged to a Word doc. I would like to write a function in Excel that will hyperlink each cell from this column to its corresponding mail ...

**1**

vote

**0**answers

64 views

### How can I move columns based on the column header title to another workbook?

I have to average grades based on each objective for a new report card we have to complete this year. Column one has students names, each additional column will have the objective associated with the ...

**1**

vote

**0**answers

46 views

### Excel for Mac 2011 move column 2 in sheet 1 to column 1 in sheet 2

I would like to move column 2 into column 1 for each successive sheet, one sheet per week, automatically. Is that possible? I do it with this formula ='1st week'!D2 and manually changing '1st week' to ...

**1**

vote

**0**answers

205 views

### How to create a formula which finds a cell that matches a range of other cells then produces a number if the cell appears.

am having trouble with finding a suitable formula for my spreadsheet in excel.
In column A i have a list of postcodes (439 postcodes). In column B-BL i also have postcodes (these postcodes are in ...

**1**

vote

**0**answers

238 views

### Conditional Formatting a date/time comparison

The function I'm working on specifically compares & colors a daily recurring time range set in 2 different cells. A specific date in the target cells (produced by a formula) compare to that daily ...

**1**

vote

**0**answers

313 views

### How might I evenly assign arbitrary categories smoothly across a large population?

Does Excel have a feature or set of features that can be used to accomplish the opposite of sorting? So, rather than sorting rows so that similar values are contiguous, I want similar values optimally ...

**1**

vote

**0**answers

446 views

### What is the best way to create charts of “database-style” tables and data in Excel?

I'm a biologist and I frequently have to do analysis on relatively large (for Excel) datasets. Usually a few charts are needed to display the data, but creating them seems to require laying out out ...

**0**

votes

**0**answers

28 views

### Create an atypical number pattern in Excel

I would like to create a custom list in Excel that repeats a number pattern, that I can fill automatically like I do with other custom lists in the Advanced Options.
The pattern uses numerals 1-7 in ...

**0**

votes

**0**answers

4 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

**0**answers

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

**0**

votes

**0**answers

10 views

### Numbering cells in excel with relation to another cell

I have data of many rows with few blank rows/text rows in between. position of blank rows differ every time. I want to give one number (say 1) to all the rows before blank rows, next number (say 2) ...

**0**

votes

**0**answers

16 views

### Creating an Excel timetable

I have created course timetables for groups of students. i.e. a timetable on a sheet for each day, with the students' names across the top. What I would like to do now is to combine the five days into ...