# 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 ...

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 ...

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 ...

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 ...

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 ...

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
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 ...

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,...

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 ...

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 ...

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(...

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 ...

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 ...

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:...

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(...

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 ...

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 ...

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 ...

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-...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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
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 ...

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 ...

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, ...

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 ...

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 ...

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
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 ...