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

learn more… | top users | synonyms (5)

3
votes
0answers
52 views

Highlight differences between two Excel spreadsheets

Data from Spreadsheet A has multiple columns but I need to compare only: Column A - order # Column C - count Column D - date Spreadsheet B also has many columns, but I need to compare: Column A - ...
3
votes
0answers
4k 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
13 views

Show MS Excel Syntax as you type Formula

I have Office 2013 on my work computer and Office 2016 on my personal computer. On my work computer in Excel 2013 when you type a formula you get the suggested syntax for formulas as you type, but on ...
2
votes
0answers
82 views

Excel- How do I find common text values from multiple (>2) rows?

This is essentially a variant of the question asked here: Excel- How do I find common text values from multiple (>2) columns? In this case, however, instead of multiple columns of data I need to ...
2
votes
0answers
41 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
431 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
55 views

Create dynamic formula so that one Excel table looks-up the same column name in another table

I have two Excel tables, table A and table B. Table A contains the columns Customer ID, Postcode, Customer Name and several others that have the same column names as columns in table B. I want to ...
1
vote
0answers
21 views

Table relationships across 3 tables and updating all with a single slicer

I have 3 tables with data. All of which have a username column and I'm trying to connect them. For this purpose I have created a 4th table which is basically just the User directory (where each ...
1
vote
0answers
41 views

Excel 2016, “{}” disappearing in formula

I've modified an existing template called "Daily Schedule". For some reason, all of the cell formulas have "{}" around them. When I click into the cell to edit the formula, the "{}" disappears. After ...
1
vote
0answers
19 views

Dynamically Naming Sections of Columns in Excel

I am doing calculations that change how much of the data I want to include in a graph for each data set. I have cells that reference the first and last row numbers that I need to use and then I have a ...
1
vote
0answers
66 views

Excel 2010 - Combine specific sheet in >200 separate files into single worksheet?

I have tried solutions in other posts like this and so far nothing has worked :/ I have >200 separate excel files, and essentially I would like a specific sheet from each of these files combined into ...
1
vote
0answers
31 views

Excel doesn't create structured formula for all tables

Structured formula case I am facing 2 weird issues with Excel 2013. I have been mainly using the tables. Initially when I created the sheet and inserted tables and added formulas, they were all using ...
1
vote
0answers
14 views

Plot a chart directly from a function in Microsoft Excel

I need an help with chart plotting in Excel (I'm using Excel 2016) let's say we have a simple function like: y=1/x Right now, to plot a chart based on this function I use two columns. In the left ...
1
vote
0answers
31 views

Show Excel Cell Only If Another Has Data

I am trying to format the below Excel sheet so that when a date is entered into cell J1, cells G24 - G37 populate with a date a specific to what I need. I currently have the cells formatted as follows:...
1
vote
0answers
26 views

Find and replace multiple values, according to table value - excel formula

I would like a FORMULA that change letters according to table of values that i make. example: êíöä = LOVE (because i made a table that show: ê=L, í=O, ö=V, ä=E). example- Picture: As you can see ...
1
vote
0answers
120 views

VBA code to select by a cell value and date range to move data from one sheet to another

I am having trouble getting a VBA code that I have adapted for my program to run. There are things in it that I don't understand and I don't know how to fix. I am trying to filter data from a "Sales" ...
1
vote
0answers
41 views

Sum csv-entries in a field in excel without a macro

Let's say I have in my Excel sheet in field A1 the content 1, 2,3,4,5, 10 then I want to have a classic excel formula combination to sum the entries up, e.g., I want to type in A2 a forumla = ...
1
vote
0answers
37 views

Optimize row height/column width in Excel = minimize white space

I often create tables in Excel where some cells contain a lot of text while others just contain a single digit or some other short value. With wrap text in cells enabled and then a combination of ...
1
vote
0answers
24 views

Aggregating Repeating Labels in Excel Sheet into Proper Table

I have an excel file with two columns: the first contains a repeating sequence of 14 labels, while the second contains the text values associated with those labels. What I'd like to do is aggregate ...
1
vote
0answers
96 views

EXCEL formula variable pricing structure

Cell B3 equals a number of Item 1 purchased with a variable pricing structure. I have a formula that works great and creates an accurate $ cost =IF(B3<5,7188*B3,IF(B3<=19,(B3-4)*4788+28752,IF(...
1
vote
0answers
47 views

Calculating averages for every n rows with missing data

I have a column with 30-min averages, and I'd link to make a new column with 24-hour averages. I found this formula very useful: =AVERAGE(INDEX(D:D,1+48*(ROW()-ROW($E$1))):INDEX(D:D,48*(ROW()-ROW($E$...
1
vote
0answers
60 views

Why doesn't Excel auto-adjust formula when a row is moved?

I have a simple spreadsheet with just one formula, which auto-increments the number in the first column, going from cell A6 down. So the value in A6 is hard-coded 1, then A7 contains the formula =A6+...
1
vote
0answers
20 views

Cell auto adjust height when pulling data from other cells?

I've built a spreadsheet with a summary report that auto formats fine, except in situations where the text in one cell and is too long to fit into a cell. The report pulls info from cells under ...
1
vote
0answers
14 views

Removing duplicate values in a cell

I found a great post answering ALMOST what I need. I want to list values as such; (so that the true line is the only one I want) Product A Cyan Cyan FALSE Product A ...
1
vote
0answers
41 views

Excel - Can I create a list of values with criteria and have excel make a list with parameters?

In Excel, I am trying to see how many combinations I can make with the allotted criteria and such. So lets say I want to know how many combinations of groceries I want to buy for a certain budget. ...
1
vote
0answers
77 views

10 minutes to calculate the very simplest excel formula - why?

Well I know partly why, my spreadsheet has about 180,000 rows. But that has not presented a problem for sorting or filtering. But when I entered =B2 in cell C2 and stretched it down to the bottom, it ...
1
vote
0answers
25 views

Control Number Generation

So currently I have the following. When I click a button or hit submit button The form looks on the Ledger Tab. I was trying to get the form to automatically generate a control number (ConNum) that ...
1
vote
0answers
18 views

I am trying to turn named ranges into named tables while keeping Auto populating headers

I have a spreadsheet with 32 individual named ranges and the Section Title (B338 for example) is linked to the equivalent value in the Table of Contents (B70 for example). What I would like to do is ...
1
vote
0answers
60 views

How can I ensure that a formula that I have written to calculate the difference between time values applies only to the filtered values?

I have two columns of data: Member Time A 09/24/2015 09:48:36 A 09/24/2015 09:47:40 A 09/24/2015 10:11:06 A 09/24/2015 10:08:47 B 09/24/2015 10:28:45 B 09/...
1
vote
0answers
26 views

Excel formula to convert self to value?

Is it possible for a formula in excel to return a value and assign it to itself, overwriting the formula? Example: If column A consists of =IF(LEN(B2)>0,"1","0") On cell contents in B2, I want ...
1
vote
0answers
66 views

Sum data from muliple cells, multiply by other cell range, reference on other sheet

Ive had a couple issues with this workbook recently this seems like the biggest. As you can see in the pics, this a time sheet that needs F15 multiplied by Q15, then added to F20 which was multiplied ...
1
vote
0answers
96 views

How can I make an excel user defined function (UDF) work in an array function (CSE)?

I am using Excel 2010. I wrote a UDF to create a unique number for each month: yearmonth = 12*Year(date) + Month(date) Now I want to use it in a CSE: {=sum(if(yearmonth('plan'!$j$3:$j$1000)<>...
1
vote
0answers
41 views

Excel Formula to autopopulate a cell and mark as Assigned

I am attempting to auto populate a excel cell with an account number with the following formula. =INDEX(C2:C20001,MATCH(FALSE,B2:B20001,0)) Column B contains True/False and Column C contains the ...
1
vote
0answers
25 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
160 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
55 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
130 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
36 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
200 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
23 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
22 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
62 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
323 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 ...
1
vote
0answers
68 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
62 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
183 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
193 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
121 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
65 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
46 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 ...