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

**2**

votes

**0**answers

95 views

### How to output a CSV file from a matrix of Excel formulas using multiple files?

I am receiving on a weekly basis a list of Excel spreadsheets from a supplier (around 40 spreadsheets) with data in a specific format. Since the format is a bit complex and can't be converted to CSV ...

**2**

votes

**0**answers

32 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

2k views

### Convert Array to String with Excel Formula, is it possible?

Convert Array to String with Excel Formula, is it possible?
I know how this would be done with VBA, but I have to share my workbook, and I have to assume that other people will not be able to enable ...

**2**

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

320 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

11 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

11 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

33 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

16 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

45 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

19 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

47 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

36 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

37 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

22 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

86 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

45 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

61 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

33 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

122 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

20 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

40 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

227 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

53 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

50 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

157 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

170 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

91 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

57 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

43 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

170 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

3k views

### How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells.
i.e. the conditional format changes the background colour of some cells if they are negative.
I ...

**1**

vote

**0**answers

215 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

282 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

400 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

8 views

### Display the last value in a data sheet of duplicates

I have a list of duplicate values and I need to display the last value in the data sheet.
In the example below I need to display that the code ER16 has a qtyleft (quantity left) of 4

**0**

votes

**0**answers

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

**0**

votes

**0**answers

16 views

### Create Round Robin Crosstable With Excel

I have a very large crosstable (120x120 at least) in Excel. The data is contains win or loss info. So, all data on row 5 and column E (which I label as column 5) contains data on the games the person ...

**0**

votes

**0**answers

17 views

### How to automatically populate worksheets?

Using excel 2016 i want to be able to be able to populate all of my worksheets automatically daily from one master worksheet with out having to enter the same repetitive data.

**0**

votes

**0**answers

12 views

### Auto Update data from one column in Excel file A to another column in Excel file B

I have 2 excel files for example myemployee.xls and salary.xls.
In myemployee.xls, I have two columns: employees code and names like this and new employees will be entered here continously.
A ...

**0**

votes

**0**answers

17 views

### How to use cell location from another worksheet

I have worksheet "Transactions". In row 2, I have a value of "SOLD". It will always be in Row 2, but the column floats due to adding or deleting columns.
In worksheet "Income Tracker", I want to ...

**0**

votes

**0**answers

9 views

### Where can I find documentation of the syntax for creating user-defined functions in NeoOffice Calc?

I'm trying (for the first time) to create a user-defined function in NeoOffice Calc. Unfortunately, I cannot find a reference source explaining what programming syntax should be used in the "Basic ...

**0**

votes

**0**answers

20 views

### How to make 2 Excel sheets read each other and combined into 1 excel sheet

I have 2 Excel Sheets with gas card numbers and date stamps on 1 and another with date stamps and driver names. I need to know if there is a way to have the first sheet read the second and combined ...

**0**

votes

**0**answers

25 views

### Select specific cells in Excel

I have an Excel 2010 spreadsheet with thousands of rows and columns. I want to select specific cells from it. Is there any way to select:
every 7th cell in a column
every 7th cell but restart the ...

**0**

votes

**0**answers

10 views

### How to reconcile various fields in excel from different worksheets

I ran into a problem in trying to reconcile some data.
(Worksheet A)
Date
Reference No.(1)
Reference No. (2)
Amount Name
(Worksheet B)
Date Reference No.(1) Reference No. (2) Reference .No (3) ...

**0**

votes

**0**answers

14 views

### Formula in Excel which allows me to identify equal values in rows

I want to build a formula in Excel 2010 which allows me to identify EQUAL, GREATER or LESS values in rows.
if column A is greater than column B or C (put the word GREATER in column D)
if column A ...

**0**

votes

**0**answers

13 views

### Possible to have template folder that has referenced worksheets inside?

It's hard to explain what I'm trying to do, but I would like to create a folder template that would have a bunch of smaller Excel spreadsheets that are referenced to a larger, master copy that has a ...

**0**

votes

**0**answers

29 views

### Find text with multiple instances of character(s)

In this
you can see that there are two types of cells. One contains order with one pair or brackets and second one has two pairs of brackets.
For cell which has only one pair of brackets i use ...