Questions tagged [microsoft-excel-2010]
The 2010 version of Microsoft's spreadsheet application.
4,660
questions
0
votes
0
answers
4
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 ...
- 11
1
vote
1
answer
72
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 ...
- 11
0
votes
0
answers
19
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 ...
- 11
1
vote
1
answer
151
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
48
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 ...
- 1
0
votes
1
answer
26
views
Is it possible to seperate cells like based on a variable?
Here I have 2 pages one named Input and The other Output
in Input I have In cell C2 the number of lanes which is 6 in this example and the D2 the number of Heats which is currently 4 :
if this ...
- 3
0
votes
1
answer
54
views
Excel - highlight a specific number between even or odd
I was not able to find a solution to the following task:
I need to highlight all numbers (in this case the number was "5") between two even numbers on the left and right in a large selection ...
0
votes
0
answers
12
views
splitting whole number into different ranges
I have energy bill og 350 units.
my tariff is
0-100 Rs 3
101-200 Rs 4
201-400 Rs 5
How do I split350 units into three columns 0f 0-100,101-200,201,400
0
votes
1
answer
19
views
Creating Exception in Excel Sum to Avoid error Value
this is the formula:
=DATE(YEAR(Q14),MONTH(Q14),DAY(Q14)+(R14))
I'm using to add date how do I make exception that it keep the answer blank until I enter the date in Q column
- 1
1
vote
2
answers
83
views
=CELL("filename") doesn't work with multiple Excel files open at the same time
Question: How do I get the name of the current document in a way that doesn't break down when multiple documents are opened at the same time?
I want to write the filename of the current Excel ...
- 203
0
votes
1
answer
28
views
Excel/LibreOffice Calc - Setting a value dependent on another value as that second value rises at a uniform rate
Essentially the value in C24 is dependent on the value in C18.
The value in C24 should be 33 if the value in C18 is 2230 or less.
The value in C24 should be 39.5 if the value in C18 is 2950 exactly.
...
1
vote
3
answers
32
views
Excel Code that limits the characters per cell to 6
Hoping someone has an answer for me.
Im trying to generate code for Excel that produces every available alpha-numeric outcome Between T5A0A0 and T6Z9Z9 in order: letter, number, letter, number, letter,...
- 11
0
votes
1
answer
18
views
Assign A Numerical Value to Non-Numerical Text in Excel for Rota
I am trying to come up with a way to calculate the hours in Excel automatically for my rota. The issue I'm running into is that we do not list "off times" on the schedule, so the shifts are ...
0
votes
1
answer
37
views
Find next cell within column that is a number, starting from reference cell in another sheet
I think this sounds simple but I'm having a bit of trouble figuring it out.
I have 2 sheets, the Sample Sheet attached and an Input Sheet.
In the Input Sheet, I input one of the green shaded values (...
0
votes
1
answer
89
views
Can anyone please tell me how can I convert 1M, 1B, 1K to number format in excel? [duplicate]
Can anyone please tell me how can I convert 1M, 1B, 1K to number format (like 1K = 1000) using formula in excel.
Advance thanks for help.
0
votes
1
answer
31
views
How can I plot two lines sharing data and a single y column?
So I have this VERY simple assignment from economy class, where we have to plot this offer/demand table:
QD
QO
PREU
10
120
100
20
60
80
30
30
60
40
15
40
50
0
20
And in the book, shows how &...
0
votes
2
answers
47
views
Excel formula to compare a 6 cell row against a reference row and return value that are missing
I need to work out a problem I have with formula logic.
there's a picture with the exact outcome I'm trying to achieve below
i have a long list of rows that contain cells with criteria in them that ...
0
votes
0
answers
78
views
Using Unique/ToCol as a formula within data validation to create a dropdown list
I have a table that contains 8 columns. Separately, in Column A, there is a list of company names. For each row in column A, I use a filter function that returns any column from the table that ...
- 123
0
votes
2
answers
407
views
#VALUE! Error when trying to use the Filter function to filter on mutiple criteria
original_name
match_name_0
score_0
match_index_0
match_name_1
score_1
match_index_1
AGI Partners, LLC
agi partners llc
100
5273
agi partners llc
100
5273
AXA Investment Managers S.A.
axa investment ...
- 123
0
votes
1
answer
23
views
How to get a data from an external Excel files?
I'm trying to get some data from the excel files in a folder, and I already know how to get some data from an external files.
How can I you get the data based on the file's name without typing it ...
0
votes
0
answers
42
views
How to Sum all Cells above in Excel, after filtered?
How to sum all cells above, after filtered. I try to use =SUM(ABOVE) but get #NAME? error.
I am using Excel 2010
I use the method in https://superuser.com/a/661848/886687, i.e.
=SUBTOTAL(109, INDIRECT(...
- 113
0
votes
0
answers
21
views
Have problems sharing an Excel Sheets
I am working on an Excel Book and I want to share it with some people.
All the Sheets are linked to another Excel Files, so I need some help with sharing this Excel without any #REF or ERROR in the ...
- 1
0
votes
0
answers
25
views
How to idenitfy cells
Hi good internet people,
I wonder if someone might be able to help me figure this out. My excel knowledge is very basic.
Using an end date (Fully Grown) and subtracting a caluculated number of days I ...
- 11
0
votes
0
answers
21
views
Variation on "Excel create table with all combinations"
I have a similar question to this Excel create table with all combinations I'm looking to do the same (Provide all combinations of each row variable) but reference the Variable column value. Like this:...
- 1
0
votes
2
answers
48
views
Compare 2 numbers in excel
I'm trying to display the following but I'm having trouble writing the formula and would be grateful if anyone our there could help.
If B2 & C2 are not blank, display C2, otherwise display B2.
=IF(...
- 11
0
votes
0
answers
31
views
Index the first instance of consecutive >0 data in a row of information?
I am using the following formula to identify weeks of no usage over 26 weeks of information:
{=MAX(FREQUENCY(IF(J4:AI4<0,COLUMN(J4:AI4)),IF(J4:AI4>0,COLUMN(J4:AI4))))}
where the data in J4:AI4 ...
0
votes
0
answers
19
views
Color duplicates rows in two different sets of data and also the rows which are present in both sets of data
Note
When I say rows, I mean cells in Column A only and not the whole row.
Data
VBA Code and Data Sample at the bottom
Background
There are two sets of data. Lets call the data on the top, Data A and ...
0
votes
0
answers
27
views
How to use Excel autofill on a sheet reference?
My current formula is =INDIRECT("'"&$U$4&"'!B5") where U4 is the name of a worksheet I am referencing from and B5 is the source cell in the referenced sheet. When I drag ...
- 1
0
votes
0
answers
31
views
Excel formula to look at a Row
I wrote an excel formula to return(true and false) if three criteria's matched in the row.
For ex) If the Row contained
Name , D, and 1 then it is true
This is the formula created:
=IF(AND(COUNTIF('...
0
votes
0
answers
57
views
VBA code to copy data from different works book to specific sheet of master workbook
I have to copy data from 15+ workbooks and combine it into a different master workbook. All these 15+ workbooks are identical and contains a sheet(tab) named 'Comment' and have same header names for ...
- 1
0
votes
1
answer
35
views
I am trying to make a formula in excel using if blank statement for multiple cells. Possibly two different arguments
My current formula is =IF(ISBLANK(J2),M2*AB2, IF(J2>1, AC2))
Important to note my formula in M2 is =IF(ISBLANK(L2),"",L2-G2)
AB2=250 AC2= 550
The code runs as G2 gets a delivery date ...
0
votes
0
answers
38
views
How can I combine two Excel formulas into one?
In a spreadsheet, I have five columns (total) and four of them have the components of the students' grade. The fifth has the final grade.
Now, I need to combine two formulas so that when the four ...
- 13
0
votes
1
answer
68
views
function LOOKUP() don´t return result expected
I'm trying to use lookup function with a simple list of text, but in one sheet in my pc is not function,i write =LOOKUP("Zapatilla 4",A28:A34) in the table
description
other info
Zapatilla ...
0
votes
2
answers
140
views
Splitting a column in excel into two columns keeping the leading zeros
Trying to find a faster way to change this format from one column (A) split into 2 columns (B and C) as in the example below:
A
B
C
978-BD71151-001-0
978-BD71151-001
0
978-BD71151-002-1
978-...
- 1
0
votes
1
answer
47
views
How to put DATEDIF and IFISBLANK together?
I have 3 total cells I'm working with. One houses today's date (A1), the second has the date the request was made (B1) and the third has the date of the event (C1). My team had previously used DATEDIF ...
- 1
0
votes
0
answers
42
views
SUMPRODUCT Query
I'm trying to use SUMPRODUCT or any other formula to get an end calculation of how many occurences "Louise" appears in column F and then how many Sessions she had in the MONTH of September ...
0
votes
1
answer
104
views
Get "most recent" start date, and "most recent" end date in row
I have data with 3 columns fields, start date, finish date and progress.
Users Report Example:
Start Date
End Date
Progress
01/12/2022
01/13/2022
20
Users append to this file adding these three ...
- 1
0
votes
0
answers
111
views
Alternative for ADDRESS function that returns an array
I primarily use LibreOffice Calc instead of Excel. When creating my spreadsheet, I relied upon the fact that the ADDRESS function was able to take a cell range and return an array of cell reference ...
- 101
0
votes
0
answers
163
views
How to keep a blank row in Power Query when there's no data from the original worksheet?
I appended multiple power queries into one table because I am working with more than one worksheet. Please see my example image below. When I delete the data for Bob and Betty from the "...
0
votes
1
answer
46
views
X.Lookup fails in excel
Good morning,
trying to create a x.lookup using data from a .txt file, copied to excel, using the text to column function to sort the data. small example below :
2.1 Dig. Oil Meter - Max. Oil ...
- 1
0
votes
1
answer
102
views
Drag down formula from multiple sheets on one sheet
I have 19 Sheets on each sheet in cell B17 I have a result.
On my first sheet I have cell numbers from 1-19.
In my first sheet I type in cell one the Formula ='sheet 1'!$B$17. I have the number in ...
- 1
0
votes
1
answer
74
views
Return value of cell based on other cell
I want to fill different data in one column and link it with the data filled in a different column. For example: I want to use "animal, bird, reptiles" in the cells of e.g., column A and I ...
- 1
0
votes
1
answer
29
views
Format of Excel drop down list
When choosing from a drop down list in Excel, is it possible for the cells to change the format when you choose a specific subject?
Each subject has a different format for the cells.
- 1
-1
votes
1
answer
46
views
multiple codes in a cell to convert to text
I have a cell which contains multiples codes that are pipe delimited - up to 5 codes. Additionally, I have a decoder for the codes on a separate excel sheet. I basically want to replace the codes with ...
- 11
0
votes
0
answers
59
views
Count dependents in other sheets VBA
I want to count the number of dependents of a cell in same sheet as well as in other sheets using VBA.
I have used below code and results are in the picture.
enter image description here
Sub tst()
Dim ...
- 47
0
votes
3
answers
258
views
Excel keyboard shortcut to 'continue' number pattern
Excel keyboard shortcut to 'continue' number pattern
Say in a collection of adjacent cells, I have a sequence of numbers such that in every cell one number appears: e.g. 2,4,6,8,10.
In the normal, ...
- 103
0
votes
1
answer
12
views
I am working on target follow up tool on Excel. Issue related to conditional formatting
The scenario is:
Cell T2 has target date entered already.
Next cell U2 is blank and it is where actual date is to be entered when action is taken.
Target is 5 days.
If the date entered in U2 is with ...
- 1
0
votes
0
answers
49
views
How do you generate a list on excel based on complex criteria when the data set is not unique?
I am trying to build a excel sheet that automatically generates the list based on the 3 criteria (each criteria has a dropdown). Below is the template I created.
Criteria list:
Purpose, loan type and ...
- 1
0
votes
3
answers
67
views
How to create a list of items in an Excel cell, and be able to data filter this column?
Can we have a list of items in an Excel cell, such that the rows can be filtered with Data Filter?
Exemple: how to filter by column B and keep only the rows that contain [item4]:
?
Notes:
we could ...
- 1,544
1
vote
1
answer
32
views
An Excel sheet that will have 2-3k columns and 100 rows, is it better to use vlookup/hlookup or index/match from an efficiency/size perspective?
Thinking about sheet design and unfortunately, it looks like it's going to have a lot of rows and columns that will have to be looking up values. From a size/speed/efficiency perspective, is it better ...
- 123