Questions tagged [microsoft-excel-2010]
The 2010 version of Microsoft's spreadsheet application.
4,697
questions
-1
votes
0
answers
23
views
Need help to Format Big Number with condition in Excel
Change the numbers format of millions in 2 digits, if the number is less than 1 Million, it should show as 0.95 - How to format number with this condition in Excel
0
votes
1
answer
43
views
IN VBA, How to sum hours and minutes exceeding 24 in memory (not in ranges of excel)
I'm getting some data in the format of text(like for example "12:47" that means 12 hours and 47 minutes) from a URL document and calculating some parameters like overtime work, shortages and ...
0
votes
1
answer
36
views
How to compare two columns in Excel with a conditional IF statement?
I have three columns that have some expense figures: Actual (column A), Budget (column B), Variance (column C):
For these expenses, if Actual is greater than Budget, I want the variance column result/...
0
votes
1
answer
74
views
Formula for Matching Header to Value
This is a question that I may have difficulty explaining, so bear with me.
On one sheet I have a column which has several cells, some of which are blank, some that have a single value, and some that ...
0
votes
0
answers
14
views
max if functions in Excel to return text from a different row matching the max if conditions
I have an Excel spreadsheet with a summary table that contains credit card charges information from different sheets. The sheets have columns A to F with 'Date', 'Cardholder Name', 'Amount', 'Result (...
2
votes
1
answer
60
views
Find uniques and show corresponding values as comma separated values in Excel?
I have a table with large data containing family name and family households. And I am trying to get them in a unique list followed by the household names as comma separated values.
The table below ...
0
votes
2
answers
35
views
Compare data across rows in range
I am trying to solve the following problem in MS Excel:
There are two columns of data, one for types of food and one for how many of them are (in an inventory, say). I want a SINGLE formula that will ...
1
vote
2
answers
56
views
SUM select ranges of varying lengths in a single column using null/non-null values in second column to decide SUM indexes in an Excel formula
Example of format for the issue, I need to generate the third row that is a sum of each color block:
I have an odd set of columns in a very large dataset, and I am not great at Excel. I could be way ...
0
votes
0
answers
32
views
How can I easily make a chart showing what weeks a task was completed or not?
I'm trying to make a chart that shows who completed an inspection for each week. At my work, we run Sunday to Saturday and within that time period, we expect our guys to submit a truck inspection.
I ...
0
votes
1
answer
59
views
How to count the number of cells that contain certain text while excluding cells that start the same but end different
In our company, the truck numbers in our database all start with "TK" A regular truck would be "TK123" for example. We also have stipend trucks and these all start with "TKPB&...
0
votes
1
answer
149
views
Sum per quarter with extra bits
I have a table that looks like this
Date
Transaction
01/05/2011
£100,000
07/06/2011
£200,000
10/07/2011
£123,123
And so on, the data continues from 2011 to 2024 and is about 200 entries,
I'm ...
0
votes
1
answer
88
views
Add Link Reference in Excel using OneDrive
I have two workbooks, and I am trying to link the two files in Excel O365 in OneDrive.
I have had limited success doing the following command on my PC or directly on OneDrive. Placing this command in ...
0
votes
0
answers
54
views
Unable to edit the required text and update auto-date
I Should be able to edit any text updated except , "Posted", "Closed" in J Column. Every time I edit the text the date should be auto updated in next column cell. For example, I ...
0
votes
1
answer
109
views
How to Autoincrement the Column index number by dragging VLOOKUP formula down
I need to drag a Vlookup down and have the Column index number in to increase by 1 every row I drag it down.
So for example:
Row 1:
IF(VLOOKUP(A2,'Jan 2024'!$D:$BP,**35**,0)<=0,"IGNORE",...
0
votes
0
answers
13
views
Automating and filtering data from one sheet in Excel to four different sheets
I have two separate sheets in Excel that shows data in the form of (O) and (T). They will be further separated in to four sheets named A, B, N, S.
How do I go about automating data keyed in the first ...
1
vote
2
answers
71
views
Converting Hours and Minutes to Minutes Excel
Goal: calculate pay for elapsed time.
Short question is how can I convert the time value of hours and minutes to just minutes in Excel to calculate payment for hours worked? For example, I have a time ...
0
votes
2
answers
54
views
How to properly count multiple criteria from ranges in a separate sheet?
I have a master data table in a worksheet (worksheet name 'Data'; table range called 'MasterData'). This includes data about participants in a conference (name, nationality, etc.) and whether each ...
0
votes
0
answers
53
views
Using Excel: how can I change to colour of a cell based on whether 3 cells in the same row contain ANY text?
sorry for the novice question. I have a spreadsheet looking for sign off from colleagues after reading a document. I would like cells in column 'O'(sign-off complete) to turn green if there has been ...
0
votes
1
answer
78
views
How to create a sorted table summary of the count of a categorical column in Excel?
I have a dataset like:
Animal fruit
cat apple
dog grape
cat cherry
cat pear
dog cherry
horse melon
cat lemon
I would like to create a summary table counting each occurance ...
0
votes
0
answers
105
views
How to create Excel histogram from frequency table?
I have an Excel table with two rows.
One header row with positive integers representing different event properties and another row with the frequency of events with said property number.
Here's a ...
0
votes
1
answer
28
views
Excel - AverageIfs with multiple valid criteria in different columns
I'm trying to get excel to give me the average of multiple objects if they meet a criteria.
I want to calculate the average of the last column (SLA in days) for the lines that contain Country=ES/PL, ...
0
votes
2
answers
539
views
My Excel files show some blue dot lines between rows
For some reason when I open a certain file in Excel, it shows some ugly dotted blue lines between rows.
I kind of eliminate them using the menu [View] -> Workbook Views and changing from Page Break ...
1
vote
1
answer
78
views
How to repeat rows with specific times and modify some variables at the same time?
The sample dataset is as follows:
name
Number of days of Annual leave
Date from dd/mm to dd/mm
Amy
3
01/10-03/10
Iris
2
04/10-05/10
The dataset that I want:
name
Number of days of Annual leave
...
0
votes
2
answers
73
views
Counting the number of times a value appears in a range of columns
Consider the following table which shows a generalised, abstract version of my specific real-world problem (which I will also illustrate).
Each "1" or "0" is part of a column ...
0
votes
1
answer
145
views
How to get the value from a cell given a name box in Excel
I have a situation that I need to do a concatenation to add a single quote in the beginning and end of whatever is at column Q, and repeat the value twice by row in column I:
So far I was doing ...
0
votes
1
answer
157
views
EXCEL show total count from sheets when using multiple drop down lists
I have a workbook with multiple sheets. On each sheet you can select a user from a drop-down list and then select an item from a different drop-down list. There can be multiple instances of user and ...
0
votes
1
answer
47
views
Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?
I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...
1
vote
0
answers
48
views
Using countifs with more than one condition using references not values
Am trying to apply the countifs function to two columns of my data sheet with two different conditions to meet (one for each column) using cell references in the criteria. The following syntax has ...
2
votes
1
answer
327
views
How to create a stacked column chart where each category has unrelated subcategories
I have the following set of data
I want to create a column chart with 3 stacked columns(one for each category). The sum of amounts for each category equals 100. The issue I'm having is that the ...
0
votes
1
answer
122
views
Is it possible to restrict a VBA macro command to just one workbook?
I have macros added as commands to a specific workbook. But a new workbook will show the commands, and if I click it, it just opens the previous workbook. I would rather them not be there in new ...
0
votes
1
answer
50
views
Formula to use to get desired result
I have 2 tables. Table 1 has raw data, Table 2 is what I'm using as a report. Can anyone suggest a formula, to be used in column D in Table 2, to pull all the negative amounts from Table 1, based on ...
0
votes
2
answers
147
views
How to make Excel subtract and add (not sum nor subtotal) only the values of the cells shown after using a filter
As shown in the image below, when I use the filter in column A to show only apples, the balance column shows wrong numbers because it takes into account the hidden cells which belongs to oranges.
I ...
0
votes
1
answer
1k
views
References to blank cells become 0's
I have a spreadsheet in excel where I need to make some references to an area in another spreadsheet. I simply use ='EARTH'!B9:N323, which is an area mixed with some columns of text and others of ...
0
votes
0
answers
20
views
How to automatically enter a table value into another calculation sheet, and get the returning outcome value back to the table?
Not sure if I formulated that in an understandable way. Basically, I have sheet1 with a table filled with characters with their theoretical DPS where they just attack forever. For a more realistic DPS,...
0
votes
1
answer
59
views
How to stop numbers from automatically opening hyper links in Excel?
So I have ID numbers that I hyper link to a website. It's automatically hyperlinking the whole cell, preventing me from editing the text by double clicking the cell.
For example, let's say I have ...
1
vote
1
answer
247
views
Divide a value based on another cell value and split it eventually by the divisor from where an another cell is matched with another cell value
I have a formula to divide a value by an another cell value and split the value eventually. This works fine but it splits from the starting month.
My current formula:
=IF(COLUMN()-COLUMN($F5)<$I5, ...
0
votes
1
answer
32
views
Dependent dropdown in excel
I have a market, submarket, store no. Columns, I want to use a drop-down to select the market submarket and store no. to display the details of the shop in Excel.
When I choose a market-only submarket ...
0
votes
0
answers
40
views
Formula to Round Down Number in cell
I need to round down the highlighted numbers in Column Q of the attached screenshot. I currently have formula of P/F starting on Cell Q5 through Q46. I am diving the quantity on hand for each part ...
0
votes
0
answers
254
views
Excel Solver Error: The objective cell values do not converge
I've been consistently getting the error above when trying to get the parameters for my logistic regression Does anyone have any ideas why this may be happening?
I see that the solver is making the ...
0
votes
1
answer
238
views
Excel Help - If two cells meet a certain criteria input a value into another cell
I'm a bit of an excel beginner so apologies in advance.
I need my worksheet to automatically input a price into a specific cell.
I have a table in one sheet which has tables for each product brand and ...
0
votes
2
answers
128
views
Round each instance in Sumproduct before sum
I am trying SUMPRODUCT Formula on lets say Column A and Column B. Column A has Patient referrals and Column B has percentage referred for each hospital. I am using this syntax:
ROUND(SUMPRODUCT(A1:A10,...
-1
votes
1
answer
53
views
How to generate a table of names based on complicated criteria in Excel
Screenshot:
Hi experts,
I am trying to make a table in Excel that can generate a "Doc #" for each day of the month (for Monday to Friday only) based on availability. I've included a ...
0
votes
0
answers
15
views
Highlighting a cell when it matches 2 prior cells
I am trying to get a 3rd cell to change colors if this cell and the 2 prior cells have the same response in it. For example if S133, U133 and W133 all have an N in them, I would like for the 3rd N ...
0
votes
0
answers
396
views
How do you get the raw data of an Excel file?
The European Union (EU) provides an Excel file that has data on trade between various countries. Their Excel file, however, only shows data between countries based on a filter function. It seems they ...
0
votes
0
answers
112
views
Excel Macro error prompting Save As dialog box with weird file name
I made a code for a friend something like this :
Sub SaveToMainData()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
pfn = ThisWorkbook.Path & "\Backup\MainData.xlsb&...
0
votes
0
answers
10
views
Why does the data point appear in my pivot table if the table it is referencing doesn't contain the same data point?
The pivot table contains the data point:
The pivot table is referencing this table:
As you can see, the data point is not in the table. This table is connected to a query. I am guessing the reason ...
1
vote
1
answer
1k
views
Why are my columns disappearing after I refresh the data?
The table below is connected to a query. When I refresh the table below, the last two columns' data, S and T, disappear. All the cells in these two columns contain a vlookup. How can I get the vlookup ...
0
votes
0
answers
870
views
I am trying have data appear in the "Values" field in a pivot table, but the pivot table keeps summing the values that have the same date
In the pivot table, when there are two or more values assigned to a date, they sum.
I don't want the values to sum. I want each value listed, even if the date is same for the values. I've tried going ...
1
vote
1
answer
3k
views
Excel VBA - add date and time to a file name as FILENAME_MMDDYY_HHMMSS
A few weeks ago I posted about using VBA To prompt me to add a file name when saving an excel file using a macro. Thank you for all the help. I got it to work!!
Now I have another similar question.
...
-2
votes
2
answers
6k
views
Conditional Formatting with greater than & less than parameters based on date in cell
Trying to conditionally format a cell based on the date in the cell. My ultimate goal is to try and track warranty expiration dates on some of the product we maintain. Within 30 days of expiration ...