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

**0**

votes

**1**answer

26 views

### IF X and A display area “A1,G20” If X and B display area “A1,G20” but on a different sheet

I'm trying to put together a excel spread sheet for our quarries.
but we have a lot of different pits along two rivers that we collect our raw material from each with its own set of sieve results. now ...

**0**

votes

**1**answer

31 views

### How to modify these VBA codes to save excel sheet in excel format (.xlsx)?

I have added a button in excel sheet and now when I click that button it saves the sheet at specific path with specific name but in pdf format.
I want to save this sheet simply in excel format ...

**0**

votes

**0**answers

31 views

### How do I delete if is a duplicate of multi columns? [closed]

How do I use a macro to delete colum a if it is a dplicate as well as if column be is a duplicate - they both need to match this for the macro (or formula)
----------edit
columns a has phone number ...

**0**

votes

**1**answer

12 views

### Getting the name of a named cell in MS Excel 2011

I am using a formula in Excel, which involves cell names.
The formula is
=MIN(name1,name2,name3)
name1 is the smallest, but it shows name1's value. I want it to show the text "Name1". How do I do ...

**3**

votes

**1**answer

18 views

### Excel INDIRECT function referencing cell with variable row from another worksheet

I cannot get the INDIRECT Excel function to reference a cell from another worksheet.
I've tried many variations of the following formula but always get an error
=INDIRECT("'Form Responses ...

**-1**

votes

**1**answer

22 views

### Excel formula need formula for if value in A1>0 we need A1, but if value in A1 is 0, then we need the lowest value of A2 and A3

A
1 5000
2 10000
3 12000
4
I need a formula for if value in A1>0 we need A1, but if value in A1 is 0, then we need the lowest value of A2 and A3.

**5**

votes

**2**answers

47 views

### Can Excel perform a count-if-like sum mixing absolute and relative cell coordinates?

Suppose I have a spreadsheet structured a bit like this
_A____B____C____D____E____F_____
1 |XFoo|XBar|YFoo|ZBar|XBaz|Result
2 |8 |3 |0 |1 |100 | (*)
3 |2 |1 |1 |20 |10 | (*)
I ...

**0**

votes

**3**answers

20 views

### Consolidate data from several worksheets with same columns but different number of rows in Excel

I have an Excel workbook with several worksheets. They all have the same column names. However, the number of rows differs from sheet to sheet. I want to create an overview sheet where I can see all ...

**0**

votes

**2**answers

31 views

### Excel Formula - Budget Structured Tables - Monthly View

I've been searching around and I am struggling to find why my formulas are not working.
Before I post my formulas I need to explain what I'm trying to do and it is a bit complicated (At least, this ...

**0**

votes

**2**answers

63 views

### In Excel 2010, how can I subtract two times when I also have the date in the cell and get the value in seconds?

I have a column of data as follows:
09/22/2015 10:25:22
09/22/2015 10:27:00
I want to subtract 09/22/2015 10:27:00 from 09/22/2015 10:25:22 to get the value of 98 seconds.
I tried formatting ...

**0**

votes

**0**answers

47 views

### Conditional formulas in Microsoft Excel

I have different conditions in a sheet where I need to update certain factors like term of loans and down payments. How do I apply a formula in excel where I can get direct result for these:
Down ...

**0**

votes

**1**answer

23 views

### Excel - New total for every inserted amount

I'm trying to calculate a new total on the next cell in one column for every new addition to another column.
Here's an image of what I'm trying to do:
I want the Debit column to be subtracted from ...

**0**

votes

**1**answer

44 views

### How can I divide cells into two parts in Excel?

For example I want to divide cell B2 into two parts, without inserting any new row or column. Please see the screenshot below:

**3**

votes

**1**answer

41 views

### Sum values in specific rows based on whether the row has a value that belongs to a range

Here's what I'm trying to do:
I have a table that has several metrics (example: revenue, cost, etc.) for about a hundred different accounts. I can't modify this sheet because it gets auto-updated by a ...

**1**

vote

**1**answer

31 views

### How to remove space before and after any numbers?

How to remove space before and after any numbers?
for e.g. " 3,340 ".
I want this number to look like this "3,340".

**3**

votes

**2**answers

37 views

### How to use one formula in a cell

I'm forecasting multiple items monthly over ten years. There are several products that will be introduced during future months while others are simultaneously phased out. I have formulas for the ...

**2**

votes

**2**answers

32 views

### Excel conditional formatting rows to highlight weeks in different colours

I am trying to automatically format the rows of my worksheet based on the date in the A column.
I am using the following to highlight the weekends.
=WEEKDAY($A6,2)<5
This works however I also ...

**1**

vote

**1**answer

34 views

### Excel - group cells that the values together is near to a number

I have this spreadsheet:
Street 1 | 234m
street 2 | 438m
Street 3 | 759m
Street 4 | 934m
Street 6 | 126m
Street 7 | 462m
These values are summed and divided by 2 in this example, because let's say ...

**1**

vote

**1**answer

22 views

### Data deletion using 'If' function

Is it possible in Excel as using vlookup or if function that if data is 0 then that row should be deleted? For example:
If cell B1=0 then DELETE cell b1, and if cell B1=1 then UNCHANGED

**0**

votes

**0**answers

16 views

### How to copy all sheets names without data?

In excel, I have workbooks and each workbook has ~60 different sheets. Now I have to copy the file with all their sheets and sheet names, but not the data. Can anyone tell me how to do that?
Thanks

**0**

votes

**1**answer

47 views

### How to identify strings that don't *contain* any of a list of other strings

I have Column A and Column B with text.
I want to copy Column A to Column C and remove rows
that equal or contain a value in Column B.
I have this formula but it doesn't remove containing rows, only ...

**3**

votes

**2**answers

157 views

### Non-intersecting values in columns in Excel

I have three columns with values (A, B, C) as shown below. I would like a formula in column D that will return ALL the values from column B that are not listed in column A. Similarly, what formula ...

**0**

votes

**3**answers

47 views

### How do I use Excel to count the times a string appears in other sheets?

I have a worksheet that contains 10 sheets with data about facility checklists.
I am wanting to create an overview sheet that looks through all of the other sheets can counts the number of times the ...

**1**

vote

**0**answers

15 views

### Excel formula to convert self to value?

Is it possible for a formula in excel to return a value and assign it to itself, overwriting the formula?
Example: If column A consists of
=IF(LEN(B2)>0,"1","0")
On cell contents in B2, I want ...

**0**

votes

**0**answers

14 views

### How to create If a range cell in worksheet equals Y, then create new worksheets

I am trying to get my macro to copy sheets if a range value in active sheet = y, then create worksheets based on the Y values from the B Column with no duplicates. Any suggestions to what I am ...

**0**

votes

**2**answers

25 views

### What function do I use for looking up how many times something occurs in a cell for each department?

Partial clip of my spreadsheet:
Dixon Tamara tmdixon2 Anthropology SecurID 700
Portera Maria mportera Anthropology SecurID Software ...

**0**

votes

**0**answers

24 views

### How would you set this up in Excel 2013? [duplicate]

I recently got hired in a sales position and would like to make sure im getting paid for what I have sold.
I have a worksheet with 3 columns where I keep track of what I have sold, 1 column shows ...

**-1**

votes

**0**answers

15 views

### Google sheets function [migrated]

I have two columns a and b
a contains a set of text and
b contains a set of numerical that relates to each row in a
I want to create a function that will search column a for substring value and ...

**1**

vote

**1**answer

38 views

### Excel Formula - Count Unique Projects but Exclude 'Admin'

I am looking for some Excel related help for a formula which I've not been able to figure out myself.
I have a sample list of data in range A1:B7 - Column A is a list of names (e.g. John, Jane, etc) ...

**0**

votes

**2**answers

50 views

### Excel formula to change one cell value to another value based on a third cell

to make this simple a1 has a value that can change, b1 has the formula to test a1 value. c1 is reset to a chosen value based on a1.
I have a table that people can input values. I have a list box in ...

**0**

votes

**2**answers

69 views

### Excel Formula To Get First and Last Non-Zero Values

I have checked the similar questions but they are little bit different from what I need to do.
What I need to do is create a summary table for large data, some of that data is this:
[A] [B] [C] ...

**0**

votes

**1**answer

25 views

### Why does this Excel formula return 0.042361? =Sum(Indirect(Address

In sheet3 columns A,B,C,D are the value of row numbers. Thus, A1 to D1 contain 1, A2 to D2 contain 2,...etc.
Why does the following formula in G2 of Sheet2 return 0.042361? All numbers are integers. ...

**3**

votes

**1**answer

57 views

### Return multiple values from one reference id

is it possible to have an excel formula that would create the output table below?
initial data:
╔════════════╦═════════╦══════╦═══════╦═════════════╗
║ product id ║ item ║ size ║ price ║ size ...

**0**

votes

**1**answer

32 views

### How to fill up gaps in excel

My data in excel has for example the following data in columns A and B:
A B
1 4
2 6
5 88
9 67
12 45
and so on.
I would like to transform it as following:
A B
1 4
2 6
3 0
4 ...

**1**

vote

**2**answers

35 views

### Extract string containing a word from an Excel cell with hundred of strings

I have an Excel workbook, in column A each cell contains all URLs of a site (this means hundreds of URLs in one cell).
Among all the URLs within a single cell I want to extract only the URL that ...

**1**

vote

**0**answers

37 views

### Sum data from muliple cells, multiply by other cell range, reference on other sheet

Ive had a couple issues with this workbook recently this seems like the biggest. As you can see in the pics, this a time sheet that needs F15 multiplied by Q15, then added to F20 which was multiplied ...

**1**

vote

**0**answers

23 views

### How can I make an excel user defined function (UDF) work in an array function (CSE)?

I am using Excel 2010.
I wrote a UDF to create a unique number for each month:
yearmonth = 12*Year(date) + Month(date)
Now I want to use it in a CSE:
...

**-2**

votes

**0**answers

19 views

### I need to know the answer to a formula [migrated]

I have a question, I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using google sheets by the way)
I want the cell underneath the day ...

**2**

votes

**1**answer

45 views

### On Excel I want to create a list from another sheet based on two values

I have 2 values in dropdown menus in cells B2(DATE) and B5(ITEM).
from these values I want to create a list of items(based on B5) that were sold on that date(B2).
The transactions are listed on a ...

**0**

votes

**1**answer

41 views

### Formula to extract certain character positions in a string

I'm busy here with excel but kind of struggling with some kinda formula.
I got first and last name in one cell and I want to combine them in an another cell. but I just want the first letter of the ...

**1**

vote

**2**answers

22 views

### Excel EDATE Formula

I'm using Excel 2013. I have an edate formula in column B which looks at at the date in column A then adds 5 months onto it. My question is, if there is a blank cell in column A a weird date appears ...

**1**

vote

**0**answers

28 views

### Excel Formula to autopopulate a cell and mark as Assigned

I am attempting to auto populate a excel cell with an account number with the following formula.
=INDEX(C2:C20001,MATCH(FALSE,B2:B20001,0))
Column B contains True/False and Column C contains the ...

**0**

votes

**1**answer

27 views

### Synchronize a part of two worksheets in Excel 2013

I am trying to build a workbook in excel where I want to synchronize a part of two sheets with each other. I search around on the internet but didn't find the solution yet. Basically what I want is ...

**2**

votes

**2**answers

28 views

### Excel fiscal quarter formula

How do I write formula to drive Fiscal quarter from Date
My 2016 fiscal year starts from Feb 2015 and ends in Jan 2016
So for Feb 2015 the desired out put is Q1-16 and for Jan 2016 is Q4-16

**0**

votes

**1**answer

16 views

### Taking data from an adjacent cell by name in cell

I am a basic Excel user.
I do a monthly rota which has the staff name in one cell and in the following cell it has the hours worked.
Is there a simple way to write an formula so that Excel takes any ...

**0**

votes

**1**answer

30 views

### VBA Script loops in all sheets but doesn't produce output in all sheets

This VBA script I have loops through all sheets but I don't see the output in all sheets. What would be the reason be?
Sub A()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim ct As ...

**0**

votes

**1**answer

11 views

### Keep an element as constant on Formula

I want to keep one element of the formula as "constant" but without writing it as a number in the formula e.g.
I have this function:
=COUNTBLANK(B2:M2)*Q1+N2
And if I drag it to the rows below it ...

**0**

votes

**0**answers

11 views

### Formula to recopy data while skipping columns in Excel 2013

I want to fill a column in Excel (say C column, starting at cell C8 for example and filling the following ones C9,C10,C11...) with values that are repeated on a same row but every 8 column ...

**2**

votes

**1**answer

46 views

### How can I execute this function using VBA in excel?

I'm trying to get this function in VBA but I'm new to it. I don't seem to know how I can get this done:
What I'm trying to do is, for each cell in column B that's numeric, I want to take the next ...

**-1**

votes

**1**answer

37 views

### How to do this function in Excel?

I'm not an advanced excel user but I have a query about it. I have a set of values in cells column B and there are random empty cells between one value in column B to the next value in a cell in ...