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

**4**

votes

**0**answers

36 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

41 views

### Excel “Smart” Dropdown / Data Validation

In Excel, is there a way to create a dropdown menu or data validation field that
Allows the user to enter a value that is not currently on the list (like a combo box)
Save that value in the dropdown ...

**2**

votes

**0**answers

63 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

26 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

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

**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

265 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

23 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

24 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

25 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

31 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

51 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

22 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

17 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

32 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

143 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

47 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

135 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

611 views

### Splitting an excel 2010 spreadsheet into multiple workbook files based on column value

I find and clean a lot of contact data, sorted by country, which I am currently using excel to manage. Mainly because it is easier to clean and manipulate the data I find this way, and I can easily ...

**1**

vote

**0**answers

149 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

82 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

50 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

42 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

137 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

176 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

244 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

343 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

10 views

### Excel - Pieces of data from large chart sheet 1, auto sort on sheet2

I have a large chart and need to take small pieces of data (3 cells each for 14 rows) and have them stack ranked on the second sheet. I need A21:A34 (Branch Name), M21:M34 (% of Goal) and K21:K34 to ...

**0**

votes

**0**answers

13 views

### Copy Blank Count

i want to copy the blank color count under Color column from "5-Jul-15" sheet and "6-Jul-15" sheet to ABC sheet under "5-Jul-15" and "6-Jul-15" column (i.e. C13 and E13) respectively.
It should ...

**0**

votes

**0**answers

20 views

### Is this possible to generate excel combo box based on cell value?

As you can see, Cell C2 got 1,2,3,4. I assume this is a student id. So, I would like to generate a combo box in Cell B8, using Cell C2's data, separated by ,. So, in this case, the combo box should ...

**0**

votes

**0**answers

27 views

### Formula to reference a cell but not update with that cell

My workbook has data that is automatically loaded in from yahoo finance. The data is constantly being refreshed. I need a formula that can take the referenced cell and produce the number value of that ...

**0**

votes

**0**answers

25 views

### Excel formula works on one sheet but not the other. Non-blank max number

I'm trying to calculate the max number between 2 non blank cells in column A, using the values in column B when B>C. There are no blanks in B:B. C is always blank unless C>B.
...

**0**

votes

**0**answers

18 views

### Mode of text in excel

I have a spreedsheet with team numbers and some info about them (as text). Teams appear several time, and in a different sheet I have a summary for each team. I want to find the mode for one team.
I ...

**0**

votes

**0**answers

35 views

### Excel performances: INDIRECT+ADDRESS VS CELL+OFFSET

Using Excel I can achieve the same result using two different approaches, here are the formulas:
A) =INDIRECT(ADDRESS(5+MATCH($A$16,Aliquote!$A$5:$A$15,0),1,1,1,"Aliquote"))
B) ...

**0**

votes

**0**answers

29 views

### How to combine two sheets based on their dates?

I have two sheets: one is stock index, one is air pollution value. I want to match the air pollution value to each trading day. How can I do it on Excel?
the first sheet looks like:
01/01/1997 39
...

**0**

votes

**0**answers

18 views

### All Data Option Filter Validation

I am creating a dashboard that has five inputs that filters my data by the custom data validation combination. My dashboard updates correctly based on the custom combinations, but I am unable to add a ...

**0**

votes

**0**answers

14 views

### Using Hlookup to populate a worksheet from a data sheet

I am trying to develop a worksheet that requires a list of equipment based on an area header. I would like to be able to pull the separate lists based on this area header.
Here is sample data I am ...

**0**

votes

**0**answers

22 views

### Search for ID column in all worksheets and then search within those columns

I have multiple workbooks with multiple sheets on each.
I am looking for a way to return the value under the column ID#, like to search all the sheets in the workbook. The header ID# might be in a ...

**0**

votes

**0**answers

42 views

### Lookup ProductID on another sheet and return the lowest cost and its column header

I have recently been working with formulas and larger amounts of data than usual and need a little help. What I am trying to accomplish is the following.
I have two workbooks (which I could make ...

**0**

votes

**0**answers

33 views

### Can someone help me with an excel function that would make merging sheets easier?

I have about 61 spreadsheets that I want to merge into one big workbook.
Int the one work book I have tabs that are named the same as the spreadsheets that I want to import the data into.
There ...

**0**

votes

**0**answers

11 views

### Matching data between two separate worksheets and transposing the data from another column next to the matched data

I have two worksheets. I am trying to input the stock number next to the matched claim number. How would I go about doing this? The first sheet is my inventory list. The second is a list we get from ...

**0**

votes

**0**answers

25 views

### Microsoft Excel 2011 concatenate each row in a cell

I am trying to use an if function for each row for a result in a single/destination cell. The if function checks if a row meets a criteria, if it does, it adds the row details to the destination cell ...

**0**

votes

**0**answers

59 views

### Average If function not returning correct value

I am trying to do an average if function, it is the average of some cells which provide a number based on a formula in that cell subtracting dates in other cells giving a number representing a span of ...

**0**

votes

**0**answers

17 views

### How do I split a worksheet into multiple files dependent on the column values, calculate sub-totals and plot graphs?

I have a large data set and would like to split these into separate files/worksheets and perform the same analysis over each data set.
I have found a macro to split the data into separate ...

**0**

votes

**0**answers

24 views

### Column order and names for table objects in Excel

I have a table in Excel that is connected to MS SQL Server table in the database. When I created a connection I specified a query that returns col1, col2, and col3 in this particular order. Then I can ...

**0**

votes

**0**answers

31 views

### Search multiple sheets for a term in one column each, copy all matching rows to a new sheet

We have a 3rd party supplied , multi tab sheet that I would like to filter and copy data to a new sheet.
In the original sheet, all tabs can be searched for the term 8A in column C. I want any row ...

**0**

votes

**0**answers

11 views

### Formula returning #N/A when referenced cell is filled in?

I have a log I have been using for years with no problems. All of a sudden my formula is returning an #N/A. The formula reads =IF(C6=0," ",VLOOKUP(C6,'ODP LISTING'!B$4:C$35,2)). On the sheet labeled ...

**0**

votes

**0**answers

41 views

### Auto Extracting Data from Excel file on Onedrive

Our team is using One Drive for file sharing. What we wanted to do is to be able to Auto Extract data from 1 excel file on OneDrive to another excel file to be saved on another OneDrive ...

**0**

votes

**0**answers

30 views

### paste data with blanks that does not erase formatting in destination sheet

I am trying to collect data using ms excel mobile which does not support a number of the functions I want to utilize. I have set up identical spreadsheets for mobile and full featured excel. The ...