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

**0**

votes

**1**answer

37 views

### Excel - How to check if and in how many rows a certain value/string is present in range

I have a list A of strings and I want to see if and if yes in how many rows each string is represented in range B. My data looks something like this:
List A:
aaa
bbb
ccc
Range B:
1 aaa --- ---
...

**0**

votes

**0**answers

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

**4**

votes

**1**answer

373 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

33 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

15 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

41 views

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

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

13 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

21 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

19 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

21 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

62 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

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

**1**

vote

**1**answer

48 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

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.

**1**

vote

**1**answer

39 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

28 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

24 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

36 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

38 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

28 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

34 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

43 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

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

**1**answer

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

**-2**

votes

**0**answers

41 views

### Google sheets/excel array formula return row of values based on =TODAY()

I am collecting data from a Google form to a spreadsheet and then capturing that information into a 2nd spreadsheet for manipulation. From here I want to display the same information on a 3rd sheet ...

**1**

vote

**1**answer

39 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

38 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

49 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

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

**-1**

votes

**0**answers

17 views

### Excel Auto Fill Date

I am using Excel to keep track of my finances. I have created a column for the date (Column A) using the today() excel function. How can I automate it for multiple entries for the same day? So for ...

**-2**

votes

**1**answer

37 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

73 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

48 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

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

**-1**

votes

**1**answer

84 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

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

**2**answers

37 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

71 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

52 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

**0**answers

15 views

### Daily spend vs projection spend pacing (%) [migrated]

I have a google doc sheet which currently has all our accounts spends.There are two tabs.The first tab details our actual daily spend for each account for each day of the month.The other tab is a ...

**0**

votes

**1**answer

44 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

24 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

31 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

88 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

82 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

40 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

26 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

41 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

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