Questions tagged [microsoft-excel-2010]
The 2010 version of Microsoft's spreadsheet application.
1,518
questions with no upvoted or accepted answers
1
vote
0
answers
26
views
How do I copy a row of values to another tab if a specific criterion is fulfilled?
I have two tabs with identical tables except one of them is supposed to contain those records that matches one of two values in one of the columns.
What I want is that whenever I add a new row to ...
1
vote
0
answers
45
views
Linking Voucher to Invoice
I am trying to link the receipt vouchers paid to the invoices number,
Using =VLOOKUP(C6;[Sheet2]$A$1:$G$1000;2;FALSE) helps when the "invoice number" is in column A, while in my case the "receipt ...
- 15
1
vote
3
answers
277
views
Excel "If Then" formula
I'm trying to write a formula which says "if sum of cell F24 to I24 plus sum of cell L6 to L9 sum up smaller than 50, show value in cell L9 in cell N9, and if not, subtract sum of cell F24 to I24 plus ...
- 11
1
vote
0
answers
719
views
Excel - Currency Conversion based on Average FX Rate in a Pivot Table Grouped by Date
Given 678,792 rows of FX data in excel from a 2 year period which shows the EUR-GBP exchange rate:
DDMMYYYY HH:MM FX
28/10/2016 12:15 0.89981
28/10/2016 12:16 0.89989
28/10/2016 12:17 0....
- 628
1
vote
1
answer
187
views
Conversion from Excel to CSV Correct Cells with Errors
I have a lot of cells which I want them to be formatted as texts. However according to Excel they are not texts and hence should be corrected to numbers instead. For example, "6814070833E000101" is a ...
- 111
1
vote
0
answers
28
views
Name Phone Data sorting from row to columns using Macro in excel 2010
i want to split my data and arranged in columns of name address city state n phone but the name is not joining all data in same row, can you pls help me to fix it?
following is the macro codes
thanks
...
- 11
1
vote
2
answers
277
views
In Excel, how do I multiply separated values with separated values in another column?
I have two columns of data. Column A contains the items, column B contains the quantity of the items. In the beneath example I would like to count the amount of "forks":
- A1: spoon|fork|knife
- A2:...
- 11
1
vote
0
answers
44
views
How to make Excel 2013 and 2016 PivotTables' line color black?
Excel changed the default line colors of its PivotTables (PivotTable Tools > Design > PivotTable Style > None) from black (2010 and before) to a very light grey (2013 and 2016). This makes the tables ...
- 121
1
vote
0
answers
33
views
How to create chart when source is in Row whereas time variable in Column Headers
I have data arranged as in this example:
I have to do various analysis which I was able to do with the pivot tables and charts. The charts are also linked to each other with each other via slicers ...
- 33
1
vote
1
answer
730
views
Find highest number of consecutive outcomes for each person in list
I've got an Excel list that looks like this (with the name in column A and the score in column B):
N. Nam Score
1. Jim 7
2. Jim 4
3. Jim 4
4. Jim 7
5. Jim 7
6. Jim ...
- 31
1
vote
0
answers
58
views
Excel to pdf not all content can be seen
I have a excel which is save in pdf format.
In the pdf one of my content does not reflect how it is suppose to look like in excel.
I have use auto fit and that is the result it shows.
When I manually ...
1
vote
1
answer
78
views
Excel table formatting for alternate row colours that get inherited
I have an excel table formatted to use excel table design which has alternate colours on rows. When I click in a cell and select insert table row above or below; I get a new row but the alternate ...
1
vote
0
answers
24
views
Creating reports in Excel without PivotTables?
I have a master Excel file which includes sections of data as well as multiple columns, although all data is in one worksheet. I want to be able to pull information from the file to create nearly ...
- 11
1
vote
0
answers
552
views
Error when connecting an Access query to an excel sheet -expression in query prevents connection being made
I have an Excel sheet which is linked to a query in my Access database (both 2010 version). It works really well and I decided that I wanted to have the same Excel sheet but to only contain data for ...
1
vote
1
answer
1k
views
How to Mirror Cells when the source is a data validation list? (Excel 2010)
I have a workbook with two tabs. The first tab is being used as a table for data validation lists I'm creating in the second page. For example, one list is called "Supervisors". This list starts cell ...
- 11
1
vote
1
answer
5k
views
Turn cells in a column a color based on another column
My spreadsheet has conditional formatting that turns a cell in Column M a color when the cell value is NOT "N/A." How do I also change the color in Column A based on the corresponding cell in Column ...
- 11
1
vote
0
answers
36
views
Excel 2010 populate based on another sheet
I need some help with dynamic range and auto populate.
I have a sheet with 2 Columns, 1 is the name and the other is the age between 6 and 12. There are 20 rows.
https://i.stack.imgur.com/dO3u2.jpg
...
- 11
1
vote
1
answer
165
views
VLOOKUP error when looking up date
I have been trying to use the CONCATENATE function in the Excel for creating multiple values of the financial statements for different years. My main formula is as shown:
=VLOOKUP(TEXT(CONCATENATE(A1,...
- 11
1
vote
0
answers
650
views
Excel 2010 - Combine specific sheet in >200 separate files into single worksheet?
I have tried solutions in other posts like this and so far nothing has worked :/
I have >200 separate excel files, and essentially I would like a specific sheet from each of these files combined into ...
- 27
1
vote
0
answers
68
views
Excel stays visible when changing desktop on windows 10
I am using office 2010 on a windows 10 computer (upgraded from windows 7, office was installed prior to the upgrade)
I have a problem that occurs whenever I have two or more excel spreadsheats opened ...
- 111
1
vote
0
answers
34
views
shift data down in one sheet when a new value is entered in a different sheet
I've reached a point where I'm quite sure I need VBA but I don't know VBA so I need your help. So:
I have a worksheet for organizing data on certain ceramic products.
In my main sheet (called '...
- 11
1
vote
0
answers
88
views
Plot a chart directly from a function in Microsoft Excel
I need an help with chart plotting in Excel (I'm using Excel 2016) let's say we have a simple function like:
y=1/x
Right now, to plot a chart based on this function I use two columns. In the left ...
- 45
1
vote
0
answers
810
views
Show Excel Cell Only If Another Has Data
I am trying to format the below Excel sheet so that when a date is entered into cell J1, cells G24 - G37 populate with a date a specific to what I need. I currently have the cells formatted as follows:...
- 11
1
vote
0
answers
167
views
Find and replace multiple values, according to table value - excel formula
I would like a FORMULA that change letters according to table of values that i make.
example:
êíöä = LOVE (because i made a table that show: ê=L, í=O, ö=V, ä=E).
example- Picture:
As you can see in ...
- 27
1
vote
1
answer
3k
views
Hide blank head rows in Pivot
Usually I use the filter to take out the blanks, but this is not adequate this time. Since I have three "columns" of my excel-sheet in the "Row Labels" section, called "Project-Category" (1-3), ...
- 11
1
vote
1
answer
546
views
Need simpler way of displaying data without merging cells (Excel)
Unmerged cells:
See attached screenshot. I want to show multiple limits/attachments/premiums for a single Insured/broker/industry.
I've tried merging rows (see below) for Insured/broker/industry but ...
- 201
1
vote
1
answer
3k
views
Weighted average, ignoring blank cells and avoiding the #DIV/0 cell error before sell has value in Excel
As stated in the title, I am having issues coding for a weighted average (in this example, if the client has bank savings and an annuity, I want the estimated return percentage average to take into ...
- 11
1
vote
1
answer
1k
views
why am I getting compile error when trying to copy data from master sheet to multiple worksheets
I am very new to VBA and macros so I apologise if this is simple. I have found similar posts but nothing that I am able to adjust to my problem. I found code to create a new worksheet for every new ...
1
vote
1
answer
1k
views
Why does Excel keep forgetting my network links that use UNC path string?
First, let me put out, I've googled and googled, and being a regular StackOverflow Answerer, I've even searched all over here. I've tried almost every solution I've found, including solutions based on ...
- 153
1
vote
1
answer
58
views
How to compare a cell with text to 2 columns?
I've tried and researched a lot, but somehow I can't find a solution to my problem:
There's A1:A50 containing various names (e.g. Bond) and B1:B50 also with names (e.g.James)(-> not necessarily in ...
- 11
1
vote
0
answers
1k
views
Pivot table with dynamic field names in Excel 2010
I am creating a workbook where the user can paste in their dataset in a worksheet. This dynamic range will be used to generate a series of pivot tables on subsequent worksheets. This works well when ...
- 405
1
vote
0
answers
182
views
Section of VBA code is stuck in a loop
From what I can see this code should work but it ends up stuck in a loop so obviously there is still an issue.
The intention is to start at cell E70 and paste a value obtained from cell X38.
Move ...
- 33
1
vote
1
answer
100
views
Microsoft Excel 2010 Counting unique values based on a changing condition in another column
Would like to have one formula to count the number of unique values in one column based on the value in another column. This other column is a calculated number (formula) so it changes.
Below is a ...
- 11
1
vote
0
answers
438
views
Ignore empty cells in graph doesn't affect the layout of the graph
I have just got an answer to this question, and now I have a new one.
Before I made the changes in the other question I had a graph that visualised the values in column M without going to the value 0 ...
- 144
1
vote
0
answers
401
views
Excel external workbook references- dynamic
I'm trying to solve an issue in Excel 2010 where I'm referencing external workbooks that aren't open (so INDIRECT isn't an option) and I'd like the cell reference to be dynamic.
The external cell ...
- 11
1
vote
1
answer
82
views
Aggregate a range of worksheets with errors
I have several worksheet in Microsoft Excel and I am currently adding them like this:
=SUM(MySheet1:MySheetN!A2)
This funtion adds the content of all A2 cells on all the worksheets contained ...
- 151
1
vote
0
answers
121
views
Shortcut for Direction Keys -- To keep hands on QWERTY end of the keyboard
I use a lot of Excel and finally got myself used to the ThinkPad pointer whereby I no longer need to take my hands off the keyboard for scrolling.
But now I need to take my right hand off the ...
1
vote
1
answer
3k
views
Carrying a conditional formatting formula from one reference row to another reference row
I have month wise reference row to use conditional formatting. They are arranged like Target Date as reference row and actual dates are arranged below the target date suppose it contains the actual ...
1
vote
1
answer
2k
views
Automatically run VBA macro whenever a cell value changes
I have a macro that works perfectly - except that I have to manually run it.
I cannot figure out how to have it automatically run when I change values in other cells.
Sub ...
- 69
1
vote
0
answers
212
views
Link with UNC path is broken after copying cells from Excel 2010 to Word 2010
I am trying to copy an Excel table to Word in Office 2010. Basically, if the hyperlinks are pointing to my local hard drive, there are no problem at all. However, if the hyperlink is pointing to a UNC ...
- 111
1
vote
1
answer
34
views
Referencing a range using hyperlink
I'm looking for a solution of hyperlinking a cell.
For example:
On Sheet one I have details
for XYZ from B1 to D5
for ABC B7 to D11.
On sheet two I have XYZ and ABC. If I click in sheet two ...
- 11
1
vote
1
answer
34
views
Restoring sheet size closes workbook
I have a situation where one particular workbook will open maximized, and then if I hit the restore button it just seems to close the workbook. The workbook is actually open, but invisible. I've ...
- 8,663
1
vote
0
answers
28
views
Linking data to chart using VBA
I'm trying to connect this data to my chart, but I'm getting application defined or object defined error .
Dim chtObj As ChartObject
For Each chtObj In ActiveSheet.ChartObjects
chtObj.Delete
Next
...
- 137
1
vote
1
answer
923
views
Selective conditional formatting when midnight is time in the cell
I have created a sheet where the first column is an hourly timeline starting with a user entered time and date. The user inputs the time and date in cell F4. Subsequently the timeline starts in cell ...
- 11
1
vote
1
answer
48
views
How do I add text in a cell based on information from a separate cell in Microsoft Excel?
Column A is a list of city names, and I want to add a column B for a list of the counties that correspond to the cities. Example: Charlotte Hall, Piney Point, and Lexington Park are all located in St....
- 11
1
vote
1
answer
125
views
Excel: Changing and updating a future value based on past occurrence and lookup in a table
I have a big dataset, dataset is in such a way that each time a customer is called a row will be created. So the customer Number is the unique ID here. Based on their current and past response type, ...
- 72
1
vote
0
answers
71
views
Excel 2010 Password Protected Book Accepts More Than One Password
I use Excel 2010 64 bit (14.0.6023.1000)
A workbook that is being protected by password "flu" and be unprotected by password "flu" OR " ab". Why is that? First of all, I thought beginning with Excel ...
1
vote
0
answers
2k
views
Is there a way to enter negative time into cell in Excel?
I am trying to enter a negative time value into a cell and receive the error "The formula you typed contains an error." I have tried changing to the 1904 date system but receive the same error. The ...
- 21
1
vote
1
answer
199
views
Date range for jobs & conversions SUMPRODUCT
I'm trying to run a report within Excel that shows the total number of jobs completed by employees within a date range and also a count of the number of conversions per employee within the same date ...
- 11
1
vote
4
answers
8k
views
Excel: count unique instances across two columns
I have a spreadsheet that has a column for cell sites and another column for frequency like this:
SITE FREQUENCY
MA1084 687
MA1084 687
MA1084 687
MA1084 4382
MA1084 4382
MA1084 ...
- 21