Tagged Questions
0
votes
0answers
36 views
replace images with text in Excel
I have 3 columns in an excel sheet.
ID Product
Name
Image
The Image column has images pasted as objects. I have extracted the images by saving the file as html which gave me an html file and a ...
0
votes
1answer
29 views
Excel: URL with normal Text in a Cell
How can you add an HTML URL to a cell with normal text?
I have a cell with normal text, but want to add a URL, but I don't want the whole cell to be the URL.
i.e.
Please go to this link: ...
-1
votes
0answers
17 views
Trying to match values in a worksheet to a range of values in a 2nd worksheet. both worksheets may contain list/array of values
I have two worksheets, but the there is really only 1 column in each worksheet that is giving me a headache.
Both Worksheet 1 & Worksheet 2 has a column E that each contains only 1 value, the ...
0
votes
1answer
39 views
Is it possible to build a cumulative formula that will sum on new worksheet added daily?
I have a worksheet that the user opens each day and clicks a button that copies the current sheet to a new sheet and renames (Day 1, Day 2, etc.).
I want to keep a cumulative count in some of the ...
-1
votes
0answers
47 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
3answers
74 views
How do you add extra value to a cell that already has an existing number in it
I find this hard to explain, but I'm stocktaking beers, spirits, and liqueurs and I want to keep one column for my ullage count for each product. However, as I go through the days, I often come across ...
2
votes
1answer
45 views
Calculating mean of value across random trials/refreshes in Excel?
Say I have a 1000 cells filled with random values and a particular cell that computes a final answer based on those 1000 values. Now I wish to track the value of the answer over 1000 trials i.e., I ...
0
votes
1answer
33 views
Issue with SUMPRODUCT Function
I am Having a issue with the SUMPRODUCT Function in Excel 2007. Here is my string and issue:
=SUMPRODUCT(($B$2:$B$800>DATE(2013,4,1))*($B$2:$B$800< DATE(2013,4,5)))
A B
1 4/1/2013
2 ...
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
272 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
1answer
65 views
remove values less than a number in excel array
I have a value that needs to calculate stock based on the previous years sales:
{=CEILING(SUM(IF('Inventory Transactions'!$H$2:$H$7137=LEFT($B6,3),IF('Inventory Transactions'!$O$2:$O$7137='Do Not ...
1
vote
2answers
120 views
Passing Range to ABS function in VBA
I would like to perform a function which looks like this
Selection.FormulaArray = "=GEOMEAN(ABS((C646:E646)))"
Since my range keeps varying I would like to pass the range as a variable which looks ...
-1
votes
1answer
59 views
Copy text from “joined cell” to another sheet's smaller joined cell
Normally, text appears in the cell when it contains a reference to another (=Sheet1!A1 will show the contents of sheet A1) However, if both target and destination are joined cells, and the target is ...
0
votes
2answers
69 views
Copy multiple cells from one to multiple worksheets
I have a workbook in Excel 2007 with 52 worksheet each representing a week of the year, each worksheet is laid out the same with 16 rows and 26 columns to give me 14 personnel and 24 hours with other ...
0
votes
2answers
94 views
Modify formula to work with conditional values
I am currently using the below formula to calculate enablement costs for meters
=SUM(SUMIF($D74,{"New Business"}),$U74*5500)
This worked fine as the fee of $5500 was standard, however I now need to ...
0
votes
2answers
106 views
How can I turn a Calculated Column back into a data column in an Excel Table?
I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new ...
0
votes
3answers
102 views
change link cell formula across sheets
I have a workbook named "stu", it has 1 to 30 sheets. In cell H4 I have linked a formula:
=[sub.xlsx]Sheet1!$B$2
in sheet1: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$2
in ...
0
votes
3answers
203 views
show cell value if another cell is true, else go to next row
It's my table in Sheet1:
A B C D
------------------------------------------
1 220 John Good
2 210 Jack Bad
3 300 ...
1
vote
0answers
66 views
Excel 2007 - Chart Not plotting/graphing new/additional series
I have workbook with many monthly worksheets (one year) that "build" on each other. Some data becomes cumulative each month. Since 90% of the worksheet is duplicated, I copy the worksheet to the next ...
2
votes
4answers
127 views
extract date from string in excel
How can I extract date from such strings in excel?
John8/11/12
Jackson09/11/2013
Desired result:
John 8/11/12
Jackson 09/11/2013
Can I do this using only excel formula using excel ...
0
votes
1answer
139 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
196 views
Excel timesheet. Public holiday pay conditional on precise conditions being met
I'm trying to make an Excel timesheet that does some quite particular functions.
1) If we say Wednesday is a public holiday, if the employee worked on Tuesday AND Thursday (days on either side of the ...
1
vote
7answers
81 views
Rounding in Excel
I have a list of numbers in column A that go to several decimal places (e.g. A1 is 27.34). Is there a way to fill column B with the same values as column A, but rounding to the nearest integer?
4
votes
1answer
191 views
Issue in Excel: Copy a cell with formula - pastes value of the copied cell
I have the following copy and paste issue since yesterday. Probably it started when I was trying to edit a locked Excel-doc which i found on a shared server (that is - it was not created with my ...
2
votes
2answers
218 views
Excel method to find the values closest to a given period
I have some data measured at non uniform time intervals, for example:
Time(s) Value(KB)
2.1 304
43.0 400
60.2 380
87.0 440
89.5 445
102.2 460
...
0
votes
1answer
259 views
How to search a string in many workbooks and copy row if true
I need to run a report for a customer.
I have approximately 50 files (Excel 2007 workbooks) in a folder. Each workbook has about a hundred rows and ten colums. I need to search a string (in a known ...
0
votes
3answers
106 views
Finding multiple coded values in a string, then returning a lookup value for each found code
I have 600 item codes, and each one is coded with attributes in the overall item code. For example, we might have the plain item 600, then 600BK (Black body) and 600BKR(Black Body, Red colored detail) ...
1
vote
1answer
254 views
Is there a way to delete everything except what is selected in Excel?
I have an Excel template with 20 tabs (worksheets) and plenty of data in each sheet. When a user opens a copy of the template, he will only need to use one tab.
Is there a sneaky way to select that ...
0
votes
2answers
85 views
Filter Duplicates by count of a character Excel 2007
I have a data list of around 32000 items in column A. Each of these items has many duplicates, some having 1 duplicate, up to about 5 or so max. Each cell has multiple sku's, divided by a /.
What I ...
0
votes
2answers
111 views
Conditional formatting with date and time
I have a problem on conditional formatting with date and time.
I have a cell A1 that has date and time and I want to conditionally format its adjacent cell if the value of cell A1 is greater than 3 ...
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 ...
2
votes
3answers
266 views
Compare 2 sets of data in Excel and returning a value when multiple columns match
I have a data set for employees that contains name and 3 attributes (job function, job grade and location). I then have a data set for open positions that contains the requisition number and 3 ...
1
vote
3answers
142 views
How to create a conditional format in excel 2007 from another cell
I have a sheet with 4 fields spanning A3:D3
in D3 I have a conditional format that turns the cell yellow if it equals the word "no contact"
How can I get Cells A3:C3 to also turn yellow if this is ...
0
votes
1answer
115 views
Excel convert MMM DD, YYYY to string
I am trying to convert a date string into a date format the strings look like this
Nov 28, 2005 12:00 AM
and i would like them in the format DD/MM/YYYY i have been trying to find a solution online ...
0
votes
2answers
102 views
If functions with three conditions
I have to derive a formula for the following conditions:
If company is listed in ROC then the result should be Applicable (for XBRL)
If company is unlisted then check the following conditions:
If ...
0
votes
1answer
1k views
Copy/move values matching a pattern to another column
I have a worksheet where we need to move all the values matching a pattern to different columns.
For example, we have a B column with values that are one or more letters followed by one or more ...
1
vote
1answer
577 views
Excel 2007 - Conditional Formatting: Compare 3 columns with text - find unique values
I have a spreadsheet with three columns, each column listing the folder names found on a server.
Server1 | Server2 | Server3
--------|---------|---------
A | | A
...
3
votes
3answers
711 views
How to identify cells in Column A that contain any of the values in Column B
First off, I'm not that advanced of an Excel user. I'm certain Excel can do what I'm about to ask, but I've yet to find a way in any of my attempts or searches.
My question is very similar to Find if ...
-1
votes
4answers
174 views
Multiple “IF” formulas
I am trying to write an IF formula for an order form where a discount is applied depending on the amount of units ordered.
The discounts are:
0 to 24 units - 0 discount
24 to 59 units – 5% discount
...
1
vote
2answers
350 views
Create table, by conditionally copying rows from another table - possible in MS-Excel?
Every week, a new Excel file is generated by a CRM system, which has the date-stamp in it's file-name, but placed in a folder at a fixed location (a sharepoint URL).
This file is quite large, and ...
1
vote
3answers
2k views
Find, match whole word only workaround
I just noticed, there is no option in Excel Find, to "match whole word only" (I am not talking about "match entire cell content") e.g. searching for "part" should not match "partner". MS Word has this ...
0
votes
1answer
120 views
In Excel input filter values in cell
Using Excel I am searching for records using Filters>Down Arrow>Text Filters>Contains
(refer screenshot), this is very tedious as the seach window disappears after taking the imput and I have to ...
0
votes
2answers
150 views
Excel - How do I highlight a cell if its address is stored in a column?
I have a worksheet with a list of 'Special Cells' in a column, like 'C352', 'E11', etc.
How do I write a conditional formatting rule that looks through the column on the other worksheet, and if that ...
1
vote
3answers
269 views
How can I get the Cell Reference of a Named Range in Excel?
I solved the issue, but I'm wondering if there's a better way to do this. What I have is a column of some named ranges ("NAME"), and I want a formula to the right that looks up the named range and ...
0
votes
0answers
40 views
Comparing two strings in Excel [duplicate]
Possible Duplicate:
if Excel row contains particular words, return that row or highlight that row or give Y/N for that row
I have two Columns with Company Names and I want to find out which ...
1
vote
2answers
70 views
How to name column headers (column B to X) based on values in column A?
Consider the following excel worksheet:
| ... | H | I | J
--+-----+--------+------+-----
1 | ... | obsDay | obs3 | obs4 ...etc
--+-----+--------+------+-----
2 | ... | 3 | |
...
1
vote
1answer
5k views
Excel, how to find column number of min value?
I have a table in Excel.
Can anyone help me find the column number of the cell where the min value of the row is?
Example: 200 is the min value in row1 and has the column number 1.
Is there a ...
4
votes
2answers
393 views
Counting “0” Stock Values Generated by Pivot Table
I have an inventory ledger. It looks like this:
Tran Type Txn Date Item Quantity
Opening Balance 6/30/12 Item1 4
Opening Balance 6/30/12 Item2 7
Shipping ...
1
vote
1answer
71 views
Excel 2007 - Adding line breaks in a cell and no line over 50 characters
I have notes stored in an Excel cell. I add line breaks and dates every time I add a new note.
I need to copy this to another program, but it has a line limit of 50 characters. I want a line break ...
0
votes
1answer
984 views
Auto create folders using Excel field entries
I need to generate folders containing certain Excel field entries:
Additionally, I need to append a date to the folder name based on that entry
So If I have the above Excel table, I'd need the ...
