Tagged Questions
0
votes
1answer
27 views
How to count based upon multiple conditions
I am trying to create the following formula
=SUM(IF(AND(K3:K138="Tekken",L3:L138="American Football"),1))
However, it always seems to result in zero, even where a single row would have both values ...
1
vote
1answer
37 views
Select activation method from dropdown instead of writing it yourself
Answer at bottom of post
I got the code below to work but a question remains. When I copy and paste the code into my project it won't work. But inserting the outline of the call from the dropdown ...
0
votes
0answers
21 views
Paste html into Excel 2010 without Excel parsing the html
I'm running a query on SQL. Typically I can easily copy and paste the results from the query into Excel and it preserves the columns, rows etc.
However, in this case I'm selecting a bunch of emails ...
0
votes
0answers
31 views
Why does my Excel macro fail when run from a hotkey and work when run from the macros ribbon?
I am really confused by what is going on with a macro I created to parse out space delimited files. The macro runs successfully as long as I select the macros ribbon and the macro from the dialog box. ...
1
vote
1answer
52 views
Any way to have Excel 2010 treat a quoted value “08540” as text not number when opening a CSV
I have a CSV with data like this:
"08540",1,"PRINCETON","NJ"
I know that it is possible to specify "Text" when using the Import Wizard and defining columns. But is there a way to tell Excel to ...
0
votes
2answers
63 views
How do I separate three different phone numbers concatenated in one cell into three new rows?
I am doing work for a client and need to know how to separate these phone numbers.
I have
0121 396 0501 / 0121 123 434* / 0797 978 8265
in one cell and I want to end up with the same row ...
2
votes
1answer
32 views
Excel 2010 removes data from clipboard, how can I stop it?
The order of my actions is:
Copy data from pivot table from second sheet.
Paste data as value into first sheet.
Notice that something is wrong in the data.
Go back to the second sheet and change ...
0
votes
3answers
27 views
Nested IF statement
I'm having a problem with this code for nested =IF statement. The range is basically:
<=16 to get 5 pts
16-17 to get 4 pts
17-18 to get 3 pts
18-19 to get 2 pts
above 19 to get 1 pt
and my code ...
2
votes
0answers
35 views
Background colors in Excel 2010
I have an Excel 2010 spreadsheet. Right now, the entire background is at 40% gray, and need to be white/transparent.
There's no conditional formatting set.
The worksheet is not locked.
The ...
0
votes
0answers
23 views
Excel formula to copy text from another sheet1 to sheet 2
I am trying everything but nothing works. Please help me.
In sheet 1, when column 3 contains a specific word (like PTA), it must copy the whole row where that PTA word is in, to sheet 2. And the same ...
0
votes
1answer
33 views
Compare two date columns and highlight the newest
I have two date columns in the same sheet. Column B receives its data from a vlookup function.
COLUMN A | COLUMN B
2013-07-05 | 2012-01-01
What I want to do, is to compare the two date columns ...
0
votes
2answers
31 views
CTRL+D in Excel 2010 fills values and formatting, but I want it to only fill values/formulas
When you press CTRL+D in Excel 2007 or 2010 (and probably older versions), it makes an exact copy of the cell above it (modifying cell references, of course). In doing so, it copies everything else ...
0
votes
1answer
45 views
Merge multiple tables by comparing multiple rows
I have multiple three column tables that share two rows in common:
DailyCosts: DailyPrices
CodeRole CodeRegion DailyCost CodeRole CodeRegion DailyPrice
1 1 ...
2
votes
1answer
31 views
Excel visual basic function across workbooks?
This seems like it should be a common issue, but I couldn't find anything on it, maybe I'm just searching for the wrong thing.
Basically I get sent a lot of workbooks that need specific modifications ...
0
votes
1answer
33 views
Optimally positioning pie chart data labels in Excel with VBA
I have a worksheet where I create a pie chart from data from a database. The chart changes based on the data it is displaying so the slices often change size.
I want to display the data labels ...
-1
votes
0answers
45 views
Format multiple sheets with irregular headers in Excel
I am trying to format a price list from a supplier for import into my own database. The workbook the supplier generates contains multiple worksheets each with the same columns of data that I want to ...
0
votes
3answers
37 views
Counting occurrences in first column of a file in Excel
I need code to find duplicate numbers and continue to count the number of times the duplicate was found.
Example: I have 20,000 entries and 303 unique numbers. I need to know how many times each ...
0
votes
1answer
35 views
Selecting Columns without Header
I am currently making a simple e-mail list to be used by my office for e-mail blasts. I want to have the first row a header (I currently have the first row frozen) and that whenever you click on a ...
0
votes
0answers
49 views
Need to find four common data columns in different locations in multiple workbooks
I am working from reports generated from different projects. Each report has varying columns but all have the four columns that I need. I would like to be able to boil the sheets down to these four ...
1
vote
3answers
39 views
Applying a one time formula to a cell range
I have a big range of data containing numeric values. but due to newer requirement to the maximum and minimum value the numeric data, I have to modified a lot of cells setting the value of each cell ...
0
votes
1answer
38 views
Using the advanced filter in Excel
I have 40 rows in Excel starting with a SerialID in sheet 1. In sheet 2 i have 109 rows which I would like to filter based on sheet 1. Sheet 1 contains records i want to hide from sheet 2.
2
votes
1answer
27 views
Capitalize first word in each row of a column in mac
I want to capitalize the first letter of every word in each row of a column.
So I create a new column, type in =PROPER(B2), then drag that down the entire length of that column. It works great, it ...
0
votes
1answer
35 views
Add rows to insert dates from a “from” and “to” column
I want to breakdown an Excel sheet by having it in a specific format.
Right now, the sheet is like this
FROM TO TOTAL
01/27/12 01/27/12 1
03/09/12 03/09/12 1
04/11/12 ...
0
votes
1answer
78 views
In Excel I need to find data matches between two arrays - one horizontal and one vertical
In Excel 2010 (or 2007 - I have both, although my OS is only Win7 32 bit as a limitation for some legacy applications we run), I need to find how I can find and return the matching value from two data ...
5
votes
2answers
39 views
Split Excel column with XML inside
Is there a way to break an Excel column containing XML into several
columns like this:
BEFORE
| Apples | Pears | XML |
| ------ | ----- | ...
0
votes
1answer
28 views
How to customize Paste Value Excel Short Cut
I was reading in this other post about how to set up a macro to past values in Excel. I understand how to create he macro but I would like to set the keyboard shortcut to Ctrl d but excel pops up an ...
4
votes
3answers
59 views
Check if Excel cell text is numeric using formula only
I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.
What I've found is that the ISNUMBER() function ...
0
votes
1answer
18 views
Custom Chart Axis
I have an Excel chart whose X-axis values are 5000, 10000, 15000 and so-on but I want them to be like 1,2,3,... i.e. divide them by 5000.
Is there any way I can accomplish this?
2
votes
3answers
53 views
Shifting/Justifying across cells in Excel
I have an excel table with the following formatting:
1| Line 1 | Line 2 | Line 3 | Line 4 |
2|123 Main Street| Apartment 1 | NY, NY 10000 | |
3| ...
1
vote
2answers
44 views
Can I use Microsoft Excel to convert a “ranged” table to a full breakdown table?
Suppose I have a spreadsheet with the following rows and columns:
QTY MIN | QTY MAX | 1 Color | 2 Color | 3 Color | 4 Color
1 | 10 | $1.00 | $1.50 | $1.75 | $1.90
11 | 20 ...
2
votes
2answers
36 views
Excel 2010/13 and column grouping : how to have the toggle button on the left?
When you group columns, there is a toggle button on top to fold/unfold the group.
This button by default appears on the right, which means that when you unfold the group, it jumps. So that you can't ...
-1
votes
1answer
26 views
Automatic borders in Excel 2010
I have a worksheet that automatically colors a row when column A is filled with certain text. What I want is to also have a thick black border appear above each colored cell, and not appear above any ...
2
votes
2answers
201 views
How to implement MATCH and INDEX functions together in Excel VBA?
I am trying to find the row number of the first cell that is less than zero.
So, by browsing through the net I was able to come up with this formula which works perfectly fine:
...
4
votes
1answer
68 views
Preventing Excel from automatically creating a range when entering + or * in formulas
Often, when I am entering formulas in Excel 2010, as soon as I enter a cell reference, and then a + or * to add or multiply things together, Excel will make some kind of correction that creates a ...
0
votes
1answer
27 views
Create a pivot table by joining two tables
I have two tables, each on its own worksheet, that are linked to Sharepoint lists, where one table has a foreign key that maps to the other's primary key. E.g.:
sheet: flavors
...
2
votes
1answer
42 views
How to make a pivot table with two sets of columns, each filtered differently
Suppose I have the following Excel data:
+------+-------+--------+
| Item | User | Status |
+------+-------+--------+
| MTB | Bob | A |
| QCD | Bob | A |
| MTB | Alice | B |
| ...
1
vote
1answer
38 views
Excel 2010 save as webpage - Want Japanese characters in HTML file instead of Unicode
For programming purposes (Objective-C) I need to export Japanese characters as a HTML file from Excel so I can parse data into my project. I'm using Excel 2010. For example:
When I open up the ...
0
votes
1answer
59 views
Excel - 2 Drop down lists to output a row-column intersection cell value to a third cell
I'm creating a spreadsheet for calculating costs of crafting in a game to determine profit ratios. There is a section of my spreadsheet where I would like to have a two drop down lists. In the first ...
2
votes
2answers
30 views
Excel: how to lowercase without losing newlines?
In Excel, if I have two lines in A1:
Hello
World
but if I go over to A2 and type =lower(A1), I get:
helloworld
How can I preserve the newline between Hello and World?
2
votes
1answer
72 views
How to config Microsoft Office Application (Word / Excel) to stop adding personal information to the document?
I have created word document (*.doc or .docx) or excel documents (.xls or *.xlsx).
Sometime, these documents need to be sent to some customer and therefore require personal information to be removed ...
0
votes
0answers
69 views
Update hyperlinks in Excel 2010 after copying files
I recently upgraded an end users' laptop to one that now has Windows 7. I copied all of his data to a backup drive and restored it into his new My Documents folder.
However, within the ...
2
votes
1answer
56 views
1/3 octave band chart in Excel?
I want to create a 1/3 octave band chart in Excel 2007 but I can't find the way to do it. Excel only allows me to put the 'x' axis into a logarithmic one, but the scale for 1/3 octave band is not this ...
2
votes
1answer
51 views
How to create a horizontal PDF in Excel 2010?
What do you have to do to create a horizontal PDF of an Excel document in Excel 2010?
-1
votes
1answer
78 views
Unable to open corrupted xlsx file
Below is the error while I try to open the file:
Excel cannot open the file '1.xlsx' because the file format or the file extension is not valid. Verify that the file has not been corrupted and that ...
0
votes
2answers
49 views
Handling Excel historical data and formulas
I have a large amount of data in an excel sheet, which users type in directly, adding new lines with new data. Each new line of data represents a customer. Each customer will has a cell with: county, ...
0
votes
1answer
67 views
Vlookup not working as expected for soccer data
i have a vlookup function that is supposed to give me some data from another worksheet. the worksheet contains soccer data, specifically about the european cups of the past 3 years (9-10, 10-11,11-12, ...
0
votes
1answer
66 views
How can I sort a list of numbers, by its last 3 digits in Excel
I have an Excel spreadsheet with a list of numbers in the first column (that is: I have a number in A_1,A_2,...).
Each one of the nubers if have 8-9 digits and I would like to sort this column by its ...
1
vote
1answer
70 views
Dynamically insert value into cell
If TL;DR - Just go down to the EDIT...
I have a spreadsheet with a table - the table is a comparison of two different (but similar) sets of data. So basically 3 columns with the 3rd being the result ...
0
votes
0answers
35 views
Excel import data from web - change browser?
I am working on an internal web application, where some tables previously has been imported to Excel 2010 using the "From web" function under the "Data"-tab. Recently we upated this web-application to ...
0
votes
2answers
66 views
How to count number of rows excluding rows with duplicate data
I have a table in which the column "Doc. No." has duplicate info as below; please advise on which formula I should use to count the number of unique Doc. that I have (as reference to the below table ...
