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

**0**

votes

**1**answer

19 views

### VLOOKUP where the column to look for is a list?

I am doing a little format in which I call data from other cells, from other worksheet to fill it out. I am using VLOOKUP to do that.
So as an example I have:
Name: (Drop down list)
Sex
...

**0**

votes

**0**answers

10 views

### Excel performances: INDIRECT+ADDRESS VS CELL+OFFSET

Using Excel I can achieve the same result using two different approaches, here are the formulas:
A) =INDIRECT(ADDRESS(5+MATCH($A$16,Aliquote!$A$5:$A$15,0),1,1,1,"Aliquote"))
B) ...

**0**

votes

**1**answer

29 views

### Excel max number between 2 non-blank cells A, in column B (given no blanks in B)

I need to calculate the maximum number between 2 consecutive non-blank cells in A, but using the values in B. ie. use a formula in column C that will give me the results I manually plotted in D. ...

**0**

votes

**1**answer

22 views

### How can i create click function between A1 to A10 prompt inputbox for user to enter numbers in vba

I am trying to create input box for selecting range (A1 to A10), every time when i click any cells between 1 to 10, i want to prompt input box to enter numbers.
That's what i did but it's not working ...

**0**

votes

**0**answers

23 views

### How to combine two sheets based on their dates?

I have two sheets: one is stock index, one is air pollution value. I want to match the air pollution value to each trading day. How can I do it on Excel?
the first sheet looks like:
01/01/1997 39
...

**0**

votes

**1**answer

21 views

### Function excel for split a cell, vlookup into a column and concatena

I've one range with the value in column 1 and I want to look for the cells in Column 2 if they are in the range of col 1. When they are in the range I want the function to give me the value is col 2 ...

**-1**

votes

**0**answers

12 views

### Excel time and date query

I have an excel sheet where in column A has dates and column B has time, i tried IF function but in vain.
I want if the column A has same date in row then column B should not have the same time in ...

**3**

votes

**2**answers

37 views

### Round cell values on end of edit in Excel

I want to build a worksheet that when numbers are entered into a cell and the user ends the edit on that cell they are automatically rounded to the nearest 0.25. I do not want to have two columns, one ...

**24**

votes

**2**answers

129k views

### What is the Excel hotkey to re-calculate all formula in sheet?

I have a sheet that contains many formulas. But they do not recalculate automatically when I change input.
I'm looking for a hotkey, that can re-calculate the sheet. According to this page, F9 ...

**0**

votes

**2**answers

2k views

### Lookup Multiple Results for Multiple Criteria

I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs ...

**-1**

votes

**1**answer

34 views

### Formula issue Microsoft Excel

I need help urgently for a work problem.
So basically, the summation of opening stock and production give total stock.
While closing stock is ((opening stock+production)-sale)) and stock cover is ...

**-1**

votes

**0**answers

26 views

### Excel Sort multiple rows at once

How do I sort all the cells of each row in a worksheet?
I have 3 columns: Option 1 > Option 2 & Option 3;
Not all rows (60+) are sorted A, B, C
I can sort each row but due to the number of ...

**0**

votes

**1**answer

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

**1**

vote

**1**answer

30 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

7k views

### Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2).
My problem is the percent changes from -4 to +4.
A change ...

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

**0**

votes

**1**answer

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

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

**1**

vote

**2**answers

33 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

38 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

31 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

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

**0**

votes

**1**answer

17 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

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

**1**

vote

**4**answers

31k views

### Extracting Data Between two Characters in Excel

I have the following format in Excel and I am trying to extract the data between the characters:
First Last <flastname@xxxx.com>
I need to extract the email address between < and > into ...

**0**

votes

**1**answer

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

**0**

votes

**2**answers

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

**0**

votes

**2**answers

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

**1**

vote

**2**answers

50 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

**2**answers

3k views

### Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula.
problem is the rota is written using a number of different notations for different shifts
some are ...

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

**2**

votes

**4**answers

61 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

75 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

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

**4**

votes

**4**answers

221 views

### Showing whether a list in Excel has a 1 or a 0 in it

So I have a column in excel that I want to roll up into one row. Essentially the column has a 0 if a feature isn't present, and a 1 if it is.
What I want to do is get excel to check a column, lets ...

**1**

vote

**1**answer

22 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**

vote

**1**answer

67 views

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

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

**19**

votes

**6**answers

31k views

### Type math formulas in Microsoft Word the LaTeX way?

I wonder if there is some free solutions for typing math formulas the LaTeX way in Microsoft Word document (Office 2007)?

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

**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&": ...

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

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

**0**answers

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

**2**

votes

**1**answer

52k views

### Can Excel use formula-input data for cell references?

I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell.
For example:
A1=5
A2=10
A3=insert syntax here, using A4 as source for cell ...

**0**

votes

**1**answer

420 views

### Excel: Matching and counting records (rows) to multiple conditions and unique values

I have two sheets: ‘Data’ and ‘Report’. In the report sheet, I need to calculate some data which requires 3 different formulae.
The Data sheet has column headers in row 1 and many records of data in ...

**0**

votes

**2**answers

2k views

### Calculate different discount levels in Excel

I have to create a price list with 3 different discount levels called eg. E1, E2, E3.
E1 = 10%, E2 = 20% and E3 =30% discount.
How do I write a formula to tell Excel if the item falls in class E1 it ...

**0**

votes

**1**answer

2k views

### Combine duplicates in Excel, sum some columns and weighted average the others

This question refers to an existing subjexct on our site. I need a variation on this solution. I have a spreadsheet with 8 data columns. The number of records can vary from a few hundred to about ...

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