The 2010 version of Microsoft's spreadsheet application.

**1**

vote

**1**answer

10 views

### Index match function to return range category in Excel 2010

I have defined categories in columns on which the values represent the ranges for each item (rows).
By using the query (column G), I want to know in which category the item ("yellow", 60) falls into. ...

**0**

votes

**1**answer

13 views

### Formula to extract the string between the Nth square brackets

Cell A1, A2, A3, etc contain strings similar to the following...
1 x Product A [SG014] 1 x Product B [SG026] 1 x Product C [SG023]
Each cell in column A could be a string of up to 20 different ...

**-1**

votes

**2**answers

39 views

### if greater than zero copy from the range

I have data sheet with "product" list in range B38:B161 and "to order" column in range I38:I161 which will be updated manually. So when cells in range "I" is updated with number to order, the product ...

**1**

vote

**2**answers

25 views

### How do I identify duplicate rows in an Excel cell?

I have an excel file with thousands of rows of contact info.
I need to get a list of all the rows that have duplicate first and last names.
I have been able to use conditional formatting to ...

**0**

votes

**1**answer

24 views

### Display prefix units in excel cell

I have a specific cell in my excel sheet that contains a value from 0-100 (The value can be changed by the user). Whose value is used in some formulas at different places in the sheet.
I would like ...

**-4**

votes

**1**answer

41 views

### Formula to find the upper case in the sentence as true [on hold]

i have one worksheet which has a sentence in-between uppercase words, i want to know which cells has a uppercase word in the sentence. Please someone help me out with the right formula to find.
I ...

**-1**

votes

**0**answers

27 views

### How to stop Excel from splitting data onto several rows

Secenaro .. have an CSV file in one of the blocks of data their are several values e.g. Other, Mobile, Data etc.... which is the results of a query dump from field that has "multi-select" options.
...

**1**

vote

**1**answer

30 views

### Excel Copy Formulas Horizontally

On Row 1, Column A I have =Sheet!1K2, on Column B I have =Sheet!1K3, Column C =Sheet!1K3 and so on.
On Row 2, Column A I Have =Sheet!1L2, on Column B I have =Sheet!1L3, Column C =Sheet!1L3 and so on.
...

**0**

votes

**2**answers

35 views

### How to find time difference between two rows of one column in excel sheet?

I have an excel sheet containing the Entry and Exit time for users from bio metric software. I want to find how much time the user had stayed i.e. sum of difference between entry and exit times, but ...

**0**

votes

**1**answer

42 views

### Excel formula to calculate the total time spent on various tasks [on hold]

I need a formula to calculate the total time spent on various tasks but I want the total splitting across the relevant interval each day.
For example, column A has the start time and column B has the ...

**-2**

votes

**0**answers

29 views

### Macro (Need help writing) in excel 2010

I am trying to write a macro that will compare column A and column B. I need it to look at the first number on A and see if there is a match on B. If there is I need it to delete the cell in A and B. ...

**2**

votes

**0**answers

23 views

### Date types management mismatch (2-digits year) - on copy/paste user action

I find a strange inconsistency when copying-pasting dates on MS Excel, when they have a 2 digits year.
I work on a machine Windows 7 Enterprise 64 bit and my Excel is a 2010 version.
PROBLEM ...

**0**

votes

**1**answer

33 views

### How do I create a table with multiple header rows

How can I create a table in Excel that has two or more headers rows. I want to keep the range as a table so I can use the built in tools such as re-sizing, totaling, formatting, etc...
I saw a ...

**1**

vote

**1**answer

26 views

### Conditional Formatting on a Specific Character only

I have the following Excel table :
> N
1 Open / Closed
2 O
3 C
4 O
5 O
6 C
I want to put a Conditional Formatting, putting in GREEN ...

**0**

votes

**0**answers

11 views

### Why does my Excel 2010 file add rows to the bottom of one of my sheets?

I have an excel 2010 file that continually adds extra lines to the bottom of the one of the sheets when I cut and insert rows from the top to the bottom. I have two sheets, one works just fine and ...

**1**

vote

**0**answers

21 views

### Calculated Fields Reference Error (#NAME?) on Pivot Table Refresh

I'm helping a colleague fix a spreadsheet with a similar issue as stated in this ozgrid post.
The problem: Pivot table with 31 calculated fields. Some calculated fields are calculations of the source ...

**0**

votes

**0**answers

13 views

### Accessing locally stored external data

I have an excel file that has an external connection in it to a SQL server DB. The external data from the last refresh is stored in Excel somewhere as i can create new pivot tables from that data set ...

**3**

votes

**1**answer

130 views

### How to automatically colour cell background with value selected from custom dropdown list in MS Excel 2010

In MS Excel 2010 Professional is there a way to automatically copy the background colour of the custom dropdown values from somewhere else in a spreadsheet?
I have some Data Validation on a row of ...

**1**

vote

**1**answer

14 views

### Excel Stuck in Developer 'Design Mode'

We have a set of spreadsheets that contain scrollbars added in developer mode. Clicking these scrollbars changes a value and a diagram in the spreadsheet.
Suddenly these do not work. The mouse cursor ...

**-2**

votes

**0**answers

13 views

### macro to collect data from number of sheets to main page and need to add the sheet name that where the data collected

Please help me..,I need a macro to collect data(Student names) form number of Sheets(Class Name).all student names collected from sheet1, then a new column added with the sheet name (i mean ...

**-2**

votes

**0**answers

28 views

### please make excel sheet [closed]

Suppose you have two Excel Sheets as follows and it’s more than 1000 rows and you cannot do manually:
Sheet 1, Available List:
Area tower Br# Siza Rent Sale Height ...

**0**

votes

**0**answers

19 views

### Using Chart BeforeDoubleClick with a chart object in worksheet

I am trying to get a macro to work on a chart in a worksheet using the Chart.BeforeDoubleClick event.
I have been able to use BeforeDoubleClick event (without the use of class modules) in a ...

**4**

votes

**4**answers

154 views

### Comparing the first part of a string in Excel

I'm trying to create an automated function that will determine the class identification for IP addresses after you input it into Column A's input section by checking the first section of the address.
...

**1**

vote

**3**answers

273 views

### Can't click on ActiveX control in Excel 2010 Standard

I have a .xlsm file that has ActiveX control button with a module macro assigned to it. Problem is I can't click the ActiveX button from my Excel 2010 Standard. I tried to repair and reinstall Excel ...

**0**

votes

**0**answers

10 views

### Oracle connection/Pivot tables

I'm using Microsoft ODBC for Oracle Connection to connect on Oracle 11g database through Pivot tables.
It works fine with old version of Excel (i.e. 2003) but when I'm saving file as 2010 .xlsx my ...

**0**

votes

**1**answer

26 views

### Adding Index to Match formula

I have the formula below working correctly, it is returning the max value in column G when the other conditions apply (valumes in B and F). However now I know what the Max number I want to return the ...

**0**

votes

**0**answers

11 views

### My image wont show up when I insert picture from file in Excel

I am trying to insert a picture into an excel spreadsheet.
I follow the normal procedures, but when it brings it into the excel sheet it either says "Image cannot be displayed" or shows up a clear ...

**0**

votes

**0**answers

10 views

### Why does Access return “Conversion Type Failure” if I'm importing text into… text?

For some reason, no matter what, I get a "Type Conversion Failure" (found in a generated log-table) after importing data from an Excel spreadsheet. It's only happening to a few columns that have ...

**0**

votes

**0**answers

10 views

### Keeping cells in the same row lined up when columns in the same row have 2 different data sources

I am working on a worksheet that references data on a different worksheet (within the same workbook). Each row in the referenced worksheet represents an order. Data from the referenced worksheet ...

**1**

vote

**1**answer

27 views

### Excel return a list of stringe where 'TRUE'

Ok so I have couple of cells listing warnings:
A B C
1 Warning1 TRUE
2 Warning2 FALSE
3 Warning3 TRUE
Now I want to return list of warnings that are set to ...

**1**

vote

**5**answers

41 views

### Using 3 conditions in Excel

I am trying to extract data using three conditions.
I have very sensitive information, so I am only able to provide some basic data below.
What I am trying to output from using 3 lists is that if a ...

**0**

votes

**0**answers

18 views

### merge multiple sheets in a work book excluding specific sheets excel 2010

How can I merge multiple sheets in a work book excluding specific sheets in excel 2010. I tried the macro below but it includes all the sheets in the workbook. Thanks.
Sub Combine()
Dim J As ...

**0**

votes

**1**answer

21 views

### Looking to total cells, but leave some blank in between

I've written a worksheet for selling work. I have one column "C" for the cost, and the column next to it "D" for a subtotal. Depending on the work to be performed, not all rows will have values to ...

**0**

votes

**2**answers

37 views

### Conditionally Formatting Across Cells

I am organizing an event and would like to do the following:
I have a General sheet with all the invitees names, addresses, etc. I also mark here if they replied and how many people will be ...

**0**

votes

**1**answer

33 views

### Chart labeling: durations in minutes and seconds

I am attempting to chart the durations of phone interactions. They are currently entered as time of day values (I did not build the workbook), even though they are actually duration values, and Excel ...

**0**

votes

**1**answer

17 views

### How to use excel 2010 functions to caculate the sum of distinct unique values?

How to use excel 2010 functions to caculate the sum of distinct unique values?
remove the duplicate values
sum of distinct unique values
but I don't need to know who are the distinct unique values, ...

**0**

votes

**0**answers

23 views

### Why is the autosum behaviour not as expected when used on an entire column?

This is some strange behavior I encountered when using the Sigma to sum a column.
If I click on the open cell under the last number in a column and then hit the Sigma button and press enter I get the ...

**0**

votes

**1**answer

20 views

### Capturing Text from Pivot Table

I'm trying to create a pivot table that has 2 parts.
In order to push parameters into the table, you would add allows users to filter across any one of several parameters in a field. For instance, ...

**0**

votes

**1**answer

12 views

### How do I assign a key based on count of available keys and indicator value?

I need some help writing a formula that does the following:
Looks to see if an indicator=1
If indicator=1, lookup the key value based on the number available for that particular key
Keep looking up ...

**0**

votes

**1**answer

16 views

### converting a cell specifcally formatted as 1h 25m (not excel time such as 1:25) and converting the cell to 85m

I would like to convert a cell with the following text/numeric string 1h 25min to a cell that displays 85min. Unfortunately, the original cell is only in the 1h 25min format and not in a true excel ...

**0**

votes

**1**answer

33 views

### Mass change the appearance of URLs in Microsoft Excel

I wish to change the appearance of the URLS in a Microsoft Excel worksheet. At present the current URL's look something like: http://www.example.com/101010101010101010101010101. I know how to change ...

**-1**

votes

**0**answers

33 views

### How do I SUM values in rows if several columns meet particular criteria?

Each week our company receives standard report which can't be modified.
Description 01/11 02/11 03/11 04/11
"All-in-one C340" 10 1 20 4
"All-in-one C345" 10 ...

**0**

votes

**2**answers

26 views

### Using COUNTIFS for a series of values at once

Working a step higher then COUNTIFS, I appose a challenge to write a formula without VBA code. The basic data is combined from 1000s of rows with:
Column A: rows with values from 1 to 3
Column B: ...

**1**

vote

**1**answer

46 views

### How to leave out certain rows in input data when creating a line chart [closed]

I created a line chart in Excel and manipulated the chart data that is attached to it; however, I have some rows of my chart data that I do not want displayed on the line chart. How can I make these ...

**0**

votes

**1**answer

35 views

### How can I make Excel conditionally highlight cells based on the contents of cells?

I have a few things that I would like to do on Excel:
How do you highlight a row [A-U] if only the first cell A has had text entered in it?
When you enter text in cell A cells B-U have a yellow ...

**0**

votes

**1**answer

26 views

### What is this argument of AGGREGATE function doing in my formula?

I need to understand this part ($B$1:$B$15<>"") in the following formula used to represent a column of data with blank cells removed:
...

**0**

votes

**3**answers

80 views

### Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...

**2**

votes

**3**answers

37 views

### How to compute the number of occurrences of a string in the entire workbook?

I want to compute number of times a specified string appears in my entire workbook in some column. I have tried this formula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") But it renders the #VALUE! ...

**5**

votes

**4**answers

483 views

### How can I record a transition from a 0 to 1 in a column in Excel?

I need to record the number of transitions from a down state (0) to up state (1) in a single column in excel and I am wondering whether there is some kind of logic like this available in Excel or ...

**-3**

votes

**1**answer

36 views

### How to copy and paste “ONLY VALUES” from one workbook to another?

Can someone teach me how to copy and paste "ONLY VALUES" from one workbook to another?
The workbook contains about 20 worksheets, and I don't want to copy and paste each sheet one after another into ...