Questions tagged [microsoft-excel-2007]
Microsoft Office Excel is a spreadsheet application written and distributed by Microsoft. Use this tag for problems specific to Excel 2007 for Windows.
2,250
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 ...
- 9
1
vote
1
answer
79
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 ...
- 9
0
votes
0
answers
20
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 ...
- 9
0
votes
2
answers
30
views
Excel - Formula Based Individual Durations and Total Duration Calculation from Slash-Separated Time-Strings
I am trying to calculate (using worksheet formula based solution only) individual time-durations and total time duration from the slash '/' separated time-texts in two cells (each value of first cell ...
-2
votes
2
answers
55
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
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
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.
...
0
votes
1
answer
93
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
16
views
How to connect the color of a legend in a horizontal chart for two groups?
I am trying to display the results of the performance of a set of rooms based on their responses to a test.
My goal is that the results above or equal to 50% to be marked as achieved results and those ...
0
votes
0
answers
81
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
432
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
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
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
2
answers
146
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
35
views
Color rows alternatingly according to change in column value – without hidden columns?
I have an Excel sheet with multiple columns, where in one column subsequent values can be identical or different, so that the rows form a number of groups. I would like the rows belonging to a group ...
- 799
0
votes
1
answer
52
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
0
answers
62
views
Call a sheet using a variable name
I have this code in wich I add a sheet with a certain name and then I need to call this sheet to get a cell value but it doesn't assume.
Dim nome1 As String
Dim tableName As ListObject
Dim addedRow As ...
0
votes
1
answer
109
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
-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
1
answer
90
views
running 2 excel versions at once, cant associate filetype to older version
I have excel 07 and the latest Excel 365 (I think thats Excel 16) both installed in windows 10. When I just had 07, I had no problem associating csv and xlsx files with that program. Now that I have ...
- 1,007
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
0
answers
40
views
Excel 2016 to open in the same place we closed it, like Excel 2010
Slow to the game, I know, but I just upgraded to Office 2016 from Office 2010. In 2010 when I opened Excel, it opened on the same screen, in the same position, no matter how many instances I opened......
- 441
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,554
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
0
votes
1
answer
521
views
How to set the language for formulas to English instead of the default Excel language?
Excel 2007 is installed in French language. So all formulas are in French, which is not handy:
=RECHERCHEV(...)
instead of
=VLOOKUP(...)
Question: how to set the language to English for formulas, ...
- 1,554
0
votes
1
answer
45
views
How to reference specific cell location for conditional formatting
I'm trying to make the conditional formatting to reference each cell location that is visible. (It has been filtered)
ActiveSheet.Range("A1:P24").AutoFilter Field:=3, Critical:="Tuesday&...
0
votes
2
answers
1k
views
How to make cell in Excel unclickable and unactive even when click?
First and foremost, I do know on how to protect the Excel files and I also know on how to Locked Or Hidden cell. The unlock cells let user to edit the cell while the lock cells will not let the user ...
- 101
0
votes
1
answer
21
views
How to setting and sort row and column simutaneously
From images below, the left is the base table and the right is the table that want to show. Here, what to I want is filter for both column and row. We do choose specific date (I did not put DATE ...
- 101
0
votes
1
answer
29
views
How to sort in column and row in order to get specific data in table Excel?
Given that image below is a simple table where the variable is DATE and TYPE. I do know on how to to filter of things in terms of only using one variable.
For instance, if I create those table, I can ...
- 101
0
votes
1
answer
215
views
Need Formula to calculate number of months into 2023 given duration and start date
In an excel file I have been given the start date of a contract and it's duration. I need to get the number of months this contract will be active in 2023.
So for example if the start data is 10/1/...
- 1
0
votes
1
answer
92
views
EXCEL formula column how to return cell reference
The values in columns E:G are calculated from data in columns A:D. The respective formulas are as follows:
E=A+B
F=B*C*D
G=D-C-B
The excel sheet looks as follows:
Assume I want to find which formula ...
0
votes
1
answer
284
views
Count number of occurences of [x] amount of zeros only after the occurence of first 1 in a row
I have a table which has only 0s and 1s in ~50 columns and ~13k rows.
What I want to do is to calculate how many times did a series of specific amount of zeroes occured in each row, after the first 1 ...
- 1
0
votes
0
answers
23
views
Data Source to Excel Date
I've been trying to import data from excel to google sheet. The data I got was in the format of dd/mm/yyyy, which makes sense as we use it in where I live; however, once I tried to copy paste the data ...
0
votes
1
answer
146
views
How do I replace text in rows by the respective columns?
I need to replace text "vegetables" in rows 1-5 entirely with the respective car make names in column A.
For example row 1 should find the word BMW and replace it with all words containing &...
- 1
0
votes
2
answers
198
views
Highlight a cell that is greater than or equal a value when another cell is a specific text
I am creating a tracker,
In cell A1 I want to define the value with a 1, 2,or 3.
In Cell B1 I want to highlight if the Cell Value >=60 when A1 is a 1,
and In Cell B1 I want to highlight if the ...
- 1
0
votes
1
answer
64
views
sum of cells if they are less than 1000 if more than 1000 then input difference
I am really not sure how to word this.
I am trying to come up with a formula that the weekly total does not exceed 1000 anything over is non-qualified "sales amount"
- Example:
Monday - ...
- 1
1
vote
1
answer
101
views
VBA Changing font color
S.No
Topic
Priority
Action
From
Date
Responsible
Target
Done
Comment
1
FuSi
3
kjkj
SSR0
26.07.2022
MR.BBBB
18.08.2022
kjkjkj
2
FuSi
3
kjkj
SSR0
26.07.2022
MR.BBBB
19.08.2022
kjkjkj
simple task. I ...
0
votes
1
answer
33
views
In Excel, How to transform data in row seperated by commas to column with value?
I have information in excel organised like this.
name
type
data
Thomas
table,height
red,180
Jail
cross,dog
young,cow
How do I convert it to this?
name
table
height
cross
dog
thomas
red
180
Jail
...
- 3
0
votes
1
answer
25
views
Adding column description of one spreadsheet sheet in another spreadsheet of same excel
I have an excel sheet having 4 different spreadsheets. I need description of column header of three spreadsheets in 4th spread sheet.
Example:
Suppose I have sheet 1 with 6 headers for 6 columns, ...
- 11
0
votes
1
answer
22
views
How to properly format combinations generated in VBA script for Excel?
So I have this VBA script that I'm using in Excel to get all possible combinations from data in 4 separate rows. It works well, however I'm trying to figure out how to make it not use a / at the end ...
- 101
0
votes
1
answer
272
views
Calculate and display Best Seller?
I'm wondering if anybody knows a solution before i start trying and failing for a few hours...
I have a table of all my orders since March, which includes the value of the orders, the date the order ...
- 15
0
votes
0
answers
39
views
Comparing lists of names in excel to output a new list of matched names
I have a list of several sheets of names in excel. My goal is to be able to compare one sheet of names against the others and have an additional list of names that match up.
Maybe it will be clearer ...
- 1
0
votes
1
answer
19
views
how do i show formula result if any two selected cell has value in excel?
I have a scenario where i have two cell for input Data E24 and I24 and one cell for calculation k24. What i want is if there is value in any of those input cell the calculation should show in the ...
0
votes
1
answer
73
views
How can shift page number all the way to the edge of the margin in Excel 2007?
I need to move page number from here
to here
Is that possible in Excel 2007? I'm not even sure what to search. To be clear, I know how create page numbers and how to position them in the basic sense....
- 2,714
0
votes
1
answer
48
views
SUM function returning 0 value
DATA SET PREVIEW
I Used the sum function to calculate the time, as the trip duration is in seconds i used =SUM(cell/60) and then convert them to HH:MM:SS, but some of the cells are returning 00:00:00
...
0
votes
0
answers
24
views
Auto adjust cell values if one cell values changed
how to adjust values automatically if cell values A1 has changed means then the changed values has to adjusted with B2 values and the final cumulative values remains unchanged.
Suppose i have list of ...
- 11
1
vote
3
answers
937
views
Calculate average in excel excluding cell contains date or zero or text
In B6 I want to calculate average for range c6:h6 skipping cells which has date, zero or non text values.
Result should be - 400
I tried using averageif with filter ex: =AVERAGE(FILTER(C6:H6,ISNUMBER(...
- 111
1
vote
1
answer
85
views
Is it possible to link an entry in a filtered sheet to the master sheet, matched on a key variable? (Excel)
I am trying to link a column in a filtered sheet to another column in the master sheet using a key category (i.e., id). Below is the illustrated dataset:
[]
As shown above, I have the master sheet, ...
