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

**2**

votes

**0**answers

48 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

24 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

66 views

### Excel Index Match with IFs?? + options for next in list

OK I have been working on this for some time and can't quite get things right in the various combinations I have tried, so I am asking for advice.
I have a management data table where column C is a ...

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

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

250 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

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

35 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

21 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

25 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

128 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

45 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

45 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

123 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

600 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

144 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

79 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

48 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

131 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

171 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

239 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

328 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

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

10 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

19 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

36 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

28 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

10 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

22 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

45 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

14 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

22 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

26 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

35 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

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

**0**

votes

**0**answers

30 views

### SUMPRODUCT & ROUND

Is there a way to get the product of the array with conditions to round to the 2nd decimal place before summing?
Current formula w/o rounding:
...

**0**

votes

**0**answers

37 views

### How to Sum based on two critera, date range and name

I have a table that has timesheet data for a number of users.
Column A has the resource Names. Row 1 contains each week of the year, starting with the 5th of Jan.
The table contains hours booked by ...

**0**

votes

**0**answers

33 views

### finding the difference between two times in libreoffice

I'd like to know how to solve the problem:
in cell A(n) is the first time
in cell B(n) is the second time
in cell C(n) is difference of times
I don't want to put each time into cell C(n) function ...

**0**

votes

**0**answers

79 views

### Extracting HTML from Excel to enter in MySQL/Wordpress site

I have a WordPress site and an Excel document. I'm not familiar with Excel formulas so forgive me.
The document contains a lot of names, email addresses and telephone numbers. I want to create a ...

**0**

votes

**0**answers

48 views

### Excel formula If column B starts with… then columns D & E populate from a list

I'm working on a spread sheet where column B is filled with ID numbers. Column D and E correlate directly to the first few digits of the ID number.
Example:
B= 1.1.1 then D= Imaging and E= T.P.
B= ...

**0**

votes

**0**answers

31 views

### How do I copy/referrence last value of the column from another file and worksheet

I have two spreadsheets - one is the inventory sheet that gets updated with sales and purchases, and the other spreadsheet is where I want to reference or copy the ending inventory as soon as it gets ...

**0**

votes

**0**answers

19 views

### How can I Collate and create totals of a range of transactions in iwork Numbers

ColumnA ColumnB
Dave 10
Dave 10
Peter 5
Susan 5
Dave 5
Peter 5
I would like to create new table to read down ColumnA, create a new list with no Duplicates and Total up the Values ...

**0**

votes

**0**answers

100 views

### excel - how to count number of names in a column, name is specified in a cell

I have two worksheets, "Clients 2014" and "Receipts 2014".
The receipts have client names for each row (each row is a receipt).
The search key is in the "Clients 2014" worksheet.
I want to ...

**0**

votes

**0**answers

54 views

### Memory limits in Excel (functions lookup)

can I obtain an advice how many Excel files with what size can I have opened at the same and link with functions (lookup) in Excel 2010 32 bit system?
Is there a way to predict how fast the ...

**0**

votes

**0**answers

54 views

### Why is VLOOKUP returning wrong cells as data is refreshed?

I am creating a spread sheet which works out my material requirements based on future order forecast. I have live data in the workbook which represents the material on stock and they're location. I ...

**0**

votes

**0**answers

34 views

### Rearrange columns from fiscal year to calendar year in OpenOffice spreadsheet

I have twelve monthly columns plus a total column in a spreadsheet that was originally created for an April through March fiscal year. Now I need to rearrange the columns for a calendar year, without ...

**0**

votes

**0**answers

68 views

### IF Function Combined with Median date range check across multiple sheets

I am trying to create a formula that checks if a single cell date falls between a beginning and end date using the median function, and if so returns a value. I have multiple sheets I would like the ...