# Tagged Questions

The 2010 version of Microsoft's spreadsheet application.

**0**

votes

**1**answer

38 views

### Inserting 'n' blank rows using a macro

I am currently trying to develop an excel spread sheet to make it easier to format. What I would like to do is create a macro to insert blank rows in multiple places without affecting subsequent rows. ...

**0**

votes

**2**answers

86 views

### How do I copy value in different column from previous row if the cell is not empty?

I have the following situation:
A B
19260218 234 77 ok
3
ok
19310328 174 46 2
When the value in column B is numerical, I have a ...

**1**

vote

**2**answers

90 views

### Select elements in an array that satisfy a criteria

The goal: Kappa-Sigma clipping in Excel
I want to implement a selective linear regression formula in Excel (avoiding VBA). I have a data sample on which I need to apply a linear regression. But my ...

**2**

votes

**1**answer

598 views

### Restrict paste into dropdown cells in excel

I have one excel sheet which contains many dropdown columns. Lets say one column has Test1, Test2, and Test3 as dropdown option values. Cells under this column should not accept the values other than ...

**0**

votes

**1**answer

30 views

### Placement of Secondary Horizontal Axis

On the primary horizontal axis I can go to Format Axis > Line Color and Style. There I can choose the color and style of the line that represents 0 on the primary vertical axis.
However, with the ...

**1**

vote

**1**answer

18 views

### How do I use the corresponding number to make the selection in a dropdown instead of using the mouse?

I looked at the information and it is just showing how to create the dropdown which I can do but I want to be able to type 1 and have it come up Very Poor, or 2 and have it come up with Poor, 3 ...

**0**

votes

**1**answer

54 views

### Is there a way to record macros to do things I would normally do in Windows explorer? E.g. Open an Excel file read-only? [closed]

Countless times a day, I go into Windows Explorer, shift-right click on an Excel file, Copy as path, then go into Run, type Excel /r (paste), which opens my Excel file as read only.
Is there a way to ...

**0**

votes

**1**answer

52 views

### Searching for a value across 2 tables in Excel 2010, returning yes or no

I have one workbook with 3 separate worksheets
Sheet 1 contains 2 columns.
Column A has a range of unique alphanumeric identifiers.
Column B has a range of non-unique numbers.
A B
...

**0**

votes

**0**answers

27 views

### Date FileDateTime in sharepoint

Sub Test()
myURL = "http://sps.ap.emersonprocess.com/sg/pssops/svcops/APServicePortal/ChartTemplates/
Filename = "IBS-CCR-AP_Prod.xlsx"
myURL = Replace(myURL, "http:", "")
myURL = Replace(myURL, "/", ...

**-1**

votes

**2**answers

29 views

### How can I assign Cell Part as Variable?

I want to set a number in a cell to be a variable yet still keep text in it. That way it can be added up later using that variable.
I want it to say something like
Justicar Aatrox: 975
And ...

**0**

votes

**1**answer

33 views

### How do I change the DATA TYPE of a cell or group of cells?

I have a large spreadsheet of data which stubbornly will not change data type. when I run the type() formula on a cell, it returns the value 1, and it will not change no matter what I have tried.
...

**0**

votes

**1**answer

23 views

### apply conditional formatting to a cell when it matches the text in another column on another worksheet

I have a list of accounts in column a of sheet one.
I have a sublist of the same accounts in column a of sheet two.
If the account name in column a of sheet 1 matches an account name in column a of ...

**0**

votes

**0**answers

47 views

### Slicers: Can a slicer filter using more than one column?

I'm looking to create a slicer (or some other filtering mechanism as part of a dashboard) to filter data that may exist in more than one row.
As a very simplified example, suppose I had a table ...

**0**

votes

**0**answers

19 views

### Use named range within index match

I have a named range (DATA_DUMP_GROCERY) that I am trying to pull values from using index match. Here is my formula:
=INDEX(DATA_DUMP_GROCERY,MATCH(Confectionery!$B$15,DATA_DUMP_GROCERY,0),4)
As ...

**1**

vote

**3**answers

26 views

### Excel 2010 is lower rounding a number entered as a custom format for a credit card number

I have used the custom format for a cell as follows
####-####-####-####
When I enter the credit card number it will round down only the last number to a zero "0", no matter what number is entered.
...

**1**

vote

**1**answer

31 views

### Get the cell number from double clicking on it and put it into another formula

As part of my ICT work we need to create a battleship game and one way I was thinking of doing it was using
=IF(ISNUMBER(SEARCH("*x*",C3)),"HIT","MISS")
to check if that cell contains x but ...

**0**

votes

**2**answers

61 views

### Excel - auto calcuate pallets based on qty

So, I been thinking how to program this...
There is product A, has two different pallet quantities (Qty 8 & qty 10).
When customer orders (ex: 72 qty of product A), I have to manually divide it ...

**0**

votes

**2**answers

23 views

### Dont show 0% data labels in excel but show -0.1% and +0.1%

I've managed to hide data labels with 0% by using the number format 0.0%;; but need to show -0.2% and that's been removed - how do i hide 0% but show +0/1% and -0.1%?

**0**

votes

**1**answer

74 views

### How to control excel cells together with vba script?

In the userform there are product names (combobox) , prices , amount (textbox). For product name control I use with combobox . I write amount of product to my textbox as a manuel when I choose the ...

**1**

vote

**1**answer

155 views

### How to add photos to specific cells in my Excel worksheet from my computer? [closed]

This is the code which I used :
Private Sub Image1_Click()
Range("C1").Select
Application.Dialogs(xlDialogInsertPicture).Show
End Sub
Private Sub Image2_Click()
Range("D1").Select
...

**1**

vote

**0**answers

32 views

### Show webpage in excel

I have an excel file with a lot of urls, which I have to look through and mark. My idea is now, to preview the urls in a tiny box when I hover over the url. However, I cannot find such functionality ...

**1**

vote

**1**answer

31 views

### How to count number of unique buyers in excel? [closed]

I am creating a workbook for sold items of my company. and I have a list of items in column O of one of my sheets.
now I want excel to count the number of total buyers and number of unique buyers.
I ...

**0**

votes

**1**answer

20 views

### Microsoft Excel - Functions sum values after mulitplying multiple criterias after looking up tables

have provided a link to the excel worksheet that I need to resolve. I have worked up for two values in cell B9, which I need to do for other criteria which goes beyond 18 or so.
Formula that I used is ...

**1**

vote

**1**answer

253 views

### Excel 2010 - Need to sum dynamic range based on trailing 3 months

I am trying to sum a dynamic range for a list of entries 3 months before and 3 months after a specific month. I think I need to use an array to get this, but I'm not able to find what I need to do.
...

**1**

vote

**1**answer

54 views

### Excel VLOOKUP with named range not working

I have the following VLOOKUP on a worksheet:
=VLOOKUP(D5,FilePurposes,2)&" in respect of "&C5
Where the named range FilePurposes refers to a block of two columns and three rows that look ...

**0**

votes

**1**answer

18 views

### Need to change the cell colours exactly as matched in other excel sheet

I have two excel files with similar set of data in columns. One sheet has specific colours in the data and the other sheet has some values. I need to copy exact colours to the exact matching cells.
...

**1**

vote

**2**answers

76 views

### When I copy table data into excel, it ONLY pastes it into column A! How to fix this?

If I use the following link:
http://web.utk.edu/~dhouston/excel/exam1.pdf
to copy the table and paste it into excel, it will only paste the values into one long column in Column A (and each row has ...

**0**

votes

**1**answer

162 views

### How do I get Excel to identify a country from city list?

I have a list of data with many columns.
In one column - let's say C, there is a list of cities/places. There are multiple entries, i.e. one city pops up loads of times in a column in different ...

**0**

votes

**2**answers

230 views

### Excel changing UNC paths in shared workbook

Excel documents stored on network drive, for example, \\networkdata\Excel\Shared
Data workbook.xls contains tables with data entered into.
Report.xls contains UNC paths pointing to ...

**1**

vote

**1**answer

101 views

### excel vba userform/button coding

I have created a workbook in Excel. To navigate I have inserted a userform. I have code which takes me to a spreadsheet once a button is clicked on the userform.
Now I would like a button on the ...

**0**

votes

**1**answer

82 views

### How to display US format date time in csv file in UK format in Excel 2010?

I have a Windows Performance Monitor csv file that I want to view in Excel and produce charts from. The first column in the csv file is in US datetime format. e.g. "01/12/2015 10:54:44.043" which ...

**1**

vote

**2**answers

43 views

### How do I create spreadsheet that will alert me when a quantity drops below a certain threshold in Excel 2010?

I am trying to make a sort of "inventory helper" spreadsheet that can track quantities. On the summary sheet, I want to be able to enter a quantity into a column called "On Hand" and then if that ...

**0**

votes

**1**answer

21 views

### Using the text values from one column to lead to output in another

I have a list of cities in one column, Column C.
I have a list of countries which relate to each of those cities. How do I get Excel to look at the Column C, compare to my list of countries (I can ...

**0**

votes

**0**answers

46 views

### How can I save realtime data from external source to excel?

Excel can stream live data from an external source on the internet. One example is stock quotes from google finance. many trading platforms also allow linking to excel. So one can see in excel, a row ...

**0**

votes

**0**answers

19 views

### Excel 2010 - Pivot Table Inquiry

Is there a way for the report filters from the pivot table to go to the row labels whenever I choose an option in the slicers?

**0**

votes

**0**answers

41 views

### Expanding abbreviated text with Substitute formula in Excel

I have a spreadsheet contains a list of 24k items found in a grocery store. Most items have abbreviated words such as: w/ for with; choc for chocolate; sauv for sauvignon. Yet the list contains both ...

**0**

votes

**1**answer

40 views

### Determine days in Excel formula

Is there a way to get a number of days based on ONLY 1 column of dates?
E.g., column A has dates:
A1 - 12/31/14
A2 - 1/1/10
A3 - 4/5/14
A4 - 12/31/12
A5 - 12/31/14
A6 - 1/8/15
I am trying to ...

**0**

votes

**1**answer

79 views

### How to use conditional formatting to highlight specific days of the week AND time periods?

This answer regarding formatting on date time information Excel 2010 Conditional Formatting - TIME is a very, very technical answer that, in truth, I don't really understand! Hey-ho, I can deal with ...

**0**

votes

**1**answer

4k views

### Can't exit design mode because Control 'xxxx' can not be created

What is the cause of this error and how do I fix it?
This started occurring on a workbook of ours for reasons unexplained. I made a tiny change to the VBA code behind it, just plotting slightly ...

**0**

votes

**1**answer

53 views

### individual greek letters in chart legend that comes from a formula

I'm creating a Weibull plot in Excel 2010. Each line on the chart needs a legend of this form:
my 1st data [n=250, b=2.401] { where "b" should really be a lowercase Greek beta }
my 2nd line ...

**0**

votes

**0**answers

39 views

### what are the ways to protect excel sheet 2010 template from user modification?

I am having excel template with fields(Column) and few of the column are colored.I want to restrict use from manipulating the excel template,but also the user can insert data into the template and ...

**0**

votes

**0**answers

15 views

### need horizontal row in excel 2010 based on matched array value and vertical row

W X Y Z
A 1
B 1
C 1
D 1
I want to convert this array to a 1d vector which shows has W,X,Y,Z side by side with A,B,C,D based on the wherever there are 1s

**0**

votes

**2**answers

206 views

### unique count of values in a cell separated by comma in excel

How do I get the unique count of values in a cell separated by comma in excel?
For example: cell B13 has the value
1,1,2,3,7,1
Using the below formula we get the count of values separated by , as ...

**0**

votes

**1**answer

18 views

### Automatically push row downward

I'm trying to make a time sheet document, and I want it to be formatted
-----------------------------------------
| Date | Time In | Time Out | Hours |
-----------------------------------------
| ...

**0**

votes

**1**answer

32 views

### MS Word 2010: Vertical line to paragraph

I have a timeline of events in my CV, where each event is described by a single paragraph. Some paragraphs are logically enclosed by others (such as an internship during studies), which I want to ...

**0**

votes

**0**answers

39 views

### Excel xlsx protected view import into ods libreoffice

On a website I get data from, I can download the query results in Excel .XLSX file format only.
This is OK at my office where I have MS-Excel. I open the file and always get this warning message ...

**0**

votes

**1**answer

68 views

### How do you export an Excel file to CSV with rows have differing number of columns?

I have text file with a header, master, detail, footer record. The types of rows all have a different amount of columns. When I export to CSV, it exports all data and puts in fillers to the longest ...

**0**

votes

**1**answer

36 views

### Zoom in on Excel 2010 print preview

Is there any way to zoom in when in print preview mode for Excel 2010? Many textboxes don't print as displayed (not WYSIWYG) and sometimes the print preview zoomed out is too small to notice.

**1**

vote

**2**answers

96 views

### Chart macro displaying incorrect labels from non-sequential visible rows on filtered worksheet

This macro displays text labels from the source worksheet when double-clicking a dot on a scatterplot chart. The chart is updated when the source worksheet is filtered on any of several columns. The ...

**0**

votes

**0**answers

58 views

### Update to: Macro to merge cells in a column if the same rows are merged in a different column

I have a large (over 10k rows) spreadsheet with text data I want to concatenate from multiple rows into a single row. The text data corresponds to a date that is in a merged cell in a neighboring ...