All Questions
35
questions
0
votes
1
answer
7k
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 ...
- 5
1
vote
2
answers
2k
views
How to insert a line break in a concatenated cell in Excel 2003
In Microsoft Excel 2003, I use the following formula [in cell A2 in a sheet named (Allocation)] to refer to several cells from a sheet named (02Mis)
='02Mis'!$A$4&"/"&'02Mis'!$A$11&"/"&...
- 249
1
vote
1
answer
672
views
How do I add 1 month to a specific date once that date has passed?
I have an excel sheet that lists our current due date. The due dates are the same day of each month (9/2, 10/2, etc) and I want the due date to update once the old date passes so I don't have to do ...
- 23
0
votes
3
answers
124
views
Joining 3 Excel IF Statements
I'm trying to join the following 3 IF statements in Excel but every time i try i get an argument error or else it reverts back to displaying "True" or "False" instead of "Miss Window", "Hit", "Not ...
- 55
0
votes
1
answer
38
views
In this data set, I want to get the most recent or three most recent numbers for a particular day. How can I get this?
This is the data set I have :
Date Day Number
8/29/2016 Mon 63
8/30/2016 Tue 58
8/31/2016 Wed 66
9/1/2016 Thu 61
9/2/2016 Fri 61
9/3/2016 Sat 67
9/4/2016 Sun 70
9/5/2016 ...
0
votes
2
answers
1k
views
How to put comma into digits like eastern (especially Indian) style by using a formula in M S Excel?
Friends I want to put comma automatically into the digits like this-
10,00,000
Or
1,00,000
For this I want to use a formula. I also know that I can do this by format cell>number>use 1000 separator (,...
- 181
0
votes
1
answer
720
views
How to fill up gaps in excel
My data in excel has for example the following data in columns A and B:
A B
1 4
2 6
5 88
9 67
12 45
and so on.
I would like to transform it as following:
A B
1 4
2 6
3 0
4 ...
- 103
1
vote
1
answer
616
views
Checking column for value. If true, check same row in another column and count if not blank
I have a spreadsheet of employees. Column A is a list of Dept types (sales, HR, payroll, etc). Column B is the date their appraisal is completed, or blank if incomplete.
I would like a function that ...
- 11
2
votes
2
answers
212
views
How to extract remainder of a match of 5 digits in 9
I am trying to extract the "remainder" (the non-matching portion) of a search using Excel 2003. I tried using various combinations of functions but it seems that there's no specific function that can ...
5
votes
3
answers
38k
views
How to find the last value in a column in excel?
I am tracking rooms booked in a hotel on a daily basis in Excel. Every day I input the number of rooms booked in a column to show a trend of pick up over a period. I'd like to return the value of ...
- 51
0
votes
1
answer
624
views
Search from bottom with multiple criteria
I am trying to search a data table in excel and find the bottom most row that meets a specific criteria. I thought I could use this LOOKUP trick but it only returns a #DIV/0 error. This is what I ...
- 269
1
vote
1
answer
4k
views
Microsoft Excel lookup match without sorting data
Have a value list and need to insert points by looking up the data table. Data table has value for points in 1000 increments. Actual value that I lookup will not be rounded to 1000 so I tried using ...
- 326
0
votes
3
answers
235
views
Excel Using Reference for Worksheet names [duplicate]
I've looked around to try and resolve this, however I am unfamiliar with the coding.
Here's my line of code,
='[WorkBook]BOM'!G6
What I want to do is make the Workbook call to a cell so I can mass ...
- 1
2
votes
1
answer
550
views
how to determine which values in a given set of cells will add up to a numerical range
In other words, I'm looking for a sum range that consists of combinations of cells from an array. Given a set of numbers:
1 720 56 17 59 120 153 203 39 1 690 583 582 561 256 310 232 95 108 16
26 ...
- 21
1
vote
1
answer
930
views
How do I create a list of phone numbers for people whose reference numbers appear in two columns?
I have two sets of data in columns A & B and also in columns C & D. Some of the content in column C & D does not appear in columns A & B:
A = reference numbers
00001, 00002, 00003, ...
- 13
-2
votes
2
answers
991
views
Nested IF formula
I have:
in *Column F* in *Column S*
F2 Monday S2 Orange
F3 Tuesday S3 Mango
F4 Wednesday S4 Grape
F5 Thursday S5 Pawpaw
F6 Friday ...
- 51
0
votes
1
answer
3k
views
Excel Time Subtraction
I think there is a way around the time subtraction issue I have in Excel. The problem is that when the time in cell F (for example 11:29am) is less than the one in cell A (for example 11:30am), the ...
- 51
5
votes
2
answers
27k
views
Restrict and validate date format in Excel
I'm trying to put together a 'Data Validation' formula which restricts the user to entering a date in the following format: dd-mm-yy.
From the research I've done, I've come across what I thought ...
- 195
0
votes
2
answers
1k
views
How to clear all value cells in an entire 2003 Excel workbook by one go?
How can I clear all value cells in an entire 2003 Excel workbook by one go?
I could do one sheet by going to special functions, however, if a file is composed of many sheets, it would take a lot ...
- 145
0
votes
2
answers
835
views
Excel 2010 formula conversion to Excel 2003
I am by no means overly proficient with Excel, I know enough to do the basics.
After much forum and soul searching I had come up with the following formula, which I will discuss what it is supposed ...
- 1
0
votes
2
answers
7k
views
Compute the first digit of an integer in Excel
I have a spreadsheet with column B having integers. I want to get the first digit of each entry in column B in the corresponding row of column C.
2
votes
3
answers
210
views
Convert Excel data
I've made a survey in Sharepoint and want to convert the results into something useful in Excel 2003. My problem is the format the responses are in for some questions.
An example of two responses (=...
- 155
0
votes
2
answers
2k
views
Remove duplicate rows in excel when not all the columns are the same
How can I automatically remove duplicate rows in Excel when data->filter doesn't work, because the columns aren't all the same?
I believe the picture explains what I need, but I can probably supply a ...
- 1,018
2
votes
1
answer
171
views
In Excel, how do I highlight all rows that do not contain “,2”?
I'm verifying the FormatCurrency function is written properly in ASP code and I put everything in Excel from "find results".
I want to highlight only the ones that do not contain ",2" in them.
Don'...
- 131
0
votes
2
answers
243
views
Excel Multiple Conditions Summing
I have Column A to A1600. In that column I have two project names "CP" and "GFI", in column B I have Project Phase 1-8, in column C I have the hours worked in each phase. The projects intertwine so ...
- 1
1
vote
1
answer
557
views
How to calculate the total sum of hours with automatic filter
I have an Excel sheet where I sum hours to get the total amount of my work.
It works without problems, but if I add an automatic filter I'd like the calculations to be done only on the portion of ...
- 1,866
3
votes
1
answer
5k
views
Random Excel bug when referencing a different sheet
I am getting a very strange error anytime I try to use a formula pointing to a cell in a different sheet from the active one (even as simple as "=Sheet2!A1"). This started happening all of a sudden, ...
Matteo
2
votes
5
answers
2k
views
Excel not properly recalculating values
I have an Excel sheet with values in it (this sheet is generated by a custom perl script, but I don't think that's where the problem lies). In it, I have a formula:
=sum(indirect(concatenate(address(...
- 373
1
vote
1
answer
20k
views
Excel: Calculating working hours between two dates
Summary:
I know that the function NETWORKDAYS can be used to find the number of business days (=excluding weekends and holidays) between two dates. But what if I want to know the number of business ...
- 18.4k
27
votes
1
answer
124k
views
How do I put logical operators in an Excel =IF Formula?
I'm trying to enter a formula to display text according to an IF condition. The best I can manage is something like...
=IF(myval>=minval & myval <= maxval, "OK", "Not OK")
But this appears ...
- 1,955
1
vote
2
answers
180
views
I hyperlinked a cell in excel 2003, formula issues?
I have a budget spreadsheet using excel 2003. I have My deposit, then all of my bills, the total, then a cell that has the difference(between the amount of deposit and the total of the bills). The ...
1
vote
3
answers
1k
views
Excel select column
In excel, if I want to perform a function with the column to the left, how do I go about this?
- 113
5
votes
4
answers
15k
views
Using two conditions with a SUMIF function in Excel 2003
For a given row on Sheet1, I use the SUMIF function to see if the value from the first cell of that row is on a list of historical values in Sheet2.
That will basically summarize all the values on my ...
- 705
4
votes
4
answers
20k
views
Excel formula to display a string of text and numbers
I need one column to show the following text: prxyz,(data1 here),0699,ABCD(data2 here),xxx
data1 is in column A and data2 is in column B.
the format of data1 needs to be 5 numbers (sometimes ...
excel gamine
1
vote
3
answers
3k
views
Sum Column, but only Filtered Rows
I have a large flat file of a bunch of ungrouped primary key values.
I want to filter on a key and populate cell A1 with the value selected from the dropdown list.
In the end, I want to see a sum of ...
- 310