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

learn more… | top users | synonyms (5)

2
votes
0answers
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
0answers
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
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 ...
2
votes
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
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
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
0answers
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
0answers
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
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
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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 ...