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

**5**

votes

**0**answers

85 views

### Use functions like Countif, Countblank etc. to refer to discontiguous ranges.

Is there a way to use array formulas or some other feature to essentially have an excel function that means.
=Countblank({A1,A4:A6,A112:127,B29:B38}) etc.
Essentially, I want to refer to multiple ...

**2**

votes

**0**answers

82 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

29 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

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

293 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

15 views

### Change every character in a cell with another in Excel

I have a large list of phrases that I would like to make more disguised. I want to substitute each letter/character with another character and be able to decode this with another formula so I am able ...

**1**

vote

**0**answers

15 views

### Excel/Googlesheet: Anyway to include a comment in a referenced cell?

I have two sheets, the master sheet which has tables of data and a second sheet which references certain cells from the master sheet in order to collate certain data. I wanted to know if theres a way ...

**1**

vote

**0**answers

13 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

35 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

15 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

38 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

24 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

28 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

19 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

64 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

40 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

45 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

96 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

19 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

37 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

170 views

### ArrayFormula + Average giviing me div/o error

I have the formula on B2
if(D2="", "", AVERAGE(D2:AF2))
This gives me the average of all numbers in the row, and takes off the ugly error when there is no numbers. I tried spreading this formula to ...

**1**

vote

**0**answers

50 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

47 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

149 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

159 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

86 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

55 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

158 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

2k 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

194 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

268 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

373 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

1 view

### Create a hyperlink to a cell in a “Table of Contents” sheet quickly

I have an excel document with a vast amount of sheets (upwards of 100). To make navigation easier, I made the first sheet a "Table of Contents" where I listed all of the other sheet names in a column ...

**0**

votes

**0**answers

3 views

### Excel formula needs help to create table

Trying to understand how to create a table that identifies what specific dates relate to the row and column it is in.
IE. if I have columns A, B, and C. and rows 1, 2, 3 with dates applied within ...

**0**

votes

**0**answers

6 views

### The data(Job #) in one column is only put to another spread sheet, IF another column contains data

J/C # A. B. C. D. E. F. G. H. TOTAL Estimated Time
97647 45 45 90 30 120 330
97651 15 30 45
99999 60 120 75 360 615
11111 ...

**0**

votes

**0**answers

9 views

### How do I stop column formulas from automatically calculating in an Excel Table?

When using a column formula for a table in a workbook with calculation set to manual, when tinkering with formulas, Excel calculates them if the column formula changes.
The automatic propagation of ...

**0**

votes

**0**answers

17 views

### Vba Script to replace html tag with formatting

I am looking for a vba script, which replaces a html <strong>.
Example <strong>Any Text here shall be bold</strong>
The result shall be Any Text here shall be bold.
It shall macth ...

**0**

votes

**0**answers

21 views

### If 2 checkboxes true then add cell value - Excel Formula

I'm trying to add a list of prices depending on if 2 check boxes are ticked.
What I want to do is say if the paid checkbox is ticked and the just eat tickbox is ticked then add get the number in the ...

**0**

votes

**0**answers

14 views

### Excel 2010 merge rows where data in duplicated except 7 columns

I have a xls file.
I has some duplicate rows of customer names and other data that is identical.
I want to be able to find the identical rows by customerid and copy data from one row to another ...

**0**

votes

**0**answers

12 views

### Filtering Pivot Table

I have this Pivot Table, and what I need to do is: Filter this data and show only those records whose date (3rd column) is equal to its parent date (2nd column) or just 1 day difference is there.
And ...

**0**

votes

**0**answers

10 views

### Count data from one column if other column is blank

I have a table which contains data read from a database and imported into the spreadsheet. What I am attempting to do is create a "live" report that will collect this info (on refresh) and calculate a ...

**0**

votes

**0**answers

13 views

### Use COUNTIF to define a calculated (dynamic) address

I'm trying to find a way to use a COUNTIF formula with a calculated address as the first and last pointers to the range of data. Normally, one might specify something like this
...

**0**

votes

**0**answers

38 views

### Excel 2003 - Matching and summarising data across multiple worksheets

I am looking for help regarding my roster calculations.
I have built a roster over 13 worksheets plus a calculation worksheet.
On each sheet column D has a list of names populated from an offset ...

**0**

votes

**0**answers

12 views

### Forcing link formulas to be relative rather than absolute in Excel 2013

I am working with a simple "paste link" situation and I have set up a separate button to make it easier (I have about 500 different pastes to make). How can I force the formula to be relative ...

**0**

votes

**0**answers

47 views

### Conditional formulas in Microsoft Excel

I have different conditions in a sheet where I need to update certain factors like term of loans and down payments. How do I apply a formula in excel where I can get direct result for these:
Down ...

**0**

votes

**0**answers

16 views

### How to copy all sheets names without data?

In excel, I have workbooks and each workbook has ~60 different sheets. Now I have to copy the file with all their sheets and sheet names, but not the data. Can anyone tell me how to do that?
Thanks

**0**

votes

**0**answers

14 views

### How to create If a range cell in worksheet equals Y, then create new worksheets

I am trying to get my macro to copy sheets if a range value in active sheet = y, then create worksheets based on the Y values from the B Column with no duplicates. Any suggestions to what I am ...