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

**0**

votes

**0**answers

4 views

### Excel finding similair text in different sheets

Is there a way to use a formula for a range of cells with text?
I mean that i have two separate Sheets, here is the example to understand:
Table
What I need to do, is to make a formula for YES or NO ...

**0**

votes

**1**answer

22 views

### Time Elapsed In Hours Between Multiple Dates In Excel

I am trying to find a formula that works out the business hours between two dates in excel.
The business hours start at 09:00 and end at 17:00 and do not include weekends.
The spreadsheet is ...

**0**

votes

**1**answer

7 views

### Dynamically reference name manager names in excel

I have survey data and I want to look at respondent data by country of origin. I have 1 column of indicator variables per country and I've used Name Manager to define each country's name (e.g. China =...

**0**

votes

**1**answer

20 views

### using an Array formula to do VLOOKUP in multiple sheets

I have used below formula in this file but I do not know why it doesn not work and return #N/A!? Infact I need to lookup "Station .No" from other sheets to display it in Sheet1 :
= VLOOKUP(I3,...

**-1**

votes

**0**answers

20 views

### this subtraction function achieveable in excel?

I have 4 cells that I am working with (A1, B1, C1, D1).
C1 is set where it'll automatically subtract B1 from A1 (=A1-B1). We also have to subtract D1 from B1 (=B1-D1) but we have to manually grab a ...

**-2**

votes

**0**answers

18 views

### Difference between two rows of one column, add new row in between if different. (CSV)

I have multiple CSV files with similar data. which is retrieved from an automation module, I want to compare two adjacent Time Stamps and if they are less than 15 min apart add a new time stamp (row) ...

**0**

votes

**0**answers

21 views

### Excel 2013 single column values multiple worksheet changes

I have been tasked at work with creating a calendar template that can be used by multiple groups with a standard set of variables & color coding. Okay, not a big deal since Excel has some ...

**1**

vote

**1**answer

17 views

### How to increase all values at once in Excel (about 15K)

Lets just say that the values currently are
1
1
1
1
1
3
5
4
I want to add 1 to all the numbers so that the result is
2
2
2
2
2
4
6
5

**2**

votes

**2**answers

30 views

### Combine multiple email addresses (in separate cells) into one cell (separated by comma) Google Sheets or Excel

I'm trying to combine multiple cells with email addresses into one cell where emails are separated by commas.
I've used this formula:
=P2 & ", " & Q2 & ", " & R2 & ", " & AD2 ...

**0**

votes

**1**answer

29 views

### Using indirect function within an Index formula, when the data is on a separate worksheet

This formula uses tabulated data on tab antistatic.
=INDEX(apestatic!$E$42:$L$2140,MATCH('Graphed data'!$E4,apestatic!$E$42:$E$2140,0),MATCH(H$3,apestatic!$E$42:$L$42,0))
Along the left of the ...

**0**

votes

**1**answer

21 views

### How to find time difference between two cell in excel sheet?

"14/07/2016 09:26:59" & "14/07/2016 09:28:47" I could not find difference between this time. I have enter formula =A2-A1 result shows "#VALUE!" Please help me.

**0**

votes

**0**answers

25 views

### EXCEL. An array that displays the most recent set of data(based on date) of a customer

I'm looking to pull data from one sheet into another sheet where I pick the most recent date of information on a person. So I may have 50 entries for Bob, but on sheet 2 I just want to post the most ...

**1**

vote

**0**answers

27 views

### Conditional formatting with several conditions depending on different cells

I currently have a table in excel that looks like
.
The table shows four projects, each with three stages with each having an expected and actual start date for that stage.
I want to create a ...

**0**

votes

**1**answer

22 views

### Excel - How to combine a hyperlink cell and text cell?

My requirement is I have one cell with text and the next cell with a hyperlink. I need to add only the hyperlink to the text. Any ideas on how this can be achieved? I want to automate or use a formula ...

**0**

votes

**1**answer

18 views

### Clearing a value when an option is selected from a drop down menu in Excel (VBA)

Good Morning,
I'm trying to write a code in VBA, that while selecting from a drop down menu from D2, it will clear any value from E2 and F2 without removing the formatting.
I have tried the follow ...

**5**

votes

**1**answer

47 views

### Limitations to the Excel INDIRECT() Function

If I have numerical data in A1 and A2 then:
=SUM(A1:A2)
works and:
=SUM(INDIRECT("A1:A2"))
works and:
=SUM(A1,A2)
works but:
=SUM(INDIRECT("A1,A2"))
does not work. I am guessing that this ...

**0**

votes

**0**answers

29 views

### How to check the values are increasing or decreasing between different columns?

In the table below each row has a different behavior. Note that, In the first row, the columns "pool1" the values are decreasing. Also decreasing for columns "pool2" and "pool3".
In the second row ...

**-1**

votes

**1**answer

12 views

### IF there is the value of I6 in range C170:C999, then set D6 to value of B at index whereever I6 matches C170:C999

I tried for 3 hours now but can't find a solution to my problem.
I have to set D6 to the value of the cell G on line where I6 and C170:C999 match.
For example I6 matches C200, then D6 should be set to ...

**0**

votes

**1**answer

19 views

### Excel offsets to suit complex autofill scenario

Having some trouble with off setting cells in order to use the auto fill option in excel rather than entering cell formula manually which is what I'm doing at the moment.
Here is an example of what ...

**0**

votes

**1**answer

15 views

### Need help tagging in excel

I am trying to sort this data in the best way I can, column B cells exist in multiple categories under column A. Therefore I would like to tag all the column B cells with their corresponding column A ...

**0**

votes

**1**answer

35 views

### How to replace an array excel formula with another regular one to avoid long calculation time

EXAMPLE
by using an array formulas in range (E11:E16) According to the above example:
To count unique customers in range (E2:E7) that meet only the following criteria:
1- Purchased Product 1, ...

**2**

votes

**0**answers

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

**0**

votes

**0**answers

14 views

### Excel: Add new rows to a table with a subtotal row, when data added to a specific column on the last row

How do I get an Excel table (with a subtotal row) to automatically add a new row when I enter data in column C of the last row?
At the bottom-right of the table I have 3 cells with basic SUM formulae,...

**0**

votes

**1**answer

11 views

### Creating a For All function in Excel

This might be hard to explain, but I am trying to create implement a formula that compares a bunch of values of "TRUE" or "FALSE" and then displays an output a corresponding cell that states if at ...

**0**

votes

**1**answer

9 views

### Excel formula: Add entries in same month and year

This seems like it should have a fairly simple solution, but I can't quite find it. Any help would be much appreciated.
I'm trying to write a formula in Excel that will refer to an itemized list of ...

**0**

votes

**0**answers

9 views

### Array formula behaviour inconsistent?

I am trying to use a sum with an array formula to be able to make a "sumproductif" :
my old formula :
=SUMIFS(OFFSET($B$2:$B$1915;0;$Z$22);$L$2:$L$1915;$Y$3;$B$2:$B$1915;X4)
I replaced it with
{=...

**0**

votes

**2**answers

20 views

### Is there a way to add together an arbitrary number of cells based on a specific criteria in Excel?

So I have a spreadsheet with a bunch of little mini-charts as seen below. The numbers in blue are user entered, and they are all added together in the chart with the gray cells on the top left.
Right ...

**0**

votes

**0**answers

41 views

### Getting data from same row as matched cell on separate sheet

I need to match A2 with Sheet2!A:A, but the value I need is a cell on Sheet2 in the same row as the match.
Let's say A2 matches Sheet2!A20. I need the formula to return what's in Sheet2!E20.
I ...

**0**

votes

**0**answers

15 views

### Defining a range in excel for index/match [on hold]

I'm trying to return a "best" value, but there are two criteria for a threading calc. One column has stress, which just has to be higher than a given value, and the other would be where you want the ...

**0**

votes

**0**answers

22 views

### Excel Power User Challenge - Advanced Workbook Split [closed]

I need to split a workbook's multiple worksheets into multiple files (not based on worksheet name). Sounds easy, but just wait.
The project:
It deals with very sensitive HR/performance data, and I ...

**-1**

votes

**0**answers

9 views

### Excel - Data Fill from another sheet in certain column if value is right

I want to copy certain information from a line in another sheet if in a specific column it shows a value different from Work Done.
Say in column F it has a value different from Work Done, then I want ...

**-2**

votes

**0**answers

11 views

### Excel pivot table grouping

I'm using excel 2010 and I want to create a pivot table that shows times between breakdowns in groups of 1 hour. The problem is that no matter what I try I get the cannot group selection error. I ...

**-2**

votes

**0**answers

15 views

### How to automatically transfer Excel data with no fixed cell reference to a new worksheet or workbook with specified fields

How do I AUTOMATICALLY transfer mixed data to go to specific fields in a new worksheet or workbook.
All the Excel data are on separate worksheets.
The columns and rows are not similar.
The lables ...

**-2**

votes

**0**answers

53 views

### Compare and group rows based on the sequence of their cell values in Excel 2013

I have one worksheet written to show packing contents for different products. This is how it is set up; Column A includes model numbers. Photos and part numbers are included in columns B through AH. ...

**0**

votes

**2**answers

42 views

### Excel Formula Shows 0 and not null

I am using VLOOKUP() to populate a few cells, but when the value does not exist in the lookup table, I want the cell to show the original content, which is usually nothing. However, when the cell has ...

**0**

votes

**0**answers

44 views

### without iteration condition, =A1+C1 in C1 cell,

I've tried iteration condition, but it is not taking only the 'A1' value, if I put some value in some other cell, C1 is calculating like =A1+C1 in C1 cell,means
A1 value is 5, in C1 i put this farmula ...

**0**

votes

**3**answers

47 views

### Excel: sum values corresponding to multiple matching or individual numbers

hoping someone can help with this one:
The numbers on the left (phone numbers) will be either individual or repeated.
Each row has a number in the second column
that is associated with that phone ...

**0**

votes

**1**answer

18 views

### How do I return the last word of a string of text?

If any of you have been following the progression of my questions, then this may come as no surprise. Be that as it may, I have to ask it. I really did attempt to find an answer to this online and ...

**0**

votes

**1**answer

16 views

### Ticket report - Auto populate from other worksheets into specific form

Hello ladies and gentlemen, I'm quite a dummy in excel and programming in general and I have a question related to building a report:
I must present a ticket report of which tickets are opened, how ...

**1**

vote

**1**answer

22 views

### Excel Get minimum value, and If two cells match from a row, return values from the consecutive 3 columns

Here's a simple explanation of what I'm having trouble with.
Get minimum value, and If two cells match from a row, return values from the consecutive 3 columns.
SUPPLIER A
F4: PRICES SUPPLIER A
...

**-1**

votes

**1**answer

22 views

### Need to filter out some names from a list of Total names

I have a list of names.
I need to separate the names which contains characters g,j,p,q,y.
The names can start with G,J,P,Q,Y i.e the characters as first letter. But the characters g,j,p,q,y shouldn't ...

**0**

votes

**1**answer

19 views

### How can I remove a cell from a range for a property lookup array

Lets say I have a value, A123456, in a column A, row 1, and i want to check if I have the same value in other rows of the same column, regardless of the length of the values as long as it has the same ...

**0**

votes

**1**answer

17 views

### How do you return the last letter in a string of text in an adjacent column?

Rather than do 600+ entries manually before seeing if some superuser might be able to answer my question, I'm going straight to "the Oracle" first this time. By the way, @Scott Craner answered my ...

**-1**

votes

**1**answer

31 views

### figuring Golf Handicap

Presently we use the following formula and it works great:
=IF(COUNTIF(D4:AB4,">0")<3,C4,IF((((SUM(D4:AB4)-(COUNTIF(D4:AB4,">0")*36))/COUNTIF(D4:AB4,">0")))*0.9>20,20,(((SUM(D4:AB4)-(...

**-1**

votes

**0**answers

14 views

### How do I transfer data values from a chart to a table on Excel?

I have data that is listed in a chart, for example
Hello1
World2
Biscuit3
I would like to transfer this to a data table, but the numbers follow a specific pattern. For example, the table would be:
...

**0**

votes

**0**answers

13 views

### Excel, Populating a cell based on what in types in another cell

I am currently working with excel and I am having a bit of difficulty.
I will try to say basically what I am doing and what I would like to do.
I am making a sheet that I would like to be able to ...

**1**

vote

**1**answer

32 views

### Can't make the import sheets from directory function dynamic?

Can I modify the code below into a dynamic import of sheets,
so I can run it in any sheet from my personal macrobook instead of a sheet called import-sheets.xls?
Found the code for importing below.
...

**-1**

votes

**0**answers

33 views

### Excel External Referencing Across Work Network

I've invested a large amount of my time over the past week to building a list of document types and descriptions into a series of Excel workbooks which rely heavily on external referencing (to each ...

**0**

votes

**1**answer

33 views

### Excel: How to highlight highest value cell where duplicate in another column

I have a report containing a log of members details and any actions carried out on their account.
As it is a log, there are duplicate membership_numbers in column A. Column B is the action on account ...

**0**

votes

**1**answer

16 views

### How do I set up a formula that will return a certain value dependent upon a portion of text in a cell?

I've got a column of text. If the text of the cell contains
el
I want it to return "M" in the adjacent column. If the cell contains
la
instead, I want it to return "F" in the adjacent column.
...