Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (3)

2
votes
0answers
164 views

Splitting an excel 2010 spreadsheet into multiple workbook files based on column value

I find and clean a lot of contact data, sorted by country, which I am currently using excel to manage. Mainly because it is easier to clean and manipulate the data I find this way, and I can easily ...
2
votes
0answers
248 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 ...
1
vote
0answers
48 views

Using getpivot in excel to create flexible reports based on user inputs

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but ...
1
vote
0answers
58 views

automatically transfer any data on excel spreadsheet to master spreadsheet

I work in a library and I am working on a workbook to track our orders from one company. I have three sheets where each sheet represents a different branch of our library. I am trying to have it set ...
1
vote
0answers
38 views

Microsoft Excel hyperlink function to a specific location in a Word doc

I have a long column of numbers in Excel that I have mail merged to a Word doc. I would like to write a function in Excel that will hyperlink each cell from this column to its corresponding mail ...
1
vote
0answers
30 views

How can I move columns based on the column header title to another workbook?

I have to average grades based on each objective for a new report card we have to complete this year. Column one has students names, each additional column will have the objective associated with the ...
1
vote
0answers
51 views

How to create a formula which finds a cell that matches a range of other cells then produces a number if the cell appears.

am having trouble with finding a suitable formula for my spreadsheet in excel. In column A i have a list of postcodes (439 postcodes). In column B-BL i also have postcodes (these postcodes are in ...
1
vote
0answers
59 views

Conditional Formatting a date/time comparison

The function I'm working on specifically compares & colors a daily recurring time range set in 2 different cells. A specific date in the target cells (produced by a formula) compare to that daily ...
1
vote
0answers
753 views

Openoffice Calc - use cell font color in formula

I have some openoffice calc sheets where some rows are maked with the font color red. All other rows are in the default font color. Can I use the font color of a cell within a formula to react to ...
1
vote
0answers
120 views

How might I evenly assign arbitrary categories smoothly across a large population?

Does Excel have a feature or set of features that can be used to accomplish the opposite of sorting? So, rather than sorting rows so that similar values are contiguous, I want similar values optimally ...
1
vote
0answers
857 views

Tab (\t) is not recognized as columns delimiter when pasting into Excel 2013

I'm pasting data separated by tab, usually the excel would be a "little" cleaver and separate the data by columns at each tab character. I don't know why, but Excel doesn't recognize the tabs anymore ...
1
vote
0answers
179 views

What is the best way to create charts of “database-style” tables and data in Excel?

I'm a biologist and I frequently have to do analysis on relatively large (for Excel) datasets. Usually a few charts are needed to display the data, but creating them seems to require laying out out ...
0
votes
0answers
2 views

Creating a more complex conditional format in Microsoft Excel spreadsheet

I am familiar with Excel conditional formatting but I need to construct a more complex query and am not having much success at the moment. I have simplified what I am trying to achieve for the ...
0
votes
0answers
8 views

=VLOOKUP() function performed on dollar value displays value without “$”

I'm not entirely sure how to present pleasant-looking spreadsheet representations on SE, so I would appreciate any advice on that issue given on the side. I want the B2 cell to display the salary ...
0
votes
0answers
7 views

Access Database Forms - Query placement and functionality

The question actually has two parts, the first is that I currently have a working access database and query for the database. I'm trying to make a search page for the database, but I would like the ...
0
votes
0answers
27 views

Excel: Finding comma delimited values in reference range

I have a two-sheet excel file in which I want to see if specific values are present in some column, based on comma separated values in some other cell. The idealized case would be: Sheet1: Cell A1 ...
0
votes
0answers
25 views

“Filter by Selected Cell's Color” is so slow

I have a row includes like 60-70 thousand lines. 30.000 of them colored. And I'm trying to filter them by "Filter by Selected Cell's Color" but it's so slow. It take forever to filter just 70.000 ...
0
votes
0answers
29 views

Excel changes formula to numbers randomly

I have had recently a number of instances where Excel has changed formula to the numerical results of the formula : Formula Formula Formula Formula Number Formula Formula Formula I have ...
0
votes
0answers
44 views

Excel - find and remove text, surrounded by varying characters

I've got an Excel file with ~5,500 rows of inventory items. One cell in each row is a description field and contains HTML. Some (but not all) of these cells contain a link formatted something like ...
0
votes
0answers
9 views

Pointing to another excel worksheet in a 3d table dynamicaly

I have a 3d table for some stocks of products, all sheets identical and all. If product name is "OIM", the worksheet the data is on is called "ROIM" and so on. Now, I would like to find a dynamic ...
0
votes
0answers
24 views

incorrect IF formula evaluation

I have the following formula: =IF(SELL_INVESTMENT="YES",1,2) It returns not 1 or 2 but 0. I verified that the cell contains YES and it should return 1. The file contains over 1000 cells. "Trace ...
0
votes
0answers
43 views

Getting data from closed files with concatenate formula

Challenge is this: Each day program is creating an excel file for me with some data for the current day. Like what is the price for products, how many people are available today and things like this. ...
0
votes
0answers
9 views

Generating ID#'s in GIS attribute table

In the attribute table within GIS, I have a certain column with ascending values, and I want to assign ID numbers (for easier readability and display) to a corresponding column. So for instance: Feet ...
0
votes
0answers
40 views

Using Excel to calculate time differences with blank cells and calculate monthly totals

I have a sheet that looks like this: Level 1 Level 2 OFF Date Time Date Time Date Time Total T Total L2 12/3/2013 12:50 ...
0
votes
0answers
56 views

Finding IPs covered by Subnets in an Excel document

So I have a giant excel document that contains a list of IPs, but there are also entrys that have subnets. So for example, I might see "IP" "/16" and the first two octets would be the first two ...
0
votes
0answers
44 views

Turning students’ marks into grades with excel

I have a sheet contain grade boundaries, For example, the first row says ‘A* | 100 | 90’, the next row is ‘A | 90 | 80 ’, the next ‘B | 80 | 70’ and so on. Another sheet contains a list of student in ...
0
votes
0answers
35 views

Displaying some cells data depending on selected text from other sheet

I have an excel sheet which looks similar to below and with some more columns. Area Branch Empolyees Products Shelfs Area1 Area1 Branch1 10 Product 1 10 Area1 Area1 ...
0
votes
0answers
23 views

Mail Merge | Excel transpose or any other solution

i have an excel with 3 columns: domains, clients, email clients each client can have more than one domain. I want to mail merge all my clients to make a change in the dns. but i don't want to send 5 ...
0
votes
0answers
39 views

Excel Forumla for SUMPRODUCT to exclude rows/cols

I am rather new to using Excel. How can I take a formula like this =SUMPRODUCT(B3:B39, A3:A39) and have it exclude specific rows that I am using as headers/dividers. But seem to break the formula ...
0
votes
0answers
62 views

Is there something better than VLOOKUP?

I'm wondering if there is something like a VLOOKUP for multiple matches in one column. So, for example, if I have the following: Alfred red Jone orange Christina blue Danielle blue ...
0
votes
0answers
45 views

Merge CSV files with Excel

Every day, I run a report that generates a .csv file of items that need to be purchased for the company that I work for. The fields look something like this: ...
0
votes
0answers
45 views

Excel 2010 - IF Function to transpose data from other sheets

I have Sheet 1 with a table with Columns A - T (first row being headings) containing aphabetical and numerical values. Column Q has a "Yes" or "No" value . The Rows contain the customer data for each ...
0
votes
0answers
40 views

Excel: IF function avoids circular reference by checking for a formula

In Excel, I'm trying to write an IF function which will avoid a circular reference. If cell X has a formula referencing this cell Y, display a value. Else if cell X has a value, divide that value by X ...
0
votes
0answers
43 views

Google docs SpreadSheet get reuslt from a formula inside a formula

I was trying to obtain the result of a formula that is inside of another formula. | A | B | C | D | E | | Option1 | D2-(D2*10%)| | 10 | Result | | Option2 | ...
0
votes
0answers
43 views

standard deviation on excel

so I have 3 data sets, which all came from the first data set using the excel program. all consisted of random numbers from =randbetween(0,100) starting from A2:A102 which is 100 integers. for data ...
0
votes
0answers
56 views

Indirect lookup across spreadsheets

I have a spreadsheet that has information about all products for every week of the year. Currently I have a second spreadsheet that is setup to look at the first one for each field (currently has 52 ...
0
votes
0answers
25 views

Can I put the value of one cell next to the cells of the same rown?

Okay, so I've got a list of competition entrants with their details all set out in a row. This includes the number of invitation emails that they have sent out. Now I want to put the ENTRANT'S name ...
0
votes
0answers
51 views

Comparing two lists of strings based on logical operatiors

I'm trying to find and merge several groups of people based on the following boolean algebra: Let '*'=AND and '+'=OR group1=A*NOT(B)+C*NOT(D) group2=NOT(A)*B*NOT(C)*D ...
0
votes
0answers
48 views

Hold cell spacing when copy/paste

I need to copy only certain cells, example. A1, D1, G1, J1, M1 To: A6, D6, G6, J6, M6 or H6, K6, N6, etc (Notice the equal spacing between the cells to copy and where to paste.) But when i paste ...
0
votes
0answers
19 views

Cell reference is not updating in manual calculation mode

I have defined a variable in Date-format (Date_selected), shared in the entire workbook. On a different worksheet a cell has a direct reference to this (=Date_selected). Also formatted as a Date. When ...
0
votes
0answers
25 views

How to use the Integer i.e. currentregion's row from the macro in a cell's formula

How do I use the row from the current cell, in another Cell's Formula on another sheet? I created this Macro, but don't know how to use the Integer from the macro in a cell's formula. In a macro I ...
0
votes
0answers
68 views

IF word in A1 matches word in Col C AND word in B1 matches word in Col E AND

Cell A1 contains a paragraph made of Hebrew words (They are Bible verses in Hebrew, so sometimes more than 254 characters long in each cell). Cell B1 contains a paragraph of English words (They are ...
0
votes
0answers
23 views

Excel for Mac 2011 move column 2 in sheet 1 to column 1 in sheet 2

I would like to move column 2 into column 1 for each successive sheet, one sheet per week, automatically. Is that possible? I do it with this formula ='1st week'!D2 and manually changing '1st week' to ...
0
votes
0answers
83 views

Excel - How do I write a formula or a macro to copy cells to a JPG file properties?

I have over 1000 jpg files and one excel file with all the properties(metadata) for them - title, subject and tags. The excel looks like this: Number ......Title ....... ...... Subject .. ...
0
votes
0answers
41 views

MIcrosoft excel 2003 auto populate cells

What I would like is input text into a cell say A1 and I want that text to then be duplicated in cell D1. But I can not type the formula into D1 itself i.e. no =a1. Is this possible? Thanks
0
votes
0answers
33 views

LibreOffice - formula to strip out addresses from a text field?

I run into this a lot so any tip would be appreciated. Is there a formula I can use that strips addresses (that change in length and are unique in each cell) from a cell that contains valuable text ...
0
votes
0answers
96 views

How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column with data and a conditional format that change, for instance, the background of some cells. I would like to copy this column with the resulting format in another place "by value", I ...
0
votes
0answers
38 views

Formula to find after how many cells a value repeats itself

I've been looking around like crazy for this: I.m looking for a formula to count after how many cells does the value 'O' occur. For example, in the below: xyz 7 O 7 8 ...
0
votes
0answers
36 views

How to use a variable end column in a SERIES (chart) forumula in Excel 2010

One of the series in my chart looks like this: =SERIES('sheet1'!$A$13,'File.xlsx'!Xaxis,'sheet1'!$B$13:$S$13,3) So what I understand is: The first argument is the series name The second argument is ...
0
votes
0answers
401 views

Using VBA how can I reassemble Excel worksheets in different workbooks while maintaing the format?

I am struggling to complete this code successfully. Basically I have a master workbook which has defined range names. The first range is the header followed by a number of ranges (section1, section2. ...