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

learn more… | top users | synonyms (5)

4
votes
0answers
51 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
0answers
62 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
0answers
68 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
0answers
28 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
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
0answers
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
0answers
274 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
0answers
41 views

Can I make Excel keep a history count when a selection is made?

I have a drop down list people (Data on Sheet#2) and I would like to keep a running total every time these people are selected under the drop down list for the type they were selected for. Example: ...
1
vote
0answers
16 views

Track workflow precendents using only Excel Formulas

what I'm trying to achieve is the following. I have a production chain, say for example: A and B are used to make C C and D are used to make E and E is used to make F And I wanna know the direct ...
1
vote
0answers
51 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
0answers
30 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
0answers
35 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
0answers
32 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
0answers
69 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
0answers
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
0answers
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
0answers
34 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
155 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
48 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
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
0answers
141 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
152 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
84 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
53 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
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
0answers
149 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
187 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
256 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
355 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
8 views

ISO Weeknumber formula explanation

can anyone breakdown this ISOWeekNumber formula and explain to me in detail how it formula works? =INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4))/7)+2 and this one? ...
0
votes
0answers
16 views

3 conditions in a nested Excel formula

I am using 3 conditions in a nested formula, each condition has a number of variants. So far =IF(I14=60,IF(AND(H10<10,D75=1),0.5,IF(I14=60,IF(AND(H10<30,D75=1),1, and so on. This works ...
0
votes
0answers
18 views

Sorting data by association?

That title may have been misleading, I don't know quite how to put it. I have a set of data where a number stands for a description. It currently looks like this: 10 Blue 11 Green 12 Purple 13 Red 14 ...
0
votes
0answers
8 views

Spreadsheet: Normalising multiple irregularly time-stamped data from sensors only roughly in sinc

I use the latest version of open office on a windows vista pc. I have .csv barometric data logged from two different sensors (pieces of equipment), that report when changes occur rather than at set ...
0
votes
0answers
23 views

sum rows and columns depending on two values

I got the following table in Excel (Microsoft Excel 2011 for Mac). It is basically for controlling issues. It shows a number of persons (employees) that are assigned to specific work-packages that ...
0
votes
0answers
11 views

What formula in OpenOffice calc can automate game show points as needed? Details in text

I host a trivia game show in which the answer to every question is a number. The eleven teams/players closest to the correct answer without going over receive points - 15, 12, 10, 8, 7, 6, 5, 4, 3, 2, ...
0
votes
0answers
31 views

Lookup and return values from unsorted sheet

I have a workbook set up something like this: Sheet 1 has data for some people: name, address, height, favorite color, and many other things.  In sheet 2, I want to pull all of the data for each ...
0
votes
0answers
32 views

Excel: Count duplicates only once, based on third criterion

So I got a excel sheet that lists (amongst other things) organizations and the year they were active in. Some organizations were active more than once a year. It looks like this: YEAR -- ...
0
votes
0answers
21 views

Formulas suddenly become text

I've been working on an extensive Excel list with cell references between sheets and it's been working like a charm, up until now. There is this one cell that refuses to acknowledge that I have ...
0
votes
0answers
7 views

Auto update filter - no macros

Is there a way to automatically update a filter without hitting "reapply", without using macros? I have a dropdown list and would like the worksheet to filter based on the coice from the drop down.
0
votes
0answers
33 views

Excel formula to find a particular sheet of a workbook from another workbook

I am looking up one workbook (Figuresmaster) to pull financial figures into another workbook (AreaBudgets). I have the below lookup formula working (in AreaBudgets). =IF(ISNA( ...
0
votes
0answers
28 views

Macro, Formula or Code? Copy data to new sheet based on specific criteria?

I have tried pulling this together with various formulas but A. A lot of the didnt work and B. it was very messy. For the example of this question lets use a Music CD with 2 Disc. The information is ...
0
votes
0answers
22 views

Excel 2007. 2nd Formula wont accept result of 1st formula on a different work sheet

I have a rather frustrating problem that it seems others have had also but I have yet to find a solution that works. I have simplified things below as I tend to give too much information. Basicly I ...
0
votes
0answers
23 views

Excel linked data formula turns to #REF upon opening workbook Office 365 ProPlus

Open File A with vlookup formula referencing File B. File A will show fine. Open File B and File A will change to #REF! I correct the #REF! and make sure to update formulas (calculate) to file B. ...
0
votes
0answers
12 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
0answers
26 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
0answers
29 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
0answers
37 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
0answers
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
0answers
36 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
0answers
30 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 ...