Questions tagged [microsoft-excel-2010]
The 2010 version of Microsoft's spreadsheet application.
4,697
questions
0
votes
1
answer
61
views
How to perform same operation on non concurrent excel cells
I am trying to perform the same operation on excel on a range of non-concurrent cells.
Let's say I have range C:C, within this range I need to perform this operation IF(ISNUMBER(C2),(C2*A2),(0)). How ...
0
votes
1
answer
5k
views
How to conditionally format if not on list?
Every tip online about conditional formatting and a list is about highlighting cells that match on the list. How can I highlight cells that don't match (ignoring blank cells).
For example, a list of ...
0
votes
2
answers
606
views
Excel VBA - Dynamic Array not Working
I am trying to make an array that stores all of the values in Column A from "Workbook B" so I can then reference and see if a cell's value is in that array in Column A from "Workbook A&...
0
votes
1
answer
2k
views
formula to autofill with condition and skip blank cells
I have the master sheet and data sheet (more than 1 but same format)
This is the example of the data from the sheet
Project (Column C)
Details (Column E)
Project A
Laptop
Printer
Camera
blank
...
0
votes
1
answer
40
views
Is there a way to count how many cell with a certain value are above a certain cell in excel?
So I have a list here of whatever that are ranked 1-40th. Only those that were top30th in the previous ranking will have a letter designation. jan-20 column shows the previous top30th having the ...
1
vote
1
answer
269
views
How to setup an offset function so it will take values from table horizontally and will go 2 rows down every 6 cells
I'm trying to automate prices on my parents online shop and I've got pretty messy data to work with. I converted csv file with prices from site to xlsx and pasted there tables from the database.
I've ...
0
votes
2
answers
11k
views
Excel VBA - If Statement Checking If Value is in Array
I have a lot of variables I have stored in an array. I am trying to use an If Then statement to see if the value of a specific cell is in that array. Here is my code:
Dim Vars1 As Variant
Vars1 = ...
0
votes
0
answers
700
views
Attempt using ISBLANK formula not working with conditional formatting
I am attempting to use the ISBLANK formula for conditional formatting. What I am wanting to do is if the C column cell is blank then the called out rows in that cell have no formatting. Like this:
If ...
0
votes
1
answer
409
views
Excel VBA - Selecting Row in For Loop
I have a for loop that goes through Column I and looks for when the value exceeds a certain point. It then inserts a row.
I need to edit this row. I need to merge a portion of it and insert text and ...
-1
votes
1
answer
385
views
Live Time in Excel
I am looking to get the Live time in Excel.. All I want is E.g. If(Now()="08:00","Yes","No")
For this I would need the live time as I see Now is constant and not changing....
0
votes
1
answer
61
views
Tried multiple IFS formula. None is working
So I have a column which has country names plus a few blanks.
So I want the formula to go by this :
IF the country name is "India" put it as India and if the country name is anything else ...
-1
votes
1
answer
673
views
Restarting excel serial number
I wants to add a auto serial number into my excel sheet which should restart from 1 automatically as soon as the text in the next column changed. For example I have column of subjects i.e English, ...
0
votes
0
answers
46
views
Combine then count combination of text in cells in a table
I have an attendance table that I need to verify every day. Every day different employees are billed on different projects, locations, and hours. Each has individual columns while the employees have ...
0
votes
2
answers
338
views
Calculate the sum of individual categories in microsoft excel
I am trying to work on a Accounting finance sheet for budgeting. I am stuck here. I want to calculate the sum of individual categories in "Actual Expenses" column. For example - The sum of &...
0
votes
0
answers
43
views
Specific users cannot open Excel 2010 on Windows server 2010
I have a Windows Server 2012 installation with multiple users. They are mostly identical and serve the same purpose - and are therefore members of the same groups (administrators included).
Whenever ...
0
votes
2
answers
52
views
Multiple if functions that won't generate a too many argument error
The following formula worked until I added the last IF statement. I understand at the core what this errror means due to researching it. However, I cannot figure out what I need to do to keep the ...
0
votes
0
answers
33
views
How to get referenced data from another sheet to update
I am referencing several cells from one sheet to another. In an attempt to compile specific data. What is occurring is that when the data from the 'CL Upcoming' sheet is modified, the data on the '...
0
votes
1
answer
1k
views
Conditional Formatting not updating when referencing new data from another sheet
I have a sheet called 'Email' in which I reference cells from the sheet 'CL Upcoming'. What I am finding is that when I add new rows to the 'CL Upcoming' sheet that the new rows in the 'Email' sheet ...
0
votes
1
answer
276
views
Concatenate sequential column cells into row cells
I need to transpose the columns of data in this format:
Input.
A1
B1
C1
D1
E1
F1
A
W
1
2
3
B
X
1
2
C
Y
1
2
3
4
D
Z
1
2
3
Into this desired result:
Output.
A1
B1
A
W1
A
W2
A
W3
B
X1
B
X2
C
...
0
votes
0
answers
254
views
I want a cell on one sheet to equal a cell on another sheet but only if not blank, If blank i don't want it to do anything at all
I have an excel workbook and the numbers in one cell on a sheet (sheet called 'LCM1225' which is my master data) I want put into another sheet (Sheet called 'Cap Study SC05L').
I can easily do the .. ...
0
votes
1
answer
77
views
get a cell in the closest row of the previous year end
I have a row in mid year which I want to get the tax value of the previous year-end row.
Sometimes it will be year-12-31 and sometime there will not be that date, but a few days before it.
So in B17 ...
0
votes
1
answer
74
views
Conditional formatting formula only partially including selected date ranges
I implemented the formula below to apply formatting to a cell when today's date is 0-30 days from the date value in column G.It is working for the most part except it is not factoring in row 27 (...
0
votes
1
answer
1k
views
Percentile Rank in ExceL based on subgroups
I was needing to calculate percentile rank in a dataset with multiple top values (ensuring percentiles of 100%) This formula was super helpful in getting the right percentile calculation, However, I ...
0
votes
1
answer
80
views
Excel: Count Weighed Cells
I've an excel-spreadsheet, which is my timetable. this means, that I've some tasks, which is almost always a connected cell, sometimes I connect 20 cells, sometimes only two. Example
Since every cell ...
0
votes
2
answers
365
views
How to get the indirect formula to not return an absolute cell reference
I'm trying to use an indirect formula in a conditional format formula and I'd like to control the $ references that are returned by the indirect formula.
=AND(ISNUMBER(MATCH("Not Interested",...
0
votes
0
answers
297
views
Including hyperlink in an If statement returns true causing all cells that return false to link to a 404 not found webpage
I have a fairly convoluted formula but for the problem at hand, it really only concerns the 2nd If statement.
=IF(OR($H24="",I$18=""),"",IF(I$18="Additional Notes&...
0
votes
0
answers
998
views
How to dynamically return the column range (Sheet1!D:D) that contains a specific header
I have a sheet that I'm trying to error proof and rather than hard code a column in my index/ match, I'd like to dynmically reference the location of that column based on the column header. Is there's ...
0
votes
1
answer
49
views
Returning only one column of names from a range where an "x" designation exists on a different sheet
I have a range that has a lot of details for a list of names. I have a column called "include" in that range. I'm trying to create a series of summaries that would be on different sheets and ...
0
votes
1
answer
329
views
Excel: Take on the value of a formula cell when other cell is equal to x?
I know that title isn’t great but essentially:
Let’s say
A1 = some number
And
A2 = some formula involving A1
I want to store values of A2 in a column for different values of A1 so like a function ...
0
votes
1
answer
229
views
Explanation to understand a VBA code for percentage change
I am learning to create formulas in VBA from the internet, and I came across this article by Jon from excel campus where he was teaching to create formulas for the percentage change.
I, however, was ...
0
votes
0
answers
57
views
Extracting Totals and Summary of raw data
I wonder if someone may be able to help.
I have a sheet with a list of events a person (email address) has attended and I'd like to extract/summarize the data in columns either at the end or on a new ...
0
votes
1
answer
1k
views
Group multiple excel rows just like SQL
I have some trading data as below :
I want to group data on some columns and do sum for Quantity and Average for price.
And output the data as SQL style like below.
How can i achieve this in excel. ...
0
votes
1
answer
369
views
Excel VBA - paste every value in a table column into the location listed in another column of same table
I made an example of a simpler version of the spreadsheet I'm working on.
On Sheet2 there's a three-column table. First column is the description, second is the value and third is the location I ...
0
votes
1
answer
322
views
Get VLOOKUP "lookup_value" to refer to a cell reference?
I have this table in the top-left of my sheet:
I want to point VLOOKUP to cell A2 above, so that it returns 2. How can I achieve this? This doesn't work:
// this outputs 1, not 2.
=VLOOKUP(A2,A1:B3,2,...
0
votes
1
answer
78
views
Excel Hyperlink within the same sheet
I have an excel workbook with 2 sheets. Sheet1 and Sheet2. I want to stay on sheet1 and create a hyperlink to sheet2 but I want the information that is available in sheet2 to be displayed in sheet1 ...
0
votes
1
answer
295
views
Merging Two Tables Including INDIRECT Function in Excel and Displaying New Table Data
Dynamic Variables Explained
Data is pulled at the end of each month and is stored in the format of YYYY.MM.DD -- Monthly Data Obtained.xlsx and Excel calculated the date range.
=TEXT(EOMONTH((EOMONTH(...
2
votes
1
answer
980
views
Excel Gantt chart: time line fixed start 1st each month in major steps?
I am using this an excel Gantt chart like this one, to name any: https://www.vertex42.com/Files/download2/themed.php?file=project-task-list-with-gantt-chart.xlsx
No matter what I try, the Gantt chart ...
0
votes
1
answer
790
views
Negative numbers with both parentheses and dash in Excel
I have numbers in a large Excel sheet. The negative numbers are displayed as strings such as (-101.10) corresponding to -101.10. The positive numbers are displayed as usual, such as 101.10.
The ...
0
votes
1
answer
379
views
Excel formula to replace boolean value with specific text
I am currently working on an excel sheet which among other things compares risk assessment based on various conditions.
In cell G3 I have the initial risk assessment, in cell L3 I have the new risk ...
0
votes
2
answers
47
views
Conditional Cell referencing value along a row
I'm trying to make a cell display a date conditional on the value of another cell in that dates column.
This best illustrates.
On row 8 I have a set of values associated with dates on row 6. I would ...
0
votes
0
answers
33
views
EXCEL: Populate a value corresponding to common row on another sheet
There are three sheets:
The master sheet
The 2020 figures
The 2021 figures
The layout of the 2020 and 2021 sheets is like this:
Figure 1 showing codes in column A. In column B is a count of the ...
0
votes
1
answer
32
views
Excel of updating dates within a cell with various formulas
I have a current summary tab that is using this current formula
Currently, I have a report that is dated back to 2 weeks
=COUNTIFS(Industrial!A:A,">9/6/2021",Industrial!A:A,"<9/12/...
0
votes
1
answer
531
views
Excel plotting many data sets each with their own x-axis values on the same graph
I know how to plot each data set so that they all show up on the same chart together but the way I'm doing it is very labour intensive and will take a long time to do this for all sets of data (50+ ...
0
votes
1
answer
56
views
Filtering entries in Excel with multiple connected data on adjacent column
I'm sorry if my title is confusing, I'm fairly new to excel.
Below is a sample image, basically what I want to do is be able to filter the Topics. But when I filter to see -- Climate Change for ...
0
votes
1
answer
375
views
Excel click to reference cell in formula not working
Hoping you can help! I'm having an issue with entering formulas in Excel 2010.
If I want to do a simple =A1 formula, I'm typing '=' in a cell then clicking on cell A1 to add it to the formula. However,...
0
votes
1
answer
123
views
How to find difference of cells with respect to first cell, where first cells are different on two excels?
How to find difference of cells with respect to first cells, where first cells are different on two excels?
Table1
Table2
Table3
Below are the 2 excel sheets (table1 & table2) and I want to ...
0
votes
1
answer
61
views
Basic SUMIFS formula - adding an IF BUY/SELL to swap cash direction
I'm making a template with the intention it provides the net sum of all settling FX values by currency and per a specified account/fund code and settlement date.
The formula below & in picture ...
1
vote
1
answer
260
views
Sum Values in a column based on three different columns
This is more of a complex problem than what is stated in the title. I have attached two images with sample data to explain the problem better. In the first image, I am trying to report cost-roll up ...
0
votes
1
answer
163
views
Excel: How to add a column to current worksheet which is stored in another file
I have 3 excel files each containing only one column. First one has customer name, second one has item name and third one has bill amount. I want to merge these to another file called all_details ...
0
votes
1
answer
571
views
Highlight all the cells if the keyword in the cell matches to any value of a list
For one of my excel sheet, it is required to highlight the cells that has specific keyword in it.
To solve this, i have created a list called my_list which contains value1, value2 & value3.
I have ...