# Tagged Questions

The 2010 version of Microsoft's spreadsheet application.

**0**

votes

**2**answers

2k views

### How can I get “Calculated Columns” to work in excel?

Using Excel 2010, I want a single formula to apply to all cells in a column.
I see documentation for a feature called "Calculated Columns". That is exactly what I want, but when I follow the ...

**5**

votes

**11**answers

48k views

### Copy Excel worksheet and maintain relative cell reference in formulas

Another copy problem in Excel:
How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes ...

**0**

votes

**2**answers

8k views

### Excel 2010 - How to quickly add multiple hyperlinks to different cells

I have an Excel document in which I have to add multiple folder hyperlinks. The problem is that I have more than a thousand rows, and doing it manually will take ages.
In each row, cell B will ...

**0**

votes

**0**answers

6 views

### One dynamic named range not staying dynamic while another in the workbook is

I am attempting to use dynamic named ranges to make it so I can pre-set the column portion of the print ranges in a workbook without assuming a number of rows. So I opened the name manager and ...

**4**

votes

**4**answers

27k views

### How to toggle Scroll Lock in Excel 2010 without a Scroll Lock key in the keyboard

I have a notebook with no Scroll Lock key (or any shortcut using the 'Fn' key).
Somehow Excel 2010 got the Scroll Lock on. I see it left down in the status bar and now I can't navigate through cells ...

**2**

votes

**3**answers

4k views

### Vertical Histogram in Excel with Conditional Formatting

I'm able to do data bar conditional formatting in Excel 2010, but I can't figure out how to switch the bar axis to make it vertical. Is there any way around this? Can I simulate a vertical histogram ...

**0**

votes

**1**answer

36 views

### Creating Folders and Subfolders from excel cell values

Im trying to create a directory named after the value in column E within a directory (c:/Site Information), then create a further directory called the concatenated value of column A,B,C, and D. This ...

**0**

votes

**1**answer

2k views

### In excel how to do a 'sumif' function that is dynamic to a changing column

I'm trying to use a formula to do a sumif of a column of monthly data that changes depending on the current month. For example say I am entering this formula in column A of tab 2. Since it is ...

**0**

votes

**0**answers

10 views

### Outlines of merged cells not staying consistent when a filter is applied

I have a spreadsheet that uses merged cells for visual clarity. The merged cells are in the B column (dates), and in the A column there are various values. I use filters in column A, and in my other ...

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

**0**

votes

**2**answers

54 views

### How to make data shown on excel drop down list appear as numbers (shortcuts)?

Is it possible to have the data (choices) shown when clicking the drop down list to appear as numbers (or any text other than the actual text from validation data source)?
Let's say the choices ...

**0**

votes

**1**answer

4k views

### Call PowerShell script from Excel macro

I'm trying to call a PowerShell script from a macro in Excel.
I have seen a number of examples along the lines of:
retval = Shell("powershell ""C:\MyTest.ps1""", 1)
When I try to use this, ...

**0**

votes

**0**answers

8 views

### Macro Hyperlink Error in Mac OS (Office for Mac 2011)

I am getting an error on a macro only when using a Mac. This macro was designed to solve a problem with a formula using the Hyperlink function to copy a link from another sheet for each row. (Excel ...

**0**

votes

**0**answers

19 views

### SUMPRODUCT & ROUND

Is there a way to get the product of the array with conditions to round to the 2nd decimal place before summing?
Current formula w/o rounding:
...

**0**

votes

**1**answer

4k views

### Excel data validation to whole column

I want apply data validation list to a whole column in excel 2010. How can I do that with out copying the validation rule

**0**

votes

**1**answer

32 views

### How can I search for a value that's repeated in excel?

I have a spreedsheet (Sheet2) that has 4 columns:
ID | Name | Semester | SemesterAvg | Overall Avg | Period |
1 | John | 1 | 2.5 | 2.5 | 20132 |
1 | John | 2 | ...

**0**

votes

**1**answer

20 views

### How can I get a count of values for one column as compared to a second column?

I have a spreadsheet with a list of companies that submit data, the type of data they submit, and the date they submit. I need to a do a count of the number of times per day they submit, but only do ...

**0**

votes

**1**answer

20 views

### Excel Month() function does not detect some date format

I have a simple table like this in excel:
The formula i used was =IFERROR(IF(LEN(B3)>0,MONTH(B3),""),""). I check if a cell is blank by using LEN, if so ignore, else, convert it to month no.
I ...

**0**

votes

**0**answers

6 views

### 3D Graphics with 3 Colums as Data

I have an excel tab as attached :
I want to make a graphic with 3 axis. On the first one, the Type, on the second, the projet, and on the third, the date.
However, I've tryed several things, and I ...

**2**

votes

**0**answers

6 views

### Excel Conditional Formatting when adding extra columns

I've created a worksheet and added conditional formatting to it. Unfortunately whenever I need to add extra columns to this sheet, my conditional formatting messes up. Not only the range when this ...

**0**

votes

**1**answer

3k views

### Special-Pasting a hyperlink in Excel

Is there a way to use the Paste Special command to paste a hyperlink from one cell in Excel 2010 to another cell?
I am asking specifically about the Paste Special command, not the Paste Link or ...

**0**

votes

**1**answer

5k views

### How to customize Paste Value Excel Short Cut

I was reading in this other post how to set up VBA code to paste values in Excel. I understand how to create the VBA code.
I would like to set the keyboard shortcut to Ctrl d but Excel pops up an ...

**0**

votes

**0**answers

15 views

### How do i right align an icon set arrow under conditional formatting so that both number and arrow are on right side of cell neatly together?

Cell displays a conditional format arrow Icon set which is on far left side of cell. The number in that cell is right aligned and I want to bring the arrow directly next to the number. I want the ...

**0**

votes

**1**answer

25 views

### Running macro on multiple sheets

I can run macro on individual worksheets but not all open worksheets.
I want to run this macro on 700 worksheets. We are trying to delete one column.
Sub RunMacroOnAllSheetsToRight()
For i = ...

**0**

votes

**0**answers

11 views

### Using IF to compare name of a worksheet with worksheets in workbook

I have two files Main file and Result.
Main file have multiple worksheet like delhi, moscow, paris, scotland and each one has same type of date and same heading ie.:
S_No. , Vendor, VendorId, ...

**0**

votes

**1**answer

12 views

### How to create a dynamic date title in excel 2010 using different tables and data

My friend has asked for help with an excel assignment and they have 2 tables, HRM and Data_Tables.
Data_Tables has corresponding month numbers from 1-12 in column I from I3-I14. In J column there is ...

**1**

vote

**3**answers

23k views

### Excel Formula To Get First Non-Zero Value in Row and Return Column Header

I am attempting to find a formula where I can look at a column range within a row and return the column header in that row where the first non-zero value occurs, moving left to right.
Below is a ...

**0**

votes

**1**answer

6k 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 change from -4 to +4. A change ...

**0**

votes

**1**answer

13 views

### Count unique values within specified variable date range

I'm attempting to create a report that includes a count of unique values from a table. I'm using Excel 2010. Normally this would be an easy matter of adding a column to the source data with a formula ...

**3**

votes

**1**answer

35 views

### Excel to count number of concurrent sessions based on start/end times

I have a massive set of data that I'm trying to work through. In Column A, I have a username, in Column B I have a session start date/time, in Column C I have the session end date/time.
I am trying ...

**0**

votes

**2**answers

26 views

### Needs VBA solution - Clear contents of 4 cells to left of cell with N/A Error

I have a table of data in which column L has a vlookup formula. In the rows where column L (i.e., the vlookup returns "#N/A"), I would like to clear the contents of the eight cells to the left ...

**0**

votes

**2**answers

35 views

### Check if a cell contains any of words from a dynamic list

I want to use a formula to populate the "RESULTS" cells. I need it to check if the adjacent "CHECKED CELL" contains any of the words listed in the "KEYWORDS" cells.
Ideally, I would be able to add ...

**-1**

votes

**0**answers

16 views

### How can a cell be displayed if another cell is marked?

I am a trainer for an organization, and we have a major conversion to a new core system coming up. We have been provided a massive spreadsheet listing all the training modules that each person must ...

**0**

votes

**0**answers

38 views

+50

### Hotkey to open filter drop down menu in merged cell in Excel 2010

I use Excel 2010 and use Alt+Down arrow to open filter drop down menus.
Sometimes my header contains vertically merged cells (e.g. autofilters are in the second row and in some columns cells in rows ...

**0**

votes

**0**answers

7 views

### How to save .xls file to tab-delimited(.txt) and specify Shift-JIS encoding

What I want to do is save .xls file to tab delimited .txt file with specifying SHIFT-JIS encoding. I tried specifying encoding in web options however when I save to tab delimited still japanese ...

**3**

votes

**1**answer

3k views

### Macros in Excel 2010 hangs

I have a spreadsheet with several macros. Generally, when previously using Excel 2007, a user clicks a button and everything works as expected (calculations, some email sending & file I/O). ...

**2**

votes

**1**answer

31 views

### Excel - keyboard shortcut to double click on format painter

Double clicking on Format Painter has the effect of allowing you to click a few times and apply the same format in all those places.
Alt-H-FP has the same effect as a single click on format painter.
...

**0**

votes

**3**answers

33 views

### How to highlight where a number in another cell falls in a column? Bracket it with the next highest and next lowest?

This is probably an easy fix. I have one cell (Cell B8) with say the number 2000 in it. I have another column of 6 numbers (Cell A1 to A6) (Say 4000, 3050, 2100,2100, 1150, 200) that will change with ...

**0**

votes

**0**answers

18 views

### Most efficient function(s) to compare and extract rows from two reports? [on hold]

I work for an international shipping company processing hundreds of containers each day. I generate a report (from an AS 11g servlet, FWIW) in the morning, work through it based on the 2 Modes of ...

**0**

votes

**1**answer

15 views

### Excel playoff bracket

I've created a playoff bracket and want to setup a way to automatically move the team name to the next round (another cell) once 4 wins are achieved. I'm using Excel 2010.
Example:
Compare the value ...

**0**

votes

**1**answer

11 views

### Dropdown + Autofill

I have a address list(name, street,city,telnumber)
On another sheet I have a form, where I can choose the name from an dropdown list. Now I want to get street,city, telnumber for this name. Is this ...

**4**

votes

**3**answers

40 views

### Keep Specific Columns From Repeating in Excel

There is a report that I pull from our ERP system that lists order detail information. It will list the order number, customer code, customer name, order date, order status, order total, product code, ...

**0**

votes

**1**answer

13 views

### Excel odd @ formula in table

I have an Excel workbook from ancient times. A table with header and rows of data are recorded in it. I insert a new column next to an existing one and start typing in an if formula in the first row.
...

**-2**

votes

**0**answers

29 views

### tallying with different criteria

An example:
Apple Worm Black
Apple Worm Black Sour
Apple Black
Orange Sweet
Orange Sweet
(5 rows and 4 separate columns)
How do I create a formula to tally all the Red apples with a worm or ...

**2**

votes

**0**answers

17 views

### How to return results of SQL query to Excel sheet with VBA function [duplicate]

I'm trying to create a VBA function in Excel to affect dynamic value from Access to Excel sheet. This is my code.
Public Function SQL(DB As String, Query As String)
Dim conn As ADODB.Connection
...

**0**

votes

**0**answers

27 views

### Excel, macro move one row down and then delete the row

I'm trying to clean up duplicate rows. My data is in a table with filters applied. I have tried using the Excel feature to remove duplicates, but get the following error.
Cannot complete ...

**2**

votes

**3**answers

6k views

### Excel-MATCH function doesn't work on pasted values

I'm trying to find the positions of the values(text) in one column in another column. I ran the function: =MATCH(B1, A:A, 0) and I get a #N/A result. But this result is incorrect...as I clearly see ...

**0**

votes

**2**answers

41 views

### How to give worksheet tab a color if range of cells contains text

I have tried code that I've found on SE, and elsewhere but they aren't working as I think they can. I'll list them below. I'm almost certain this is an easy question.
What I'm trying to do: If in any ...

**1**

vote

**1**answer

42 views

### How can I replace forbidden characters in a file names before saving in VBA?

This will be very simple for somebody who knows VBA better than me. I used a code original provided by user Nixda on this forum to split excel worksheet into multiple CSV files based on column value ...

**0**

votes

**1**answer

9 views

### Can you change the type of an object in EXCEL

I created a rectangle object with text inside an EXCEL document and formatted it a lot, if I want to suddenly change it to circular (oblique) type, is there a way to do this in EXCEL?
Right now, I ...