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

learn more… | top users | synonyms (5)

4
votes
0answers
32 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
39 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
61 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
25 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
264 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
18 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
22 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
42 views

Align rows from different columns (which contain some cells that have identical values) using simple formula in Excel

I have 2 tables in Excel and I am trying (via Excel formula) to match/align some rows (ones that contain identical letters for the sake of the example included below). Btw, I don't mind aligning Table ...
1
vote
0answers
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
0answers
49 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
28 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
140 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
47 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
134 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
607 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
147 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
81 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
50 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
136 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
174 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
243 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
342 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
10 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
11 views

Copy Blank Count

i want to copy the blank color count under Color column from "5-Jul-15" sheet and "6-Jul-15" sheet to ABC sheet under "5-Jul-15" and "6-Jul-15" column (i.e. C13 and E13) respectively. It should ...
0
votes
0answers
18 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
27 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
25 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
32 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
28 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 ...
0
votes
0answers
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
0answers
14 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
0answers
22 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
0answers
42 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
0answers
33 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
0answers
11 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
0answers
24 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
0answers
56 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
0answers
17 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
0answers
23 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
0answers
31 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
0answers
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
0answers
39 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
0answers
29 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 ...