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

**0**

votes

**1**answer

15 views

### Convert string Jan to January, Feb to February, Mar to March etc

I would like to be able to convert the string value Jan to January, Feb to February etc but I don't know how.
I have a table, populated some how, with the months, which looks like
...

**0**

votes

**0**answers

18 views

### All Data Option Filter Validation

I am creating a dashboard that has five inputs that filters my data by the custom data validation combination. My dashboard updates correctly based on the custom combinations, but I am unable to add a ...

**1**

vote

**1**answer

27 views

### Copying cells to another sheet and inserting rows for the copied cells

I am trying to create a sub that copies information from one form (which has four cells) to another sheet.
When it copies the information it also creates a new row.
Each form has a maximum of ten ...

**0**

votes

**1**answer

15 views

### How to compare differences for multiple possible pairs of cells in Excel

In Excel, I have 7 columns of data (numbers) spanning 100 rows. For each row, I want to check whether at least one number exceeds all other values by a certain value (say 0.2 or greater). If so, the ...

**0**

votes

**1**answer

31 views

### How to use INDIRECT with a SUMIF formula for a date range

I'm trying to get a SUMIFS formula to dynamically work with multiple tabs in an excel workbook. I have numerous tabs in this workbook, each that contains client data I update daily. In the example ...

**1**

vote

**2**answers

31 views

### Excel Formula difference between two dates

I have two dates as the following , I want to get the duration spent between them.
6/29/2015 12:18:04 PM
Regards,

**1**

vote

**1**answer

34 views

### Excel: Write a 1 or 0 value if condition is met

Sorry if this has already been posted, but I just started today playing with Excel macros and I need a hand. I have a data column with values and I would like that if the conditions were met, the ...

**0**

votes

**1**answer

29 views

### Excel: Is it possible to copy cells only if cells match in other columns?

If it's an EXACT duplicate, not just some answer that MAY suggest an answer then please provide the link in a comment.
Say, I have dates in column A of Sheet-1, and also dates in column A of Sheet-2. ...

**0**

votes

**1**answer

12 views

### How to copy tab-contained text from Excel without quotes?

I've noticed that a cell that contains a tab is copied with quotes embedded. I'm looking for copying such kind of a cell without the quotes.
Illustrative Example
I've 5 cells; A1, A2, A3, A4, and ...

**0**

votes

**1**answer

14 views

### Excel Formula change and remove

The numbers on the form are for two different references. 2015-##### (five digits after year) is the way it will be entered. When the user enters that I would like to enter in another cell the result ...

**0**

votes

**0**answers

10 views

### Using Hlookup to populate a worksheet from a data sheet

I am trying to develop a worksheet that requires a list of equipment based on an area header. I would like to be able to pull the separate lists based on this area header.
Here is sample data I am ...

**0**

votes

**1**answer

17 views

### Google Spreadsheet =Filter in excel

I have this formula in google spreadsheet that uses the =FILTER functionality but I can't seem to find the corresponding syntax in excel. Specifically the formula is as follows:
...

**1**

vote

**2**answers

49 views

### Need a formula to check the first x amount of characters if numbers

Is there a formula to check if the first 7 characters in the cell are numbers?

**0**

votes

**1**answer

41 views

### Is there a way to exclude keywords in a VLOOKUP formula?

I have 3 sheets in Excel. In the 1st one there is a column with hotel names and another one with ID. I want to get that ID from the ID column on the 2nd sheet where I have the names as well.
The ...

**2**

votes

**4**answers

59 views

### How to apply a formula to an entire Excel column using a keyboard missing its END key

I have a keyboard with a trackpad that's missing some of the normal keys available on a full-sized keyboard (among them Home and End).
Also, there are 30,000 rows in the sheet and dragging the corner ...

**0**

votes

**2**answers

69 views

### How can I compare two columns and if match return a value?

How Can I compare the entries on TAB: SUMMARY SLS, column B with SUMMARY P&S, column B and bring back a "X" if a match is found in the cell with the formula? SUMMARY SLS and SUMMARY P&S are ...

**0**

votes

**1**answer

21 views

### Find cell address for MAX+IF function based on category

I have a large set of data, which has been divided into 9 categories using numbers 1-9. The data is not sorted by category, but the categories are defined in a column as data. Using MAX+IF, I have a ...

**0**

votes

**2**answers

19 views

### Assign category to data based on range

In Excel, how would I take numbers from one column and assign them to a category in another? E.g., if I set ranges that correspond to certain words 1-10=Beginner, 11-20=Intermediate, etc. How would I ...

**-1**

votes

**1**answer

26 views

### Error in SUMIFS [closed]

I have the following data
--- A B C
1 Name Form Group
2 John 7A 1
3 Sarah 7B 1
4 Lucy 7A 1
And I want to sum all the values in ...

**1**

vote

**1**answer

38 views

### If statement that returns original function if false (similar to iferror, or substitute)

If I use IFERROR as opposed to IF(ISERROR I can save typing the condition twice.
i.e.
=IFERROR(Long lookup,"not found")
is much preferable to
=IF(ISERROR(long_lookup),"not found",long_lookup)
...

**1**

vote

**1**answer

60 views

### Compare Two Dates and If Less than TODAY Add Date till TODAY and Add 6 Months to it

I have data like this in
Column H
6-Feb-14
5-Oct-14
27-Dec-14
5-Feb-15
17-Feb-15
2-Apr-15
I'm trying to add 6 months to each date with the date being up to today.
Like if ...

**1**

vote

**1**answer

20 views

### fit a table in a middle of an excel sheet

I have a table in a sheet like this:
I want to place the table in the middle of page. but the number of column is odd (5) and the number of column in the sheet is even (8). there is 2 cols after ...

**-1**

votes

**1**answer

26 views

### BOM comparison sheet

I have been working on Comparison sheet, where i need to compare Base BOM1 with other BOMS. I added formula & prepared the attached sheet & I am able to highlight the mismatch values. But i ...

**0**

votes

**2**answers

43 views

### Populate missing entries from one sheet to another

I have two Sheets in Excel. For this example, Sheet 1 and Sheet 2.
Sheet 1:
100 records with Name, Location, Gender
Sheet 2:
100 records with Name, Location, Age
The sources of the data are not ...

**0**

votes

**0**answers

19 views

### Search for ID column in all worksheets and then search within those columns

I have multiple workbooks with multiple sheets on each.
I am looking for a way to return the value under the column ID#, like to search all the sheets in the workbook. The header ID# might be in a ...

**0**

votes

**1**answer

39 views

### Modify Concatenate Formula to add an additional column (Mac Excel 2011)

I would like to modify this formula to ADD another column:
=IF(LEN(AK2),AK$1&": "&AK2,"")&IF(AND(LEN(AK2&AL2)>0,COUNTA(AK2:AL2)=2)," _
& ","")&IF(LEN(AL2),AL$1&": ...

**1**

vote

**0**answers

20 views

### How to quickly reference the same cell across different sheets [duplicate]

I have 60 Sheets with the same information but for different employees. I was wanting to create a summary sheet but without manually linking.
In the summary sheet, I want cell A6 from each sheet to ...

**0**

votes

**1**answer

35 views

### Excel formula for checking if a string is a substring of another string and if so, creating a hyperlink

So I've been working on this problem at my office now for awhile. I have an excel file with a few thousand lines representing files that the office has either opened, scanned in, closed or destroyed. ...

**-2**

votes

**0**answers

22 views

### Multiple Function [closed]

I need a similarity function. But I need Column A, row 1 to be an assigned number (i.e. 11013) which is then spread among many worksheets where A2,A3, A4 usually show differing results dependent on ...

**0**

votes

**0**answers

36 views

### Lookup ProductID on another sheet and return the lowest cost and its column header

I have recently been working with formulas and larger amounts of data than usual and need a little help. What I am trying to accomplish is the following.
I have two workbooks (which I could make ...

**-1**

votes

**0**answers

19 views

### Can you tell me what this formula means =P19*(1+40%) [closed]

Can you tell me what this formula means =P19*(1+40%)

**0**

votes

**2**answers

48 views

### EXCEL nested IF() statements [closed]

I'm working on a linguistic-based spreadsheet and I'm quite lost with excel formulas.
Here is a tiny list of what I am working on:
A B C
ábaco nm ocabá
ábaco ...

**0**

votes

**1**answer

35 views

### Excel formula from VBA difficulty

I have a formula that I want to generate in VBA and put in a cell. The final formula is:
=IF(ISNA(MATCH(B6670,ProcMIRs!B1:B235,0)),"","Found in ProcMIRs row " & MATCH(B6670,ProcMIRs!B1:B235,0))
...

**0**

votes

**2**answers

32 views

### Make formula depend on key word entered in another cell

I would like to know how to get a set of cells to auto generate a specific date when another cell has been entered with one of four specific key words. (Key words: AFAM, AFCM, MSM, MSM/FW)
Ex. If ...

**0**

votes

**3**answers

46 views

### Adding a date to a formula in excel [closed]

=$M2="COMPLETED"
I am using this formula to shade a row when COMPLETED is written in column M, but I would prefer the 'date' or 'COMPLETED and the date' but can't seem to work out how to do this.
...

**0**

votes

**1**answer

14 views

### COUNTIF/COUNT Function help required to count the filtered data for the subsequent columns

I am using a normal COUNTIF Function to know the count of "Box 9" I have placed this in one cell "I12" and i am using the formula.
I am filtering the data with the column J.
1)
...

**0**

votes

**1**answer

16 views

### Excel 2010 Array (CSE) Formula: How to remove a part of contiguous (adjacent) range

I would really appreciate help on the following question:
Is there a way to write a formula that removes a segment from a contiguous range?
For example, say there is a formula ...

**-1**

votes

**1**answer

35 views

### Excel formula to Show (1,0) for specific ranges

The problem I am having is I only want to look at Order number(Column A) if they have more than 4 value. When they have 4 values I want to only look at the last 4 values in that order. In column D I ...

**1**

vote

**1**answer

19 views

### How do I create a summary of the same few cells on many different sheets in Excel 2013?

I have an Excel workbook with 140 sheets. I'm trying to summarize the same 3 cells in each sheet on a summary sheet. How do I grab the value of the respective cells on each sheet without manually ...

**1**

vote

**2**answers

44 views

### Excel 2010 Array Formula (CSE) Dynamic cell range/reference; index? offset?

This might be an easy question to some but I am completely lost so I would really appreciate any help out there!
A
1 10
2 20
3 30
4 40
5 50
6 60
7 70
...

**0**

votes

**1**answer

13 views

### Counting in Excel until a special integer comes up [on hold]

I am trying to get column C to say what column D says (I have typed the value in column D). The problem I am having is that I can not find a way to tell Excel to stop counting when it comes across the ...

**-1**

votes

**1**answer

24 views

### Formatting - conditional - vlookup- values from one cell to another different worksheets based on value

I would like to be able to enter a part# in column L of sheet 2 and a formula would look through column A of sheet 1 for a match, and then return their price from column F of sheet 1 into column K of ...

**1**

vote

**2**answers

26 views

### openoffice calc: sum of a function of elements of a row

If I want to sum the elements of the first row (from the column a to x) of a calc document I can use =sum(a1:x1).
My question is about the possibility to calculate something like this
...

**0**

votes

**1**answer

40 views

### How do I Reference all non-blank cells after row A6 for a Formula

I want to use the following formula:
INDEX(A6:A94,MODE(MATCH(A6:A94,A6:A94,0)))
Except the exact number of rows is ambiguous (imported using VBA).
How can I reference all non-blank cells starting ...

**0**

votes

**0**answers

28 views

### Can someone help me with an excel function that would make merging sheets easier?

I have about 61 spreadsheets that I want to merge into one big workbook.
Int the one work book I have tabs that are named the same as the spreadsheets that I want to import the data into.
There ...

**0**

votes

**2**answers

38 views

### Excel function to find the last three items by date

I am looking to obtain the 3 most recent transactions in relation to the date. (The problem is that they are in the spreadsheet out of order,
and I don't want to do a sort.)
Date Amount
...

**0**

votes

**2**answers

28 views

### Prevent formula input in all cells in a column, but allow whole number input

I am in a bit of a sticky situation. There is a public spreadsheet that some of my coworkers are required to use for their job. They all collectively use it during their shifts to track product ...

**0**

votes

**1**answer

48 views

### Why is this IF, OR, AND function not returning TRUE?

Why is this IF, OR, AND function not returning TRUE in A848?
See image below to view screen shot.
I'm using this algorithm to search for " not " and " valid", "not valid", and "invalid" in the ...

**1**

vote

**1**answer

50 views

### Work out Number of hours still to Complete [closed]

I have a spreadsheet to keep track of training which I do. As part of the requirement, I have been given a set number of hours to complete per month (20 hours).
The formula I require is to work out ...

**1**

vote

**1**answer

60 views

### Formula to show date of the last day of the month specified in another column in Excel

For a month number in column A, I want the LAST day of that particular month in column B. Right now, I'm working with year 2014, so:
A1 number is 1 so B1 date is 01/31/14
A2 number is 2 so B2 date ...