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

**2**

votes

**0**answers

18 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

18 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

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

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

245 views

### VBA - Arrayformula to a range stored as array variable

I need to apply a formula array to dynamic ranges in my spreadsheets, which for a fixed size range works fine using for example:
Selection.Offset(2, 0).FormulaArray = ...

**1**

vote

**0**answers

30 views

### How to drag fill series in excel to adjust sequencing without removing the hyperlinks attached to cells?

I have a range of cells in a column in excel, that have some number in them along with text. For Example:
TA_47
TA_48
TA_49
TA_50
TA_51
TA_52
TA_53
TA_54
TA_55
TA_56
TA_57
TA_58
TA_59
TA_60
Now I ...

**1**

vote

**0**answers

70 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

41 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

35 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

99 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

538 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

134 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

70 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

46 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

103 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

146 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

202 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

264 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

7 views

### Find a specific name in a column, bring in the value in the corresponding row, in the column I designate in another worksheet

So I have a worksheet that people will go into and put their name down to sign up for a meeting for a specific date. In a separate sheet, I have their names and I manually input the days they signed ...

**0**

votes

**0**answers

23 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

**0**answers

20 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

**0**answers

26 views

### Excel: Converting two lists into a matrix

I have two lists in my workbook currently
Name | System 1 | System 2 | System 3 |
John | x | x | |
James| | x | x |
Peter| | x | ...

**0**

votes

**0**answers

34 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

**0**answers

27 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

**0**answers

19 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

**0**answers

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

**0**answers

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

**0**answers

24 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

**0**answers

21 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

**0**answers

33 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

**0**answers

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

**0**answers

20 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

**0**answers

126 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

**0**answers

15 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

**0**answers

19 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

**0**answers

27 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

**0**answers

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

**0**

votes

**0**answers

34 views

### Formula entered in a cell on sheet A referencing sheet B populates in sheet B

I'm seeing an error when entering formulae which reference another worksheet. This typically only happens with large workbooks.
Example: I have two sheets, DataSheet and OutputSheet. On OutputSheet I ...

**0**

votes

**0**answers

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

**0**

votes

**0**answers

64 views

### Notepad++ vlookup to Excel

I have an XML file that has some specific incorrect text within it. The incorrect text is associated to a specific code that identifies it,. Eg: CODE="RM014" PART="ROOM MONITOR".
The issue is that ...

**0**

votes

**0**answers

32 views

### Xcel spreadsheet formula for sorting qualifying names

I am trying to set up a spread sheet to determine qualification for employee of the month. I am able to insert all the employees that qualify under a certain criteria. How can I make a formula that ...

**0**

votes

**0**answers

168 views

### Trying to get VLOOKUP to get data from a table array on another workbook where the worksheet name is variable

I currently pull data in from 4 workbooks using the INDIRECT function, the formula is as follows,
=INDIRECT("'[" & $AE$1 & "]" & $AA$13 &"'!" & $AI$1)
$AE$1 contains the workbook ...

**0**

votes

**0**answers

19 views

### Alternate row colors only if there are contents in them

I want to alternate row colors in an OpenOffice spreadsheet, but only if the first cell in the row has contents in it.
The following function works great to return the correct value:
...

**0**

votes

**0**answers

28 views

### Copy distinct values in calc

I have some text data in column A, adding data to that column is the standard use case. I would like Calc to dynamically copy only unique values to, say, column D.
I am aware of OpenOffice: get ...

**0**

votes

**0**answers

13 views

### Prepend text for spreadsheet

I want to have a string value prepended to a XL-style formating in a spreadsheet program (gnumeric). The current formatting is [$-f4f2]hh:mm which should take 1:00 PM as input and convert/display it ...

**0**

votes

**0**answers

13 views

### Excel referencing: Go To Box does not fully have the whole path

When I reference to another workbook in the formula line I see the whole path to the workbook. So that is good. But when I have the referenced workbook closed (the one I get figures from) and I want ...

**0**

votes

**0**answers

21 views

### Microsoft Excel 2010, Adding/deleting lines in linked workbooks by only adding/deleting them in the Master Workbook.

I have Multiple Woorkbooks linked to one Master Workbook. Each WB has 13 columns and 452 rows(Different Products I sell). Only one column(My Selling Price) is linked to the Master as of right now. ...

**0**

votes

**0**answers

28 views

### Continue from one excel sheet to other without merging them together

i have an Excel workbook whic contain monthly sheets of sales. Each monthly sheet contains sale figures for various items (in columns) on a daily basis. the days are grouped in weeks.
Now i want to ...

**0**

votes

**0**answers

31 views

### save a sheet in new closed workbook without links

I have this code which work pretty good but I have a problem that the new exel workbook have links from the old one.
Sub archive()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As ...

**0**

votes

**0**answers

33 views

### Dynamic Scenario output in excel

I have a model where in the input numbers going into the model depend on the cases/scenarios i select from a drop down menu ( Base, Best and Worst). These numbers are then used across all remaining ...