Questions tagged [microsoft-excel-2010]

The 2010 version of Microsoft's spreadsheet application.

Filter by
Sorted by
Tagged with
-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
Kritika Sood's user avatar
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 ...
alireza Seraj's user avatar
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/...
Samantha Lima's user avatar
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 ...
chunky-coal's user avatar
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 (...
user1896365's user avatar
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 ...
DigitalNomad's user avatar
  • 1,678
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 ...
zz20s's user avatar
  • 103
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 ...
Drail's user avatar
  • 13
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 ...
Cole's user avatar
  • 3
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&...
Cole's user avatar
  • 3
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 ...
DETIVA's user avatar
  • 101
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 ...
Ernst Binder's user avatar
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 ...
raju nagavelli's user avatar
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",...
Owen210's user avatar
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 ...
gigachad2220's user avatar
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 ...
dryadsdad's user avatar
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 ...
matt's user avatar
  • 3
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 ...
Laura Harvey's user avatar
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 ...
Ray Larone's user avatar
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 ...
136's user avatar
  • 103
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, ...
Monik's user avatar
  • 1
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 ...
KansaiRobot's user avatar
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 ...
doraemon's user avatar
  • 157
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 ...
Nils L's user avatar
  • 103
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 ...
William Studart's user avatar
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 ...
SCT73's user avatar
  • 3
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 ...
Juan P.'s user avatar
  • 13
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 ...
Gabriel Figueira's user avatar
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 ...
Mohit Singh's user avatar
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 ...
J.P.'s user avatar
  • 5
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 ...
user1832229's user avatar
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 ...
Falcon Pilot's user avatar
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 ...
BearCoder's user avatar
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,...
Brian's user avatar
  • 1
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 ...
Danny P's user avatar
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, ...
Darious's user avatar
  • 11
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 ...
NAS_2339's user avatar
  • 103
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 ...
Juan P.'s user avatar
  • 13
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 ...
user avatar
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 ...
Paris Golding's user avatar
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,...
Ammar's user avatar
  • 1
-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 ...
EOCZ9's user avatar
  • 1
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 ...
Amy's user avatar
  • 1
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 ...
Will M's user avatar
  • 101
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&...
karma's user avatar
  • 111
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 ...
anon1212's user avatar
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 ...
anon1212's user avatar
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 ...
anon1212's user avatar
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. ...
Kirk Thompson's user avatar
-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 ...
LonlyBoy's user avatar

1
2 3 4 5
94