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

**2**

votes

**0**answers

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

**0**answers

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

**0**answers

49 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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

858 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

**0**answers

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

**0**answers

9 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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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

**0**answers

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. ...

**0**

votes

**0**answers

54 views

### Insert Scroll bar in excel using Index function

I am trying to make a list scrollable in excel. I want to use the Index Function. Any tips or any suggested resource which I can refer to. Below is my formula that I am using in my sheet to get the ...