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

learn more… | top users | synonyms (3)

-1
votes
1answer
181 views

Compare the value change of data in Excel between two similar sheets without VBA?

I need to match data in the column A's of two different sheets with similar data and then compare the values in column C. sheet 1 features data from this month, sheet 2 from last month. I have column ...
0
votes
1answer
67 views

Months and Date keep being switched around when running VBA

I have a column on my sheet, Column F which is full of dates. Now I need these dates to be in this format "dd,mm,yyyy" but sometimes they are in text form, us/armenian format in the original data. Why ...
1
vote
1answer
55 views

Clear contents from matched column of data in another sheet

I have a column of email addresses on sheet 2 col A (but I could put them on sheet 1 if it would make it easier / faster) that I want to remove from sheet 1 col D if matched (there may be 2 or more ...
0
votes
1answer
34 views

=randombetween with a certain percentage between a narrower range

I want to randomly generate dates (within a four-year period) for 32 different records/rows in excel. Additionally, I want 75% of those dates to be during one year in particular (within that four ...
0
votes
1answer
62 views

Excel formula for collapsing adjacent rows with a sum

I have a spreadsheet with values like so: blah 1 12 2 12 3 12 foo 1 6 bar 1 2 foo 1 6 2 5 Numbers in the middle column range from 1 to 8; ...
0
votes
1answer
69 views

Excel - minifs function returning 0

I'm trying to write a function in Excel 2013 that returns the minimum of a set of cells that meets two criteria, but the function always returns zero. On one worksheet, I have a table with several ...
0
votes
2answers
61 views

Calculating time between 5a-1p or 530a-130p

I was wondering if it is possible for Excel to calculate time span if each time block is in 5a-1p format in one cell. That way I could add the hours for each week without changing too much. Sample ...
0
votes
0answers
50 views

Excel using INDEX and LIST to return multiple results [duplicate]

I'm trying to use Excel with the INDEX and LIST functions (rather than VLOOKUP) as I am trying to return multiple results from one worksheet into another. I've got close with the following formula: ...
1
vote
3answers
141 views

Excel function working like SQL group by + count(distinct *)?

Suppose I have an Excel sheet with below data CODE (COL A) | VALUE (COL B) ============================== A01 | 10 A01 | 20 A01 | 30 A01 | 10 B01 | ...
8
votes
1answer
97 views

How does the NOW() function get evaluated cross time zones in Excel Online?

How does EXCEL ONLINE evaluate the NOW() function when a spreadsheet is accessed from different time zones? For example, if user PAUL is in London, UK and accesses a sheet at 5PM GMT and user SUE is ...
0
votes
1answer
39 views

How to compute regularization date in excel [duplicate]

Hi I want know if there is a formula in excel. Let's day I was hired Feb 24, 2014 so regularization is 6 months so my regularization date would be August 24, 2014. I want to know the formula in excel ...
1
vote
1answer
20 views

Date Arithmetic in Spreadsheets

I have a bunch of data in a spreadsheet where the left-most column represents a date in the format DD-MM. Here's an example for column A: 15-09 23-09 25-09 30-09 02-10 I ...
0
votes
1answer
100 views

Excel SUM From Different Sheets IF Date Found

I have a workbook with separate sheets for each product (about 20 sheets, adding more on a regular basis). Each product is only available for a certain time frame, and has daily sales data recorded on ...
0
votes
1answer
74 views

How to require a cell input if another cell has a value

I'm trying to edit the VBA for one of my workbooks so that If there is a value in column A, then a value for column C is required or else the file won't save, but i'm having some trouble with this. I ...
0
votes
1answer
37 views

Double Filter in Excel

I'm trying to "stack" filters in excel, so to speak. I want to filter column A to show anything greater than 30 and then I want to filter column B to show the top ten items. When I do this, however, ...
0
votes
0answers
40 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
3answers
46 views

Excel Extending Equations

So I have an excel table that is multiply 1 value against several other values. It looks like this: So I want the equations inside cells C14 to F14 to be B14*C5, B14*C6, B14*C7, B14*C8 respectively. ...
-1
votes
1answer
69 views

Excel: if cell in column A = text1 and cell in column B = text2 then cell in column C = text3

I'm desperately trying to figure out how to create a formula. In column F, I've created a data list of 5 items. In column H, I've created another data list of 5 items. In column I, I'm trying to make ...
0
votes
0answers
123 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 ...
2
votes
1answer
48 views

Auto formatting new pivot rows to data bars

I have a pivot table that takes data from database and displays it as a data bars. Problem is, that the data is updated daily - every day new row is added so the pivot table is growing. Unfortunately ...
0
votes
1answer
61 views

How can I sort data that is organized in 4-row chunks?

I have an Excel document with six columns and 57,000 total rows. Each record comprises four rows, that is, each set of four rows goes together. How can I sort this data by one field while keeping the ...
0
votes
1answer
62 views

Return day number in year from date in Excel

I am trying to show the date in an adjacent cell as the number of days in the year. Jan 1 would be 1, Feb 1 would be 32, Dec 31 would be 365. Also the reverse, how many days are left in the year. Jan ...
0
votes
1answer
29 views

Excel: filter consistent information from constantly changing cell

I am setting up an Excel spreadsheet and I am needing to extract a dollar amount out of specific cells. The problem is that there will always be a dollar amount in the cell but the information on ...
0
votes
1answer
168 views

VBA Solution to VLOOKUP with Hyperlinks

I am looking for some help with a VBA solution for preserving hyperlinks when using VLOOKUP on Excel (2010). I have a load of data on Sheet 1 for internal use only, and a cut-down version of this ...
0
votes
1answer
32 views

Excel - Four returnable values, given two crieteria

I need excel to return one of four possible values given two criteria. The two criteria are: Is the value in cell A1 >= to 10 OR A1 < 10 (two possibilities) and Is the value in cell B1 > ...
0
votes
1answer
28 views

Windows 7 Enterprise, Service Pack 1. Software MS Office Excel 2010

In Excel I understand there is no mechanism to customise & re-label the Rows & Columns (i.e. Renaming Col. A to some text like "Item Number" and so on. My question is regarding if it's ...
3
votes
2answers
183 views

Highlighting Cells in Excel based on the Value Associated with the Cell

I have two sheets: Users in their respective groups and A compilation of those same users with an expiration date. I'd like to highlight any cell in the the first sheet if their respective date ...
0
votes
2answers
75 views

How to dynamically populate a filter list with “first match” on a row, using excel 2007

The image is an example of what I am working with. I am trying to write a formula in my leftmost column, just under the listbox cell that you can see, which will find a string/value match in that ...
2
votes
1answer
24 views

How do i add a running total in A1 to B2 if there is a value OR subtract from C2 if there is a value

What I am trying to accomplish is that when I enter a number in B2 it will only subtract, A1-B2= A2 but if i put a number in C2 and nothing is in B2 I want it to add A1+C2=A2. I really tried looking ...
1
vote
1answer
33 views

Total row doesn't work properly, Excel 2010

In my Excel 2010 file and in one of its sheets I have a small table. As following, when I checked the Total Row option in Table tools > Design > Table style options > Total Row the tabled appeared as ...
4
votes
1answer
740 views

Add/Subtract button Excel VBA

Here is an example of my project in this picture I like collecting, so I am making an inventory sheet in Excel. I want to make a way that I can click a button and it affects the next, or previous ...
0
votes
1answer
78 views

SUMPRODUCT and IF conditional (LibreOffice) [closed]

I would like to multiply two cells in a same row, if a condition of a third adjacent cell is met; then sum the result to the next product in the next row (if condition is met), and so on. | A | B ...
0
votes
2answers
203 views

Excel: AVERAGEIF on multiple work sheets (via 3D Reference)

I have a question when using excel averageif functions: I have a workbook containing monthly information (stored as numerical values) in 12 different worksheets (named Jan, Feb,..., Dec). In each ...
2
votes
1answer
51 views

Match formula excel case sensitive

I have an if(iserror(match formula in excel. Just when I thought I was finished with my sheet I found out that the formula isn't case sensitive. Does anyone know how I can make my formula case ...
1
vote
2answers
1k views

Searching for a value in Excel, returning yes or no

I have one workbook, with two separate worksheets. I want to know if the values that appear in worksheet B also appear in worksheet A, if so, I want to return a "YES". If not, I want to return a "NO". ...
0
votes
1answer
84 views

Excel - Sum certain range from pivot table

I have an excel pivot table that looks like the following: Is there any way I can create a formula that will calculate the sales$ for a particular product at a particular store for a particular ...
1
vote
3answers
99 views

Trouble using Excel OFFSET function

I have an Excel workbook with numerous sheets entitled "Week1", "Week2", "Week3" ... "Week7". Each week, I input the customers IDs of people buying my product in column C, and -- as I am interested ...
0
votes
1answer
55 views

In Excel selecting an item from row when cell matches the first item in that row

I have a LIST of 5 optional sizes. There are several numbered mating parts that coincide with each of the sizes forming a pair. Any 1 of the mating parts can go with a chosen size, but only 1. Some ...
0
votes
1answer
26 views

Define names in workbook scope

I use MS Excel 2010. In this app, I know that using Formulas > Define names > define name it is possible to name a cell (or range). At that dialog box there is a scope rectangle which if I choose the ...
1
vote
1answer
170 views

Excel, compare cells in different sheets in a workbook

I am a real nooby and I have some questions. I have 5 sheets (A,B,C,D,E). Each sheet has numbers in it (10 rows-15 lines) A1 to I15 Now I want to make a 6th sheet in which I can compare the cell ...
0
votes
2answers
111 views

Is there one simple formula for Excel that can identify a string contain non-ASCII Characters?

I know that using the clean formula, I can clean up some of the non-ASCII characters (such as additional non-printable ASCII control characters #0 through to #31, #129, #141, #143, #144, and #157 ...
3
votes
1answer
105 views

Toggled Hyperlink with If:Then formula [duplicate]

I'm attempting to make a formula that will toggle a hyperlink on or off depending on the argument. Basically, I want to make it argue the following: If there is text in (Tab A, Cell A), then create a ...
-1
votes
1answer
26 views

Which formula should I use to place a user ID in one column based on a name in another column? [closed]

Edited to reformat: I have 530 rows of employees in a spreadsheet, each has their manager listed in column H. There are 15 managers. In Column I, I need to assign an auditor to review each employee. ...
0
votes
1answer
49 views

Search Excel price grid with variable values

WIDTH DROP 1150 1550 1850 2160 2360 2750 3000 3480 3980 4450 1200 338 348 378 413 431 450 467 513 ...
-1
votes
1answer
55 views

How to split up above single cell Address value into multiple cell value through macro

How to split up the below single cell value into multiple cell value through macro ? So, this... A1 = MISS FirstName LastName***95*SPECIAL ROAD**LONDON SW92**SW929AB ...should split as below, B1 ...
0
votes
1answer
660 views

Excel formula to sum all entries in a row if value of adjacent cell is equal to third cell [duplicate]

I need to build a formula that will sum the numbers in a series of cells only if the value of an adjacent cell is equal to the value of a third cell. The latter two values are text. In plain ...
0
votes
2answers
54 views
0
votes
1answer
47 views

How to lock a cell with formula in excel after the referred cell is filled?

I'm using "CONCATENATE" formula in excel and I would like to lock (or turn the formula into value in) the cell which contains the formula after the last referred cell is filled. Some of the referred ...
0
votes
1answer
73 views

SUMIF with multiple match criteria in Excel

I'm trying to create a formula so that if in cell E2 it says "Market", "Limit", or "Stop" it adds the numbers in cells L2 and M2. =SUMIF(E2, "Market" "Limit" "Stop", L2+M2) These expressions are ...
0
votes
2answers
148 views

Nneed to calculate average of NON sequential multiple cells & I want to exclude values = 0 or Empty Cell with AVERAGEIF

Nneed to calculate average of NON sequential multiple cells and I want to exclude values = 0 or Empty Cell with AVERAGEIFS. There is a mistake in my formula but I can't find a way to have it since ...