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

**2**

votes

**0**answers

214 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

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

**2**

votes

**0**answers

291 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

28 views

### Excel: Keeping references/links when transposing

I have a worksheet in where I have table (let's call it the summary table) with references to a another table (among others) which is 2x40 (rows x columns). I would like to tranpose this into a 40x2 ...

**1**

vote

**0**answers

29 views

### How to combine data from multiple sheets into 1 main sheet in calc

I have an excel file which gets generated from various database servers on fly by executing sql. Separate sheet gets generated for each server. Now I simply want to combine data of all sheets. Each ...

**1**

vote

**0**answers

72 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

72 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

42 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

36 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

57 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

84 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

140 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

1k 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

204 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

10 views

### Using Query Function in Google Spreadsheet where {data, query, [headers]} is determined in another cell

For example in cell T2 contains the following text generated with the =CONCATENATE function
DW004!$A$11:$H, "select H where (C contains """&$E2&""")", 0
This was created with the format
...

**0**

votes

**0**answers

8 views

### summing values associated with names in multiple columns

I'm trying to sum up total values with names. There are 8 total columns rotating between a list of names and then values. I believe a sum if function would work, but I'm too novice to find the right ...

**0**

votes

**0**answers

14 views

### Convert COUNTIFS formula to SUM(IF) to enable external workbook references?

I need to convert the following formula to one that uses SUM(IF), as I am given a #value error when the external data source is closed.
...

**0**

votes

**0**answers

25 views

### Return value from table using Multi-dependent drop-down list

I am trying to obtain the value of a column header (or topmost row value for a specific column) based on the selection of properties from 2 drop-down lists.
...

**0**

votes

**0**answers

33 views

### Creating automated Microsoft Excel filtered list

I have the following situation in one of my Excel sheets:
I have a table with some values, particularly "date", "stock" and "cost" among other data. The table may contain values from various ...

**0**

votes

**0**answers

39 views

### Google sheets formula reference cell data in another sheet

I need help with formatting a sheet. My sheet that I need to format is an attendance list. I want the names of the students in column A that have a certain teacher. I want to pull those names from ...

**0**

votes

**0**answers

49 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

52 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

41 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

34 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

68 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

28 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

85 views

### Getting data from closed files with concatenate formula

Each day a 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 that.
Based on all ...

**0**

votes

**0**answers

11 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

61 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

74 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

68 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

46 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

37 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

47 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

67 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

54 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

70 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

48 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

50 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

61 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

75 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

58 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

57 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

29 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

27 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

74 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

28 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

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