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

learn more… | top users | synonyms (5)

2
votes
0answers
20 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
0answers
29 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
0answers
1k 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
0answers
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 ...
1
vote
0answers
12 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 ...
1
vote
0answers
19 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
0answers
88 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
0answers
43 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
0answers
41 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
0answers
107 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
0answers
570 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
0answers
135 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
0answers
72 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
0answers
47 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
0answers
38 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
0answers
114 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
0answers
154 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
0answers
215 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
0answers
294 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
0answers
13 views

Most efficient function(s) to compare and extract rows from two reports?

I work for an international shipping company processing hundreds of containers each day. I generate a report (from an AS 11g servlet, FWIW) in the morning, work through it based on the 2 Modes of ...
0
votes
0answers
32 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
0answers
23 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
0answers
38 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
0answers
21 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
0answers
21 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
0answers
11 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
0answers
43 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
0answers
16 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
0answers
33 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
0answers
27 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
0answers
44 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 ...
0
votes
0answers
24 views

Excel no longer auto update my calculation?

My excel sheets does not auto update my formulas. It did up to a point and now it no longer do. Just checked the settings under file and it is set to auto? Can some please help. Sheet to big to redo. ...
0
votes
0answers
42 views

Prevent Excel from changing formula of last row of table when adding a new row

I have a table with columns named "date", "cost" and "daily". "Date" is a date, "cost" is a number and "daily" is a formula calculating the total cost of each day. The formula looks like this: ...
0
votes
0answers
34 views

Delineate Excel Formula Without Using Wizards

Is there a combination of excel formulas that I could use to do this without using VBA or the import wizard? I know how to insert comma delimited data from a text file and to split a cell into ...
0
votes
0answers
41 views

copy and paste according to a order in a column

I want a macro that will fetch data from Series B and Automatically paste it according to the serial of Serial A. ASeries B and Series A data are same but Series B is not ordered. So i'm looking for ...
0
votes
0answers
37 views

Formula to search two columns of a database and return value from different cell in the matching row

I am designing a spreadsheet for scheduling appointments. On one sheet I have all the data but the dates and times are not in chronological order. I am having trouble writing a formula that will ...
0
votes
0answers
23 views

Excel automatically applying styles to new data

I'm having a problem with Excel 2013. I have a spreadsheet filled with names and related information for applicants. When I contact the applicants, I highlight the cells (using the fill tool) so I ...
0
votes
0answers
17 views

Excel locked columns

-Is is possible to lock a column, for example column F, and then set it so if i scroll from column E, all other columns will appear before the F column. I am trying to set F up at the end of a spread ...
0
votes
0answers
16 views

Can Excel automatically create an x-axis range?

I'm doing a basic financial analysis with Excel. The gist of it is I have an input parameter (discount rate) plus a bunch of static data that gets fed into the =NPV(rate, x1...x_i) function. I would ...
0
votes
0answers
35 views

Excell 2013 Calculating across Multiple Sheets number of fields

I am trying to work out the total number of fields across multiple sheets to arrive at the total number of new members each month. Some months have little as 50 others as much as 250+. I thought I ...
0
votes
0answers
30 views

Issue Using Advanced Filter with Multiple Criteria

I'm trying to use the advanced filter to filter multiple criteria in two separate columns. The example I'm about to show is simplified so it is very important that we stick with advanced filter as ...
0
votes
0answers
40 views

Expanding abbreviated text with Substitute formula in Excel

I have a spreadsheet contains a list of 24k items found in a grocery store. Most items have abbreviated words such as: w/ for with; choc for chocolate; sauv for sauvignon. Yet the list contains both ...
0
votes
0answers
22 views

How can I copy new data with additional rows, while including formulas?

As an example I have data in the range A1:D55, and in Column E I have a formula using VLOOKUP in reference to column A. I pull the data again for A1:D55 and the values change (titles remain the same) ...
0
votes
0answers
12 views

Keeping cells in the same row lined up when columns in the same row have 2 different data sources

I am working on a worksheet that references data on a different worksheet (within the same workbook). Each row in the referenced worksheet represents an order. Data from the referenced worksheet ...
0
votes
0answers
21 views

microsoft excel 2010 formula writing

I am writing numbers in a cell, but need the value of these numbers to change according to the letter being assigned. For example: 70 if I add a Q on the cell before (or after) it, I want it to ...
0
votes
0answers
135 views

Check if substring of cell in one row matches with another cell on column values in another row

I have a table with 2 columns (ID number, Description) as shown below: ID no Description 1001 A letter to george william 1001 ...
0
votes
0answers
16 views

Excel 2010 “lastcell” issue with formula in complete column

I'm currently using a formula throughout a column in an Excel 2010 worksheet. It works fine, but I'm not really using the entire worksheet, I only don't want to have to worry about adding the formula ...
0
votes
0answers
21 views

Spreadsheet Function Help - From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And There About

I have posted this question before, and got half-solution. Pls have a look at this link to get an idea. http://yogi--anand-consulting.blogspot.in/2014/11/yogifrom-table-of-names-and-birth-dates.html ...
0
votes
0answers
36 views

Formula to copy rows to a new worksheet based on a single value in a row

I need to extract complete rows from a spreadsheet to a new spreadsheet based on a specified value in the row. Cannot be advanced filtering since that only allows me to copy into same worksheet. Is ...
0
votes
0answers
57 views

Microsoft Excel Starter 2010

I have multiple columns/tables of data that I want to use in what I think would be an if/then formula BUT I have more than 2 options ie. I want to be able to type in a number (62111) and have it auto ...