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

**1**

vote

**1**answer

33 views

### Automatically update date in excel cell based on system date

I have data in an excel cell like this: 11/2/2015 6:05:00 AM
I need to update this as follows:System Date+1 6:05:00 AM
What function can be used so that whenever sheet is opened up, based on system ...

**3**

votes

**3**answers

251 views

### Split Excel Cell by last Delimiter

I need to split a cell by the first delimter (reading right to left) so that I can put that value in another cell.
For Example, my data in cell A1 could look like:
"something - more something - a ...

**0**

votes

**1**answer

36 views

### Excel formula weekdays

I am trying to create a formula that will give me the expected due date of a task (15 business days), and that needs to exclude weekends, that its easy and I did it with a simple weekday formula:
...

**1**

vote

**0**answers

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

**0**

votes

**1**answer

27 views

### Sheet numbers do not change when I fill.

I have a formula:
=SUMIF(Sheet11!$K$3:$K$200,B$66,Sheet11!$O$3:$O$200)
I want to fill cells below it and have the sheet number adjust, like this:
...

**1**

vote

**1**answer

24 views

### Excel 2010/2013 - Need to count number of doses of a medication given on the same date, to the same patient MR#

Okay, a colleague is trying to compile some dosage information. He has a spreadsheet containing Dates of Service, the patient's Medical Record #, and the name of the Medication. So, if Oxycodone ...

**0**

votes

**1**answer

31 views

### Select data from different sheets in LibreOffice Calc

I have three sheets 1,2 and 3. There are three columns on sheet 2 and 3: name (A), surname (B), age(C). How can I select men whose age is >=30 from sheet 2 and 3, order them by age and show the result ...

**0**

votes

**2**answers

27 views

### How can I filter out only those values that have appeared more than once in a column?

I have the following data to start with:
Sr. No Conf. By Prod
1 A X
2 B Y
3 A Z
4 B X
5 A X
6 ...

**3**

votes

**1**answer

23 views

### How to average ten cells below when rows are inserted

This is a question about Excel, not VBA. I have a simple formula in my top row that averages the ten cells below it. I want to copy the row below this one and insert a copy as a new row. (My range is ...

**0**

votes

**1**answer

13 views

### Formula that execute lookup on first match of a criteria

In cell C26, I want to use a formula to print the earliest date at which: Consolidated target > Total # of skus
Thanks in advance for your help!

**1**

vote

**2**answers

139 views

### Interpolate a collection of values to expand the number of points in the pattern in Excel

Suppose I have six cells (A1:A6), containing the values 1, 2, 4, 8, 16 and 32.
These values form a pattern, which I can graph. I want to expand the list of values to include intermediate ones ...

**1**

vote

**2**answers

181 views

### How to interpolate intermediate values for arbitrary data in Excel 2013

I have data tables like this example, nine entries at A1:B9 in this case:
A B
-- ---
1 2.9
2 5.06
3 7
4 8.84
5 10.87
6 13.24
7 16.22
8 20.25
9 36.7
The above ...

**0**

votes

**1**answer

14 views

### Trying to show in one cell the date at which a cash flow will become negative

I made a cash flow projections sheet with columns going from D to FE and Rows going from 1 to 65.
On the top of each column I have a row with dates from D8 to FE8.
On the bottom I have a row with ...

**0**

votes

**1**answer

45 views

### Can I have two LOOKUP functions within a single IF function in Excel?

I am attempting to organize a dashboard of sorts that will place the name of the appropriate owner in a column on the spreadsheet. The two criteria is the location or US state the item is located in ...

**0**

votes

**2**answers

24 views

### Excel Indirect #ref error [closed]

Trying to use the INDIRECT formula to pull a summary at the end of a large workbook. I have listed the Worksheet names in a single tab and now wish to use these to pull specific cell data into a ...

**2**

votes

**1**answer

34 views

### Adding Value to cells in Excel

For the spreadsheet pictured below, I am trying to create a formula that will calculate the amount of shirts ordered for each size, then calculate the cost of each shirt ordered for a balance (total) ...

**1**

vote

**0**answers

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

**0**

votes

**1**answer

29 views

### How to find table_array referenced in Excel formula in cell

I was given an Excel spreadsheet that I have to convert into a SQL view.
In the cell formulas VLOOKUP is used and for almost all of the table_arrays I can find where the table_array is. But for one ...

**0**

votes

**0**answers

14 views

### Worksheet Formula Changes

='Location (33)'!A35
I have 1200 worksheets to pull in, I want the worksheet number to increase but not change the cell number, I've tried to make the cell constant and the table properties to fill ...

**0**

votes

**2**answers

22 views

### Excel formula for a “running %” of a column

im trying to figure a formula for a running percentage of entries in a column.
For example, in column A, i have "r" for returns, and "n" for non returns, and "c" for canceled, but "c" is classed as a ...

**0**

votes

**0**answers

18 views

### Create a hyperlink to a cell in a “Table of Contents” sheet quickly

I have an excel document with a vast amount of sheets (upwards of 100). To make navigation easier, I made the first sheet a "Table of Contents" where I listed all of the other sheet names in a column ...

**0**

votes

**0**answers

23 views

### Excel formula needs help to create table

Trying to understand how to create a table that identifies what specific dates relate to the row and column it is in.
IE. if I have columns A, B, and C. and rows 1, 2, 3 with dates applied within ...

**0**

votes

**0**answers

10 views

### The data(Job #) in one column is only put to another spread sheet, IF another column contains data

J/C # A. B. C. D. E. F. G. H. TOTAL Estimated Time
97647 45 45 90 30 120 330
97651 15 30 45
99999 60 120 75 360 615
11111 ...

**0**

votes

**0**answers

20 views

### How do I stop column formulas from automatically calculating in an Excel Table?

When using a column formula for a table in a workbook with calculation set to manual, when tinkering with formulas, Excel calculates them if the column formula changes.
The automatic propagation of ...

**1**

vote

**2**answers

114 views

### Count coloured cells without a macro?

Is it possible to count cells by their colour (not by their cell value as they are blank) in Excel 2010/2013 without using VBA, as my files needs to be saved as .XLSX?

**0**

votes

**0**answers

49 views

### Vba Script to replace html tag with formatting

I am looking for a vba script, which replaces a html <strong>.
Example <strong>Any Text here shall be bold</strong>
The result shall be Any Text here shall be bold.
It shall macth ...

**0**

votes

**0**answers

44 views

### If 2 checkboxes true then add cell value - Excel Formula

I'm trying to add a list of prices depending on if 2 check boxes are ticked.
What I want to do is say if the paid checkbox is ticked and the just eat tickbox is ticked then add get the number in the ...

**1**

vote

**1**answer

41 views

### Change every character in a cell with another in Excel

I have a large list of phrases that I would like to make more disguised. I want to substitute each letter/character with another character and be able to decode this with another formula so I am able ...

**0**

votes

**1**answer

51 views

### Multiplying the remaining balance of an amount

I want to multiply the total of an array of cells, but only want to multiply the remaining number of the overage amount.
For example:
Goal amount: 10,000
Sum: 15,000
Multiply by: 5,000

**0**

votes

**2**answers

42 views

### How can I select and output the first and last row of a worksheet or a filtered list?

I am new to VBA and I have no clue where to start with this.
I have two columns of data:
Team Member Time
A 1
A 2
A 3
A 4
I am only interested in ...

**0**

votes

**2**answers

36 views

### Check if any names are present in two columns [duplicate]

I have two lists on names, first list of names just under 140k (Column A), second under just over 7k (Column B).
I want to see if there any matches from column A & B.
My Excel skills are basic ...

**1**

vote

**1**answer

35 views

### Look up a name from row in a timetable and output the column header in Excel

I'm making a timetable for interviews which comprise of 6 stations plus a rest.
(FYI in the timetable time is progressing downwards)
Sample of timetable with names omitted:
I'd like to create ...

**2**

votes

**0**answers

43 views

### Is there any way to include a comment in a referenced cell in Excel? [duplicate]

I have two Excel spreadsheets:
The master sheet which has tables of data
A second sheet which references certain cells from the master sheet in order to collate certain data.
I wanted to know if ...

**0**

votes

**1**answer

30 views

### I just cant figure out the formula for this excel sheet

i hope someone can help me with a formula for excel.
On my sheet i have on column A - D's for Dog, and C's for Cat, and O, for other.
I'm trying to figure out the formula for: If 8 or more of the ...

**0**

votes

**3**answers

85 views

### Parsing non-standard date/time data in an Excel spreadsheet cell

I'm looking for some advice on this one more than anything. I have a spreadsheet that contains store hours for each location at work. The formats range from this:
"Mon-Sat 9:30am-9:00pm
Sunday ...

**2**

votes

**1**answer

46 views

### Increase a cell's value based on other cell's value

What I am looking for is an "IF" formula that can solve my problem:
I have a single sheet with 2 columns and an unlimited number of rows.
On the first column (Column#1), I have a drop-down with ...

**3**

votes

**1**answer

45 views

### In Excel, how would I calculate the number of Mondays between dates that excludes a list of holidays?

I'm currently using this formula:
=INT((WEEKDAY(E2-2)-E2+E3)/7).
I know how to use the NETWORKDAYS function, which allows you to a specify a list of holiday dates, but I can't figure out how to ...

**1**

vote

**1**answer

30 views

### Retrive row number of MIN IF formula

I would like to know the row being used in a MIN(IF()) formula so I can use it to grab a another value in the same row..
I have this formula:
Between the two sheets
A is an Id
B is a Date
C is ...

**0**

votes

**1**answer

18 views

### transposing and collating text from different sheets into separate sheet

I am using the following formula to collate text from 3 sheets into a 4th.
=$'sheet 1'.A1&CHAR(10)&$'sheet 2'.A1&CHAR(10)&$'sheet 3'.A1&CHAR(10)
this collates the text with a ...

**1**

vote

**1**answer

33 views

### Applying text formula only if text is in the cell in Excel

I am using 4 Excel sheets for collating text.
The formula I currently have in sheet 4 is:
=$Sheet1!A1&CHAR(10)&$Sheet2!A1&CHAR(10)&$Sheet3!A1&CHAR(10)
This allows me to write ...

**-1**

votes

**1**answer

34 views

### Auto-populate calendar with an “X” in Excel

I have have a set of due dates for preventative maintenance on equipment and I want a calendar to auto-populate with an "X" under the correct month it is due.
For example I have machine 1 due 1/1. ...

**0**

votes

**1**answer

42 views

### How to use conditional formatting to search a specific value in a range in ms excel?

I have values in the range K5:K333. I want the value in cell S5 be searched in the range and the corresponding cells of the range being formatted.
I am using conditional formatting's "use a formula ...

**0**

votes

**2**answers

36 views

### If multiple cells are with the same text, return this text(value) to another cell

I need your help with this:
If A1:E1 is "Red", return "Red" to C3;
If A1:E1 is "Blue", return "Blue" to C3;
BUT
If A1:E1 are not the same, return False or "X" to C3
What function to use ...

**0**

votes

**1**answer

34 views

### Prefix/Suffix Data Validation List

I'm finding it really difficult to understand how to prefix or suffix a set of words based on a what I have chosen in a 'Data Validation List'.
CLICK HERE TO SEE/DOWNLOAD AN EXAMPLE SPREADSHEET
I ...

**3**

votes

**1**answer

127 views

### excel ISTEXT returns True where a formula contains the answer “”

I have a formula that returns the value "" blank.(correctly) However when testing for ISTEXT, I get the result "True" I understand "" is seen as text, however Im looking to test for actual words that ...

**3**

votes

**1**answer

79 views

### Excel Large Function with Duplicates

I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In ...

**0**

votes

**3**answers

55 views

### IF function with multiple criteria

I am trying to use an IF function to return a "1" if TRUE and a "0" if FALSE.
I need cell A1 or B1 AND C1 > 0 in order for the statement to be TRUE (return 1), else FALSE (return 0).
I cannot write ...

**0**

votes

**1**answer

46 views

### Change the cell value depends upon the values in the 8 neighborhood cells in Excel [closed]

My excel sheet contains 400 data points,its like a image pixel values,but some of my data points are missing so the value of that cell is zero,the data consist only four values 1,2,3 and 4. I need to ...

**1**

vote

**1**answer

64 views

### Make an array formula in excel calculate only on active rows

When excel calculates formulas over a column (e.g. a lookup), it's smart enough to only calculate until the last active row, rather than also running on all the blank rows.
With array formulas, it ...

**0**

votes

**1**answer

28 views

### sum values based on match criteria to another table

I would like to sum prices in one table based on a match criteria from another table
example table 1
Grocery Items Price
Apples $2
Oranges $3
Bread ...