Tagged Questions
1
vote
1answer
38 views
Refering certain rows in other sheets based on a particular condition
I require help for making the following table: Consider that I have three sheets in an Excel workbook.
The first sheet is the summary page. The next two are Subject1 and Subject2.
Each of these ...
1
vote
1answer
33 views
How to count based upon multiple conditions
I am trying to create the following formula
=SUM(IF(AND(K3:K138="Tekken",L3:L138="American Football"),1))
However, it always seems to result in zero, even where a single row would have both values ...
0
votes
2answers
22 views
Need simple macro for “match” formula
Are column B cells hidden somewhere anywhere in column C? Give results in column A. (true/false or yes/no)
I'm unable to post the simple Excel image I've created as I'm a guest, but it shows simply ...
0
votes
3answers
28 views
Nested IF statement
I'm having a problem with this code for nested =IF statement. The range is basically:
<=16 to get 5 pts
16-17 to get 4 pts
17-18 to get 3 pts
18-19 to get 2 pts
above 19 to get 1 pt
and my code ...
0
votes
1answer
40 views
Compare two date columns and highlight the newest
I have two date columns in the same sheet. Column B receives its data from a vlookup function.
COLUMN A | COLUMN B
2013-07-05 | 2012-01-01
What I want to do, is to compare the two date columns ...
-1
votes
0answers
46 views
Format multiple sheets with irregular headers in Excel
I am trying to format a price list from a supplier for import into my own database. The workbook the supplier generates contains multiple worksheets each with the same columns of data that I want to ...
0
votes
0answers
53 views
Need to find four common data columns in different locations in multiple workbooks
I am working from reports generated from different projects. Each report has varying columns but all have the four columns that I need. I would like to be able to boil the sheets down to these four ...
1
vote
1answer
20 views
How to calculate values between three cells using conditional calculation
I am new to this forum and not very new to Excel (not in depth)
I have to perform a calculation for three cells by conditional calculation. so here is the scenario
Column Name: Total Area (Cell ...
2
votes
1answer
28 views
Capitalize first word in each row of a column in mac
I want to capitalize the first letter of every word in each row of a column.
So I create a new column, type in =PROPER(B2), then drag that down the entire length of that column. It works great, it ...
4
votes
3answers
64 views
Check if Excel cell text is numeric using formula only
I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.
What I've found is that the ISNUMBER() function ...
0
votes
1answer
55 views
vlookup and indirect and external shared workbooks
i have two excel workbooks that are both shared in our company folder.
excel1 is where each employee has his own page to fill information and column 1 of each page is the row reference
excel2 is a ...
1
vote
2answers
45 views
Can I use Microsoft Excel to convert a “ranged” table to a full breakdown table?
Suppose I have a spreadsheet with the following rows and columns:
QTY MIN | QTY MAX | 1 Color | 2 Color | 3 Color | 4 Color
1 | 10 | $1.00 | $1.50 | $1.75 | $1.90
11 | 20 ...
2
votes
2answers
250 views
How to implement MATCH and INDEX functions together in Excel VBA?
I am trying to find the row number of the first cell that is less than zero.
So, by browsing through the net I was able to come up with this formula which works perfectly fine:
...
0
votes
0answers
47 views
Change Column Values Based on an Input
Column A contains Products created from a drop down list. I want Column B to equal product benefit statement for each product, Column C to equal full price of the Product and Column D to equal ...
0
votes
1answer
116 views
How to copy the value and format of a cell into another cell on Excel 2010? [duplicate]
I have been looking all over the internet for this question, but it has not been answered by anyone.
I am, on Excel 2010, trying to copy (not copy and paste) the format AND value of a cell, onto ...
0
votes
2answers
68 views
How to count number of rows excluding rows with duplicate data
I have a table in which the column "Doc. No." has duplicate info as below; please advise on which formula I should use to count the number of unique Doc. that I have (as reference to the below table ...
2
votes
1answer
77 views
Find last row in sorted table for matched value
I have a table in Excel, which for simplicity sake we will define as:
Order# | Date | Price |
123 | 1-1-2013 | 100 |
135 | 1-2-2013 | 150 |
145 | 2-4-2013 | 100 |
145 | 2-5-2013 ...
0
votes
1answer
44 views
VLOOKUP in Excel with IF statements?
I'm not sure whether IF statements are actually needed in this problem;
however, I'm totally stumped.
In the first worksheet I have a column for "years employed" (Column D6:D107) ranging from 0.1 to ...
0
votes
1answer
111 views
How can I get the hex value of an 8 row column with depending on background color?
I have a spreadsheet that I'm using to help visualize a character bit map for each ASCII character. For a 5x7 font, I use 5 cells wide (columns) and 7 cells tall (rows). I then marked the background ...
1
vote
2answers
82 views
How is OFFSET supposed to work on filtered EXCEL data?
Consider:
+---------------+
| Column A |
+---+---------------+
| 1 | Milan |
+---+---------------+
| 2 | Paris |
+---+---------------+
| 3 | London |
...
0
votes
1answer
55 views
Ms Excel Formulas for Date and Day
I need help.
I have written a specific date, for eg 13/03/2013 in A1.
Now I want its corresponding weekday in A2 (i.e. 13/03/2013 (Wednesday).
In A3 I want it to show two days ahead (i.e. 15/03/2013).
...
0
votes
1answer
102 views
Get unique comma separated values from list of comma seperated values
I have a list of csv in a single cell, and I need to get the unique values from that list and put them in another cell. For example, in one cell I would have:
DIV-154, FOD-371, UDL-913, DIV-154
And ...
0
votes
2answers
118 views
Sumproduct fails when range is only 1 row and part of the calculation is blank
I have the following formula that works when the ranges have more than 1 row, but generates a #VALUE error if there is only 1 row and one of the factors is blank.
=SUMPRODUCT(cost, units)
Cost ...
0
votes
1answer
67 views
Unable to get the VLookup property of the WorksheetFunction class
I want to implement a barcode finder in excel, so I have item names and barcodes in separate sheets depending on what group an item belongs to. The user clicks on a radio button (selecting Group 1, ...
0
votes
1answer
48 views
Can cell addresses (vs. cell contents) be used as operands in Excel formulae?
Consider the following scenario illustrating the data I have vs. the data I want. Very simply, I have a single column that represents Field 1, Contents of Field 1,...etc. and is more appropriately ...
1
vote
1answer
70 views
Guidelines for developing an Excel grading sheet
I'm trying to develop a convenient solution for preparation of a summary sheet comprising of lots of grading sheet underneath it.
I have a tabular excel sheet used for obtaining feedback from random ...
0
votes
1answer
81 views
Easy MS Excel - MIN column with IF condition checking another column
I'm analyzing some personal expenditures for somebody on Excel.
I have a column for calendar dates and a column for expenditures on that date.
Of course, there are days when there are no expenditures ...
0
votes
1answer
137 views
Compare two excel sheets with almost identical data and come out with matched and mismatched data
There are two excel sheets with almost similar data. One sheet is huge with around 1800 rows. and the second one is a small with around 100 rows. And these 100 rows may be present or not , if present ...
1
vote
2answers
104 views
Excel formula to get time difference as a decimal number
I have a table like this (the C column is blank):
A B C
1 19:30 23:00 (3.50)
2 14:15 18:30 (4.25)
I need to calculate the time difference in each ...
1
vote
0answers
90 views
Excel: Why is VLOOKUP returning inconsistent and unexpected results?
I'm automating an Excel column to assign account values based on the vendor paid. For instance, if AT&T gets paid, the account value is Utilities:Telephone, and when the post office is paid, the ...
0
votes
2answers
97 views
If / Else logic involving text i Excel
I want to perform the following logic in a Microsoft Excel 2010 worksheet:
In cell E3: if C3 = Off or off then VALUE=0, else Value=C3*D3
Where cell C3 will contain either a number or the word 'off' ...
0
votes
0answers
31 views
Excel function is not returning expected results
I have a list of documents, and I'm trying to flag those that have the word see in the title. The formula I'm using is =--(ISNUMBER(SEARCH("see",F43))). but all my results return as 0.
ACE [see C3] ...
1
vote
2answers
1k views
Is there an Excel function to create a hash value?
I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...
0
votes
1answer
82 views
Extracting delimited substring in Excel
I started with this question, but it doesn't quite address my needs. I have a series of filenames in a worksheet. Some of the filenames have cross-references enclosed in square brackets. Consider Foul ...
1
vote
2answers
157 views
Display multiple cells if data on another workbook contains “X”value
I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 ...
0
votes
1answer
103 views
Excel, Average dates between transactions?
I have an inventory data set that I am playing with and I have hit a wall in trying to figure out something that I want to do.
The data set has a name column (name of item; e.g., “hammer” or “saw”), ...
0
votes
3answers
108 views
Excel - Sum First Value Of Non-Unique Adjacent Column
I have a table:
ColA | ColB
1 1
2 1
2 1
3 1
4 1
5 1
I would like to sum ColB only once where ColA = 2.
Essentially, I would like to sum the values ...
2
votes
1answer
72 views
Is there any way to programmatically stylize part of a string in a calculated cell in Excel 2010?
Let me explain what I mean.
I have a cell that contains something like this:
="String of text that's moderately long" & YEAR(NOW()) & "."
And I want to know if there's any way that, via ...
2
votes
1answer
74 views
Merging Excel files on a keyfield
Using the answer to this question as my starting point, I have a slightly different scenario that I'm stuck on.
Consider:
File 1: Incident# (key), foo1, foo2, foo3
File 2: Incident# (key), status, ...
0
votes
2answers
335 views
Count how many times a text appears in Excel 2010
How do I count how many times each text appears in a column?
For example I have this column:
foo1
foo1
foo1
too2
foo1
too2
mmm
mmm
foo1
Now I like to excute a function that shows me this result:
...
0
votes
1answer
121 views
Need to VLOOKUP in worksheet in the same workbook based on cell value
I am a building a workbook (spreadsheet) that has worksheets labeled by month; e.g., "Jan-12", "Dec-12", and so on. I need VLOOKUP to be able to change where it looks based on the name of any given ...
0
votes
3answers
84 views
Calculating a sum on an integer column when another column includes the year criteria for the calculation
I have a spreadsheet where the columns contain values as follows:
Column(A) B C D E
NameA 01/02/2010 5 4 5
NameB 05/06/2011 4 3 ...
1
vote
2answers
70 views
How to pass a formula as one of the arguments to CONCATENATE?
I'm using Microsoft Excel 2010 and I am trying to create a title which will be updated every time the spreadsheet is opened. I want it to say "Forecast from (today's date) until end of 2013".
So far ...
0
votes
1answer
113 views
excel 2010 open dialog window after changing formula
I have to copy 120 thousand formulas from one xls file to another. After a simple copy-paste, the original file name is inserted into the formula, not good. I tried to remove it with Data - Links, but ...
0
votes
1answer
107 views
Formatting Time Entries in Excel 2010
I have an Excel Spreadsheet. Essentially, I'm trying to use this spreadsheet for time tracking purposes. I have the following columns:
Date (Formatted as a Date in Column A)
Start Time (Formatted as ...
1
vote
2answers
205 views
Comparing Two Columns in Excel, Returning a Third
I've hit a bit of a brick wall in my Excel-ing. Below is the example and explanation.
Sheet 1:
Column A | Column B | Column C | Column D
_ Name _ | _ Date ._ | _ Cost .._ | _ Code _
...
1
vote
2answers
104 views
Does AND work in Excel spreadsheet formulae using Ctrl-Shift-Enter?
I have an Excel (2010) cell with this formula:
{=SUM(IF('Tasks'!$I:$I=$C4,'Tasks'!$F$2:$F$2000,0))}
which works (the correct total is given). But when I enter another item to search for:
...
0
votes
1answer
73 views
How do you find a difference among cells in a single column?
I have a list of ~100 values in a column. I want to find all possible differences among cells of a value of 30 +/- 2. For example, 1200, 1201, 1205, 1208, 1230. The difference between values 1 and 5 ...
0
votes
1answer
117 views
excel function searching multiple rows
I have a worksheet with a column (A) with dates. Some rows between dates are blank. I want to put a cell on another sheet that searches sheet1, column A for a particular date, and when that date is ...
1
vote
1answer
48 views
Formula for Formatting Date in Excel
I need to make a formula that will reformat a date in excel. My content is as below:
Cell E4: 2/15/2012
I would like column E5 to be like this:
Cell E5: 15 February 2012
Is there an easy way ...
