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

**0**

votes

**0**answers

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

**4**

votes

**1**answer

387 views

### Does Excel always calculate hidden rows?

If I have Excel sheets with long filtered lists, and I recalculate while a filter is applied, when I remove or change filters, can I always guarantee that Excel will have already calculated all hidden ...

**0**

votes

**0**answers

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

**1**

vote

**1**answer

16 views

### Conditional Formatting in Excel via a different worksheet

I've been trying to figure out how to make a cell bold and color red based on conditions entirely in a different worksheet. For example, I'd like Sheet1!A1 to be bold and red when Sheet2!A1 = ...

**1**

vote

**0**answers

46 views

### Can I make Excel keep a history count when a selection is made? [closed]

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

**1**answer

15 views

### In LibreOffice Calc is there a hotkey to edit the current cell?

I am wanting to edit a column of cells (for example, copy/pasted values have $0.005/hr and I want them to just be 0.005)
I can navigate with arrow keys, I can click to edit the existing value, but ...

**0**

votes

**1**answer

28 views

### How can I use vlookup function for multiple table array that has different length?

I need to import the minimum value form Column C to column B for each item, but the problem is that the table array is different for every item.
Since I can't upload any image, I will just type the ...

**0**

votes

**1**answer

20 views

### Adding Text to Existing Data in Excel

Is there a way in Excel to add text without spaces to the ending text of contents of a column. For example: I have 600 records and I want to add the letters OP directly after the contents of all ...

**1**

vote

**2**answers

25 views

### Comma inside inverted commas in an IF statement

In the formula, IF[1000,",",1/3*27]
Please can you let me know what the "," represents?

**1**

vote

**1**answer

110 views

### Excel: Count duplicates only once, based on other criteria

I need a formula that counts only unique values based on a third criteria.
So I got a excel sheet that lists different organizations, the location they were active in, and the year they were active ...

**0**

votes

**0**answers

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

**1**

vote

**1**answer

99 views

### Switch Calc (or Excel) formulas to English language

I'm not using an American/English version of Calc/Excel so, every time I want to post here (or on other "web-places") or when I want to try formulas posted by others I need to "translate" them somehow ...

**0**

votes

**1**answer

15 views

### Summing the above 7 rows in same column based on a value

I have a series of values that repeat like days of the week doing down in the same column.
Monday : 2
Tuesday : 5
Wednesday: 6
Thursday : 1
Friday : 8
Saturday : 12
Sunday : 4
Monday : 3
...

**0**

votes

**0**answers

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

**1**

vote

**1**answer

46 views

### Combine text contents of two cells ONLY if one has an entry

I want to copy the contents of two cells as an easy "cheat" to be able to copy the text for entry somewhere else.
The first cell (D2) is a name. Example: Smith, John
The second cell (AG2) is a note. ...

**0**

votes

**1**answer

35 views

### How do I highlight the max values across every column in a pivot table?

Looking for a way to use conditional formatting to highlight the max value in every column of a pivot table.

**0**

votes

**2**answers

26 views

### Excel not to start calculation on Automatic mode if data is put wrong

I want excel to stop calculation if invalid date is put. I tried to use "data Validation" using below formula =AND(ISNUMBER(E3),LEFT(CELL("format",E3),1)="D").
It is working but the calculation don't ...

**0**

votes

**1**answer

44 views

### How to identify duplicate combinations of two values in random order

I am trying to identify the second duplicate combinations of cells across two (or more) columns. Complicating matters, the order of combinations varies.
My data looks something like this:
aaa 111
...

**2**

votes

**1**answer

54 views

### Creating a line graph in excel with changing/result cells

For a project we are creating care packages for the homeless. I am trying to figure how many kits to make for the least leftover material as the things we are putting into the packages are sold in ...

**0**

votes

**2**answers

37 views

### Excel Countifs date column not empty

I am trying to count number of external employees.
There are two columns,
Column F with values EXT for external and INT for internal employees
Column G with date.
Now, if date column is empty ...

**0**

votes

**0**answers

47 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

**2**answers

46 views

### Is it possible to have an Excel formula dependent on value input?

Is it possible to have Cell G24 recognize the number in D24, and do its math depending on the size of the number, using the in Cells B37 through B42?
I have a basic spreadsheet for cataloging sales ...

**0**

votes

**0**answers

35 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

**1**answer

31 views

### Sum up values from two lines but keeping some columns seperate

I want to sum up the values in Column C for rows with same Column B value.
In my example the values for A Dunn and G Birch should be combined, with the bottom line going blank
But column A (account ...

**1**

vote

**1**answer

43 views

### How to automatically sort random numbers

I've got 2 columns in a spreadsheet.
Col. A contains a formula that generates a random number:
=RANDBETWEEN(1,100)
Col. B calculates the rank of the random number in A using
...

**1**

vote

**2**answers

46 views

### How to convert cells of a column to text in Excel 2007

I have a column consisting of cells (from 1 hundred to over 100 thousands) with values like :
5
12
78.456
1235.78510
AFT780.963
ZFLQ
57180.004
...
I want a way to rapidly change the format of the ...

**2**

votes

**2**answers

64 views

### Raise matrix to power without VBA

I need to raise a matrix to a large power (say 200), and I can't use VBA because it is for a final user whose IT doesn't allow macros to run.
Also since the power is large, it won't suffice to write ...

**0**

votes

**0**answers

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

**-2**

votes

**1**answer

45 views

### MS365, Excel 13 - New row added not auto updating formulas

I have a table in Excel I made myself not an inserted one - though I have tried that too it didn't do anything. The problem is at the end I add a new row and none of my formulas are in the new row. My ...

**4**

votes

**2**answers

90 views

### How can I parse a string from a cell multiple time in excel?

I have a column in my datasheet that often contains a string that I want to place in another column. This string may occur multiple times and I want to place ALL instances of it into the other column. ...

**1**

vote

**2**answers

53 views

### Formula substitution excel

is it possible to do formula substitution easily in excel?
I have a worksheet similar to below example (albeit more complex, hence the need for some function in excel if there exists one)
What I ...

**0**

votes

**0**answers

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

**-1**

votes

**1**answer

86 views

### Correct way to write or even better combine these two Excel 2007 formulas

I have two formulas. As an example for this question let's assume I have a workbook that is basically a CD Database. It contains all the usual information that you would expect.
This workbook is ...

**1**

vote

**0**answers

19 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

**2**answers

46 views

### How can I copy a column from one sheet to another sheet based on title in Excel?

I have a datasheet that has many columns that may change positions. To make it easier to use I want to create a second sheet that keeps the columns in a specified order. I can count on the column ...

**1**

vote

**1**answer

87 views

### Change excel formula references when dragging across columns

I have a row that I want to populate with formulas like
=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)
=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)
=SUMPRODUCT(D91:D94,D44:D47)/SUM(D44:D47)
but dragging ...

**1**

vote

**0**answers

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

**0**

votes

**1**answer

63 views

### Assigning values to rows based on keywords [duplicate]

My bank statement has a description column which tells me where I spent the money or where I got it from. Based on this information I fill a column manually to be able to catagroize these expenses - ...

**1**

vote

**1**answer

25 views

### How to determine largest individual value in excel in a group of identical cells?

I am trying to figure out Column D Which SKU has highest total? how do I do that in excel with a formula? In this case I determined it manually, I tried IF=IF(A2=A3,IF(C2>C3,"Largest","NOT Largest")) ...

**0**

votes

**1**answer

39 views

### Adding value of rows if column A and B cells are similar in Excel

I have a situation. I have three columns
| A | B | C |
|Category | Sub | Price |
Column A is basically Category column, column B is Sub Category and "C" is price.
I need to add the ...

**2**

votes

**2**answers

95 views

### IF statement formula for overdue DVD rentals

I am creating a spreadsheet for my local parish church to keep track of borrowed DVDs:
The DVDs have to be returned 14 days after they are borrowed. If the current date is over 14 days after the ...

**0**

votes

**2**answers

85 views

### If Difference Higher or Lower than Display

I need a excel formula that will show:
* "1" if B is less than A by more than 2
* "0" if B is within 2 of A
* "-1" if B is higher than A by more than 2
Can someone please tell me whether this is ...

**0**

votes

**1**answer

66 views

### Calculate time difference in percentage

I'm trying to make some statistical data on the performance of a specific task.
One of the data points gathered is how long time it took to run. I'd then like to compare that against the expected ...

**0**

votes

**1**answer

32 views

### Excel formula to show multiple cell address or row locations?

I'm using the following formula, which returns a numeric value:
=SUMPRODUCT((A3:A176="Thing1")*(B3:B176="Thing2")*(C3:C176="Thing3")*(U3:U176="No"))
However, I'd like to also know the cell address ...

**0**

votes

**1**answer

44 views

### Extract text between emails with < >

Original link
Extracting Data Between two Characters in Excel
I have a combination of cells where some have nicknames and some without nicknames.
Some will have nickname < > emails and same will ...

**0**

votes

**1**answer

190 views

### How to return all the cells in a row if first column matches a value on another worksheet

I have 2 worksheet tabs in an Excel workbook, tickets and tickets info. I have values in Column A of tickets (from A2:A500). I want to search for each value from A2 to A500 of the ticket sheet looking ...

**2**

votes

**2**answers

59 views

### Fill a large range with a formula in Excel, without mouse-dragging to extend

I have 5000 elements in column A and 5000 in column B
I want the element wise difference and stored in column C.
The subtraction command I know is =A1-B1 then drag all the way down with a mouse.
...

**0**

votes

**1**answer

44 views

### How do I superimpose a conditional formated heatmap based on one set of values over a different set of values in Excel 2013

I would like one cell to be formatted with a gradient color based on the value in another cell. For example, I would like cells A1 and A2 to be highlighted based on the values in B1 and B2. If I ...

**0**

votes

**3**answers

40 views

### EXCEL VLOOKUP returning #NA when IF condition specifies “ ”

=IF(VLOOKUP(D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry) Specifications.xlsx]Big'!$B:$B,1,FALSE),"Project
BIG",IF(VLOOKUP(D4,'[05.14_Wave 5 Priority 14 (Big+Cranberry)
...

**1**

vote

**1**answer

56 views

### Excel 2013, SUMIFS formula

I think I need to use a SUMIFS function for this but I am not positive. I have a list of codes:
All of these codes are in a category of downtime, each code also has minutes 5,10,15,40,65 etc. My ...