Tagged Questions

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

learn more… | top users | synonyms (5)

1
vote
0answers
7 views

Make Excel formula yield `blank` as an ISBLANK() = TRUE

Make Excel formula yield blank as an ISBLANK() = TRUE I found couple poster asking about what are the differences between "" and simply (blank), but now how do make formula return blank? Example: ...
0
votes
1answer
6 views

Does a range of cells contain a value in a list?

I want to answer - does a range of cells (A1:E1) contain any value from a list (G1:G50) In other words: Does A1 OR B1 OR C1 OR D1 OR E1 contain any value that appears in G1:G50?
0
votes
1answer
16 views

Excel, how to check cell value against a column of values?

Using excel 2007, I have 2 worksheets. The first one is 1 column of about 600 numbers/IDs. The 2 worksheet is about 30 columns for about 5000 IDs. Im basically trying to filter out the 600 IDs ...
1
vote
1answer
12 views

Convert Text Value to Time Value in excel

I am trying to convert text to time. Basically I copy and pasted the total time of employees but it pastes as text value and then in order to add the time I have to convert it to time and I have to ...
0
votes
0answers
21 views

Index/Sumproduct calculation error

I have been working on an excel problem for the better part of 24 hours. I have a formula that almost does what I want but it is not returning what I imagine it should return: ...
1
vote
1answer
23 views

How to count blank cells at end of row in n-column table

Suppose I have a table with 50 rows and 50 columns. Each row has a certain number of blank cells, often occurring in the last few columns, with the first few columns being relatively dense. I would ...
0
votes
1answer
27 views

How to combine two columns with no data loss for further analysis

Hopefully you good people can help me as I'm pulling my hair out doing this manually and can see no way to automate At present I have two sets of data consisting of three columns each - Date, Amount ...
0
votes
1answer
25 views

Is there a function to get the cell your equation is pointing at as the output rather than the value inside the cell?

For example if I had an function such as vlookup that put me into a cell could I then have another function that gives what cell it found rather then the value inside the cell?
-1
votes
2answers
35 views

How to compare multiple columns and set values based off of it

I Reference to the picture in the link below, I need to find the best way to compare which Start Month and End Month row the current month is between and then set a cell somewhere else in the spread ...
-1
votes
1answer
18 views

How do I reference a specific row number using a defined column name?

I'm using Excel 2010 and am creating a workbook to define details about products in a store (including category, price, accessories). I also want to define default details to use if the specific ...
-1
votes
1answer
22 views

How to make COUNTIF only count subtotal for visible records when filter is applied

I would like a formula to COUNTIF the Brand 3 column and if filtered the COUNTIF changes to filtered criteria. TOTAL COUNT 0 0 3 COUNTIF(H3:H5,"VoID") Area ...
0
votes
0answers
13 views

Copy distinct values in calc

I have some text data in column A, adding data to that column is the standard use case. I would like Calc to dynamically copy only unique values to, say, column D. I am aware of OpenOffice: get ...
0
votes
2answers
35 views

Find partial text in Excel 2013

In Excel 2013 I would like to find the value in column B in the row where a string matches a string in column A. What complicates it is that the strings in column A are allowed to be shorter than the ...
0
votes
2answers
39 views

Excel last appearance of a given string in a column

I am using the LOOKUP function to find the last appearance of a given string in a provided column, like this: LOOKUP(A3;Runs!D3:D6;Runs!A3:A6) However, the bounds in the vector are hard-coded: ...
0
votes
1answer
50 views

Auto Sequential numbering based on cell criteria - create two independent automatic numbering sequences

Is it possible for the numbering in column A to skip any rows that have the Word "TOTAL" in column C but pick right up again in the next rows? At the same time can the rows with the word "TOTAL" in ...
0
votes
1answer
47 views

RIDICULOUS EXCEL FORMULA, IF [#] minus [#] and > [Date] [on hold]

Im trying to create a formula for the below example: cells A1-A15 MOS #'s 0311,0352,0341 cells E1-E15 End of Tour Dates First I need to get the count of the MOS #'s for each SPECIFIC MOS, ...
0
votes
1answer
29 views

How to display a given number of names randomly selected from a list

I have a list of 148 names in Column A. I need a function that would let a user enter in a number and use that number to return a list of names in column B or C. There can be repeats. So column B ...
0
votes
2answers
20 views

Lookup and match functions

I have a problem that I have been unable to solve, or find a result that works for me. I have a sheet with data entered from a lab. The dates are based on sampling time, and each sample point is ...
0
votes
1answer
22 views

I need a formula so that if a value in column a change then add +1 to the value of another column

I have a list of product codes in column A. In column B i have a list with text values which i will call "positions" (1B 02 1 etc). In column C I give a serial number for each product code (52001001). ...
0
votes
1answer
24 views

Get Excel function that expects a list to use the contents of a cell

A recent question involved cells that contained a comma-delimited list of values, each if which needed to be individually selected and manipulated. The data looked like this: Cell B1: ...
0
votes
0answers
9 views

Prepend text for spreadsheet

I want to have a string value prepended to a XL-style formating in a spreadsheet program (gnumeric). The current formatting is [$-f4f2]hh:mm which should take 1:00 PM as input and convert/display it ...
0
votes
1answer
34 views

How can excel take two seperate cells with comma seperated data and pair them up?

I have looked all over for an answer to this and seem to be stuck fast. I have something like this: Cell B1: 01/01/2014,01/02/2014, 01/03/2014, 01/04/2014, 01/05/2014, 01/06/2014, 01/07/2014, ...
0
votes
1answer
35 views

How to fill down formula so that range references are offset by 5 rows rather than by 1 row

How can I get Excel to increment ranges arguments by more than one row when I fill down a formula? For example, if I want Excel to keep averaging the next five numbers in a column, how do I set the ...
0
votes
2answers
20 views

Excel Highlighting First Empty Cell Below An Entry

I've been trying for hours what I thought would be simple but clearly is not for me! I have a column where every ten cells has the word "TRUE" in it, the rest of the cells in between are empty. So it ...
0
votes
1answer
16 views

Adding another value in Excel cell/column if another cell has particular value

I wanted to be able to create a macro to change the value in Cell B to EPTBSIET if it equaled that value in Cell E - SIETCO. I've been able to compile an IF formula but would rather have the value not ...
1
vote
1answer
24 views

Excel duplicates in one column based on another

Hoping someone can assist with this. We have a spreadsheet with data as below: A B C D E F G 777142 233 4 45151 te01-ccm 0 777303 233 6 45151 te01-ccm 0 ...
0
votes
2answers
19 views

Match-Search function in Excel?

So I've got 2 columns. Column "Mess" has gazillions of random numbers x digits long. Column "Check" has specific sorted numbers. Now I make a 3rd column called "boolean". I want a function to check ...
0
votes
3answers
36 views

How can I merge 2 sheets in excel?

I have to update a defect database in excel that is filled with data that fluctuates between 160 to 700 rows every day. This database has 19 columns, some of which are numerical and some are ...
0
votes
3answers
20 views

Create custom parts for the header and footer in Excel

I have a spreadsheet with several worksheets. On each printed page of each worksheet I would like to have references to some fields that i define somewhere in the document. More specifically, i would ...
0
votes
0answers
11 views

Excel referencing: Go To Box does not fully have the whole path

When I reference to another workbook in the formula line I see the whole path to the workbook. So that is good. But when I have the referenced workbook closed (the one I get figures from) and I want ...
0
votes
1answer
28 views

Excel: Matching and counting records (rows) to multiple conditions and unique values

I have two sheets: ‘Data’ and ‘Report’. In the report sheet, I need to calculate some data which requires 3 different formulae. The Data sheet has column headers in row 1 and many records of data in ...
0
votes
1answer
20 views

How can I return corresponding value from Master List by comparing Child List?

I have 3 columns MasterList, Value and ChildList. Now I want to compare Childlist against Masterlist and get the value from corresponding MasterList. I have below values in worksheet. MasterList ...
0
votes
2answers
33 views

Excel formula to calculate percent of growth over multiple months

I am trying to determine if a customer's number service requests have increased or decreased of a 12 month cycle and by what percent - F2 thru Q2 show each individual month's number of requests - in ...
0
votes
0answers
8 views

Microsoft Excel 2010, Adding/deleting lines in linked workbooks by only adding/deleting them in the Master Workbook.

I have Multiple Woorkbooks linked to one Master Workbook. Each WB has 13 columns and 452 rows(Different Products I sell). Only one column(My Selling Price) is linked to the Master as of right now. ...
0
votes
1answer
23 views

Convert Array to String with Excel Formula, is it possible?

Convert Array to String with Excel Formula, is it possible? I know how this would be done with VBA, but I have to share my workbook, and I have to assume that other people will not be able to enable ...
0
votes
1answer
24 views

How to use SUMIF to calculate 12-month moving averages

On a single sheet I have Datapoints listed from top to bottom (A:A) and months from left to right (C:AI). In Column B I have an indicator which is used with SUMIF to 'group' certain data points. ...
-3
votes
0answers
22 views

Lookup that Use a cell that contains range as lookup value and lookup vector

Sorry, I can't post image here because of restriction. But, I can't find another way to explain my problem. I have a sheet like that. And then, I need cell B5 to return a value based on data from ...
0
votes
2answers
29 views

Find most frequently used value

I know that that question has been asked before but my question needs more scalable solution! I have a column G with States, there are 10,000 States in that column. I need to find out what is most ...
0
votes
1answer
11 views

Excel max values across many worksheets

I have multiple worksheets each with 4 columns of 32 names and values. I need to be able to find the highest 8 values(with associated names) from across all sheets. I have been able to determine the ...
0
votes
2answers
32 views

Shortening an Excel formula if possible

I am unable to shorten an Excel formula I am using in an Excel database. I have googled it, but this appears to be the shortest way already from the first page on Google. ...
3
votes
1answer
37 views

How to convert data in Excel to percentages of total?

Is there a quick way to convert all cell values in a table to a percentage of a total listed in a separate column? I tried posting an image of my data as an example, but my reputation here isn't ...
0
votes
1answer
25 views

Formula Required for Costs to date based on number of project days completed

I am trying to put together a template for project costs and am looking for a way to roll forward daily costs for each account code that avoids the need to change the formula on a daily basis. I have ...
0
votes
0answers
15 views

Continue from one excel sheet to other without merging them together

i have an Excel workbook whic contain monthly sheets of sales. Each monthly sheet contains sale figures for various items (in columns) on a daily basis. the days are grouped in weeks. Now i want to ...
0
votes
1answer
48 views

Lookup Values Based on Values in Other Cells

I have an inventory list that contains a manufacturer name and product category for each product. I want to look up the code for the manufacturer and the category code for the product and place the ...
0
votes
2answers
26 views

Reference cell range parameter from other cell dynamically in excel

I've got a simple range based formula =SUM(A3:A$5) I would like to dynamically assign the range value in the formula by referencing another cell's value, for example cell B1 = 3 and cell B2 = 5 ...
0
votes
1answer
35 views

Inserting N Values into N Blocks

I have a column with values that repeat an arbitrary number of times before the next block begins. Some of these blocks, however, contain no number at all. While every other block is unique, these ...
1
vote
1answer
17 views

How do I have a Date format show in a Formula that is looking up that cell?

I have a formula: ="('"&A6&"','"&B6&"','"&C6&"','"&D6&"','"&E6&"')," And B6 is a date and formatted as dd/mm/yyyy (have tried all forms though) so the ...
0
votes
1answer
33 views

in Excel, Can I replace “sheet1!” with a formula?

I have 100 sheets named as 1,2,3....100 then in each sheet, there's a value in cell A1. I am building a summary sheet now, in the summary sheet, I want A1 = 1!A1, A2 = 2!A1, A3 = 3!A1 ...... A100 = ...
0
votes
1answer
31 views

Convert a range to a percentage in Excel

I have a range of figures I want to convert to percentage. For example I have temperature readings taken every hour over a full day. The range is from 4.5deg to 15.5deg. I want 4.5deg to equal 0% and ...
0
votes
0answers
13 views

save a sheet in new closed workbook without links

I have this code which work pretty good but I have a problem that the new exel workbook have links from the old one. Sub archive() Dim FileExtStr As String Dim FileFormatNum As Long Dim xWs As ...