Tagged Questions
-1
votes
0answers
17 views
Trying to match values in a worksheet to a range of values in a 2nd worksheet. both worksheets may contain list/array of values
I have two worksheets, but the there is really only 1 column in each worksheet that is giving me a headache.
Both Worksheet 1 & Worksheet 2 has a column E that each contains only 1 value, the ...
0
votes
1answer
34 views
probable cause of excel files getting corrupted
how to find out the probable cause of corruption of excel files...here i m not saying of a single file...files are getting corrupted one after the other...can
1)using windows 8 with excel 2007 and ...
2
votes
1answer
55 views
How to automate transforming a table into a matrix
I've been looking for a way to automate transforming data in an Excel table into a matrix.
For example, from the left table into the matrix on the right, as shown in the following image:
Apparently ...
0
votes
3answers
72 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 ...
0
votes
1answer
49 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
35 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 ...
-1
votes
0answers
47 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 ...
-1
votes
0answers
28 views
EXCEL EMBED CAPABILITY: can the embed link be manually entered?
I can manually enter a HYPERLINK into a cell, e.g.
=HyperLink("\FileLocus\FileSpec.Ext","LabelText")
but cannot similarly enter =Embed("\FileLocus\FileSpec.Ext","LabelText"). My need to do this is ...
-3
votes
0answers
40 views
In Excel 2007, why must I click the title bar of an open spreadsheet to bring it to the front? [closed]
I'm using Excel 2007, and when there's a spreadsheet window behind another spreadsheet window that I want to bring in front, I can click the side of the covered window, but it doesn't bring it in ...
0
votes
3answers
39 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 ...
1
vote
1answer
46 views
Conditional Formatting - Highlight An Entire Row by Comparing Two Cells of a Column
I've dug through the questions on this site and they address both problems I have, but separately. I've been trying to combine the solutions for a couple hours with no success.
Basically, I would ...
0
votes
1answer
41 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.
0
votes
3answers
74 views
How do you add extra value to a cell that already has an existing number in it
I find this hard to explain, but I'm stocktaking beers, spirits, and liqueurs and I want to keep one column for my ullage count for each product. However, as I go through the days, I often come across ...
2
votes
1answer
45 views
Calculating mean of value across random trials/refreshes in Excel?
Say I have a 1000 cells filled with random values and a particular cell that computes a final answer based on those 1000 values. Now I wish to track the value of the answer over 1000 trials i.e., I ...
1
vote
2answers
68 views
Excel: how to convert a table with daily dates to weeks
Have an excel file that tracks daily dates against a daily metric.
What I'd like to do is convert the table into weekly metrics. Is there an easy way to do this, perhaps with pivot tables?
0
votes
1answer
79 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 ...
0
votes
1answer
33 views
Issue with SUMPRODUCT Function
I am Having a issue with the SUMPRODUCT Function in Excel 2007. Here is my string and issue:
=SUMPRODUCT(($B$2:$B$800>DATE(2013,4,1))*($B$2:$B$800< DATE(2013,4,5)))
A B
1 4/1/2013
2 ...
1
vote
2answers
45 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 ...
1
vote
0answers
110 views
Excel 2007: Intermittent 0xc0000005 crashes
Hello there SuperUser Hive Mind.
We are having an issue at the office I work. One of our users who has a workflow which consists mostly of Excel documents with dynamic formatting and macros which she ...
2
votes
2answers
259 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:
...
1
vote
1answer
35 views
Exporting Data into a form type format from Microsoft Access query to Microsoft Excel Worksheet
Thanks for any help you can provide. I would prefer not to use VBA if possible, but will if necessary. I have MS Office 2007. I have a query which holds information for products that need user ...
1
vote
1answer
48 views
Using an array formula to add up several cells in another worksheet (using INDIRECT)
I have an Excel 2007 workbook. On one worksheet, I want to add up several regularly spaced values on another worksheet. I can easily do this with a very long sum formula without an array formula but ...
0
votes
1answer
48 views
Automatically updating a workbook from another workbook
I have a master workbook which I use to store all the client information I have available but I also need to generate other spreadsheets (preferably in separate files) with sub sections of the data in ...
0
votes
1answer
58 views
How do I add values in column B if column A contains the same employee surname more than once?
State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once?
I have been using what I ...
1
vote
1answer
73 views
Populate a table based on various inputs
I have three simple inputs for a quotation we're preparing, and I would like to automatically calculate all the possible combinations.
For example:
We need to produce Small, Medium, Large and XL ...
1
vote
0answers
31 views
Pivot table Grand Total calculates incorrectly [closed]
Does anyone know if there is a solution/fix for article ID: 211470 issue?
http://support.microsoft.com/kb/211470
Thanks,
Don
2
votes
1answer
74 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 ...
2
votes
1answer
60 views
Referenced values in a pivot table
I have a workbook that is being used by management to review data.
One sheet has a pivot table with some quite specific information.
The pivot table has multiple fields being pivoted in the rows. ...
2
votes
1answer
59 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 ...
1
vote
2answers
119 views
Passing Range to ABS function in VBA
I would like to perform a function which looks like this
Selection.FormulaArray = "=GEOMEAN(ABS((C646:E646)))"
Since my range keeps varying I would like to pass the range as a variable which looks ...
2
votes
2answers
79 views
Excel 2007 Sorting and Function
I have combined data from monthly charts over a 12 month period – and have sorted by Acct Name – (see ex. A)
Example A:
Rep Acct Name Jan Feb Mar Apr May Jun Jul ...
0
votes
1answer
102 views
MS Excel 2007 Warning Significant loss of functionality
I have created a template in MS Excel 2003. In MS Excel 2007, when I save it in MS Excel 1997 - 2003 format, it gives a compatibility warning;
Significant loss of functionality
This workbook ...
0
votes
2answers
69 views
Copy multiple cells from one to multiple worksheets
I have a workbook in Excel 2007 with 52 worksheet each representing a week of the year, each worksheet is laid out the same with 16 rows and 26 columns to give me 14 personnel and 24 hours with other ...
0
votes
2answers
94 views
Modify formula to work with conditional values
I am currently using the below formula to calculate enablement costs for meters
=SUM(SUMIF($D74,{"New Business"}),$U74*5500)
This worked fine as the fee of $5500 was standard, however I now need to ...
0
votes
2answers
103 views
How can I turn a Calculated Column back into a data column in an Excel Table?
I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new ...
0
votes
3answers
102 views
change link cell formula across sheets
I have a workbook named "stu", it has 1 to 30 sheets. In cell H4 I have linked a formula:
=[sub.xlsx]Sheet1!$B$2
in sheet1: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$2
in ...
1
vote
1answer
36 views
combining two plot type in excel
I want to combine xyline plot with bar plot in excel.
I created xyline plot and then added new series with a hope that I can chance the series 1 later.
Step1: But it turned to be not true, see the ...
0
votes
3answers
203 views
show cell value if another cell is true, else go to next row
It's my table in Sheet1:
A B C D
------------------------------------------
1 220 John Good
2 210 Jack Bad
3 300 ...
0
votes
1answer
232 views
Is it possible to create a broken X axis in Excel 2007?
I'm trying to find a solution to a problem that I have with my data.
In my data series, I have Temp over Time plotted on a scatter graph in excel. The data plots a period of around a week in the ...
0
votes
1answer
88 views
Complex excel formula help needed?
I have a complex formula i need to create to look up various fields, with percentages of totals to be given if certain criteria are met, and need some guidance.
In column P i have a monetary figure.
...
1
vote
2answers
82 views
How to display a specific formula value as a 0
I am using the DAYS360 formula to count how many days since last activity from a predefined date
=DAYS360($R15,$X$1)
The R Column reports on last activity date, whilst the the X1 cell contains the ...
2
votes
1answer
247 views
Change the Fill Color of a cell if the value of that cell equals the value of another specific cell
I want the fill color of cell C5 if it has the same value as cell A5.
Can this be done? I know how to format cell C5 if it has a certain value - but not when it is to be compared to the value of ...
-1
votes
1answer
97 views
How to set number format to show all decimal digits
I have one excel sheet. In that one cell value $1,544.13 when i click that cell it is showing 1544.12857142857 in formula bar.
I tried to set as General, but it does not show all the digits. It ...
0
votes
3answers
240 views
Find Array Intersection of 2 Match Array Formula
How do I find intersection of two match function by using array formula (pressing CTR+SHIFT+ENTER keys).
Currently I try using this array formula but cannot get any result.
...
2
votes
4answers
124 views
extract date from string in excel
How can I extract date from such strings in excel?
John8/11/12
Jackson09/11/2013
Desired result:
John 8/11/12
Jackson 09/11/2013
Can I do this using only excel formula using excel ...
0
votes
1answer
137 views
Compare two excel sheets with almost identical data and come out with matched and mismatched data
There are two excel sheets with almost similar data. One sheet is huge with around 1800 rows. and the second one is a small with around 100 rows. And these 100 rows may be present or not , if present ...
0
votes
2answers
86 views
How to remove duplicate records in excel 2007
I am using Excel 2007.
I have more than 40000 records in Sheet1.
I want to remove the duplicates.
Constraint for duplicate would be if you have the exact same fields for the entire line. This means ...
1
vote
1answer
117 views
Excel 2007 - changes on one sheet affecting other (without cell references)
Some strange behaviour I've encountered twice in Excel is that changes made to one sheet would be duplicated on another sheet in the same workbook. The first time, I typed values into a few cells on ...
0
votes
1answer
72 views
How can/does one dynamically adjust line width using Excel?
I have a series of figures that look like the "Basic Radial" SmartArt Graphic in Excel.
My goal would be to be able to change the width of each line using values in values found in my worksheet, thus ...
0
votes
1answer
127 views
Best way to insert big amount of data in excel sheet
I have to simulate a sheet with more or less 200 columns x 200 rows.
When I add the data manually, Excel shows the message "not responding" and I have to wait about 20 minutes till it fills all the ...
