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

**0**

votes

**1**answer

20 views

### Deleting all cells in row after blank space

I was just wondering if there is a simple way to, for each row, move right and check if there's a blank cell and if so delete everything to the right of it. Can this be done with a formula?

**1**

vote

**0**answers

23 views

### Excel lookup value not on same row

What i`m looking for is that the data will be filled automatically from data table by lookup value in not same row.
See the picture
or xls example https://dropmefiles.com/BDlHe
1st table lookup ...

**0**

votes

**1**answer

15 views

### Excel- averaging subsequent cells using first unused cell in column

Good morning,
I need this output to be on a separate sheet, but essentially the problem I am trying to solve can be explained as follows.
I am trying to figure out how to average cells in a column ...

**0**

votes

**1**answer

36 views

### How to compare and sumproduct two cells value and use multiple criteria in IF in Excel 2013

I want to compare the month value (=TEXT(A1,"MMMMMM")) from sheet A with a cell value (JULY) in sheet B using an IF statement.
If the months are same then I need a sumproduct of the cells in sheet A.
...

**-1**

votes

**0**answers

16 views

### Excel: search, extract, paste function

I have columns containing strings with the names of colors. I would like to search a column for a specific set of colors and if these strings are found, paste them in another cell.
So basically, ...

**1**

vote

**1**answer

21 views

### SUMIF function with rules inside the interval

Is it possible to set the condition of excel's SUMIF function inside the interval where there are the value to sum?
In the picture above I have to sum only the value in the column BU that are > ...

**0**

votes

**1**answer

33 views

### Concatenate two columns, look for duplicates copy single row to new worksheet

I have an Excel or CSV file with 5,000 rows and 20 columns.
I want to concatenate Column 6 and Column 8, look for duplicates and then copy only one of the duplicates into WorkSheet2.
For Example:
...

**0**

votes

**1**answer

66 views

### Count based on multiple alternate conditions in Excel

I need a formula to count any trailers that are due in but do not have a cell that says "yard" or have a date next to that trailer. Please see screenshot. I tried the COUNTIFS formula and was ...

**1**

vote

**1**answer

44 views

### Formatting date from decimal in Excel [closed]

How do I format a decimal value like 1.30 as "1 year & 3 months" in Excel?
The value needs to remain the same but just needs to be displayed as 1 year & 3 months.
I need to do this just ...

**1**

vote

**1**answer

70 views

### Auto-populate values based on multiple dependencies in Excel

I have three columns with information filled in already. The value in Column C is dependent on two identifiers from Columns A & B (i.e., George is always the seller of Apples in America, but Sarah ...

**1**

vote

**1**answer

53 views

### LibreOffice Calc equivalent to Excel “Evaluate Formula” feature

Excel has an "Evaluate Formula" feature (it's located in the Excel Formulas ribbon among the Formula Auditing group, as shown in the picture below).
Is there an equivalent to that Excel feature in ...

**4**

votes

**0**answers

33 views

### Use functions like Countif, Countblank etc. to refer to discontiguous ranges.

Is there a way to use array formulas or some other feature to essentially have an excel function that means.
=Countblank({A1,A4:A6,A112:127,B29:B38}) etc.
Essentially, I want to refer to multiple ...

**0**

votes

**1**answer

33 views

### Excel spreadsheet formula to sum a column

I'm trying to create a formula that sums a total of only the trailers that are in the yard but don't have a date unloaded next to them. Please see the link for a screenshot of the spreadsheet I am ...

**0**

votes

**1**answer

31 views

### Count continuously until cell value changes

I have a work sheet with data like so:
A
A
A
B
B
C
D
D
What I'd like to do is count each occurrence of A, B, and so on like so:
A 1
A 2
A 3
B 1
B 2
C 1
D 1
D 2
Is ...

**0**

votes

**1**answer

27 views

### how to filter “all rows where column x doesn't contain [0-9]”

I'm cleaning a dirty directory. A few (just a thousand or so) records have their postcode messed up, either blank or containing other address data. Usually that means city or county.
A Postcode, ...

**2**

votes

**2**answers

18 views

### How to sum two categories within a single column and show the totals to the side of the column?

I have a list of trailers with a column to the side that says if the trailer is full or a pup trailer. I can't figure out how to make the column total how many of each type of trailer and show the ...

**1**

vote

**1**answer

27 views

### 2010 Excel formula dilema [closed]

I am trying to set up an inventory spreadsheet using 3 worksheets. the first one has the items in inventory and their counts. the second sheet consists of the number of items used at an event which ...

**0**

votes

**0**answers

5 views

### Google Sheets notification triggered from function result [migrated]

I have a google form that populates a spreadsheet with a timestamp and two numbers.
For technical reasons I can't have the numbers passed directly into a database but they need to be copy-pasted ...

**2**

votes

**0**answers

39 views

### Excel “Smart” Dropdown / Data Validation

In Excel, is there a way to create a dropdown menu or data validation field that
Allows the user to enter a value that is not currently on the list (like a combo box)
Save that value in the dropdown ...

**-1**

votes

**0**answers

9 views

### how do i use a command button so when I press it the information i have captures goes into a central store?

I have a rather large spreadsheet with 109 pieces of information that need to be captured.
I've set this up as a scorecard - at the bottom I have created a button called submit (its a command ...

**0**

votes

**0**answers

27 views

### Formula to reference a cell but not update with that cell

My workbook has data that is automatically loaded in from yahoo finance. The data is constantly being refreshed. I need a formula that can take the referenced cell and produce the number value of that ...

**0**

votes

**0**answers

25 views

### Excel formula works on one sheet but not the other. Non-blank max number

I'm trying to calculate the max number between 2 non blank cells in column A, using the values in column B when B>C. There are no blanks in B:B. C is always blank unless C>B.
...

**0**

votes

**0**answers

18 views

### Mode of text in excel

I have a spreedsheet with team numbers and some info about them (as text). Teams appear several time, and in a different sheet I have a summary for each team. I want to find the mode for one team.
I ...

**0**

votes

**1**answer

25 views

### Automated weighted counts in a Pivot Table (Excel)

I have data for sales people who work with different clients. Sometimes the sales person has multiple deals with the same client. I want to be able to work out the count for each sales person for each ...

**0**

votes

**1**answer

25 views

### Complex coutif formula

I hava a dataset that on the headers has: (String Employee Name, String Day off, time Check-in, time Check-out).
What I want to do is get a table that has row labels from Monday through Friday, and ...

**0**

votes

**2**answers

62 views

### Excel Formula To Get LAST Non-Zero Value in Row and Return Column Header

I currently have a spreadsheet of transactions, and I want to write a formula that will help me extract from each row a customer's most recent transaction.
Additionally, I want to then pull the ...

**0**

votes

**1**answer

36 views

### Excel, pair 2 columns, then find all rows where column J is in two different Column D

SO I would like to have a formula or macro, to look at a table in Excel, pair two columns say J and M. Then find rows where M is connected to more then one value of Column J, Column J is Super Group, ...

**1**

vote

**0**answers

43 views

### Align rows from different columns (which contain some cells that have identical values) using simple formula in Excel

I have 2 tables in Excel and I am trying (via Excel formula) to match/align some rows (ones that contain identical letters for the sake of the example included below). Btw, I don't mind aligning Table ...

**1**

vote

**1**answer

17 views

### How do I match cells in one worksheet to another and print a result?

Not new to excel, but new to formulas. I have to say this is killing me.
So what I am trying to do is take strings in one column, check if any of them are in another column of a separate worksheet, ...

**3**

votes

**0**answers

25 views

### Nested IF function returning tiny value instead of zero [duplicate]

Whilst creating a balance sheet for a lotto syndicate, I encountered this phenomenon;
Note the negative zero value in D5. With no formatting, its actual value is -0.0000000000000230926389122033 ...

**-1**

votes

**1**answer

43 views

### Whats is the formula in Excel to display A2 if A1 =1

I need to display what is in cell A2 if A1 is 1. How do I do that?

**0**

votes

**1**answer

44 views

### VLOOKUP where the column to look for is a drop down list?

I am doing a little format in which I call data from other cells, from other worksheet to fill it out. I am using VLOOKUP to do that.
So as an example I have:
Name: (Drop down list)
Sex
...

**0**

votes

**0**answers

32 views

### Excel performances: INDIRECT+ADDRESS VS CELL+OFFSET

Using Excel I can achieve the same result using two different approaches, here are the formulas:
A) =INDIRECT(ADDRESS(5+MATCH($A$16,Aliquote!$A$5:$A$15,0),1,1,1,"Aliquote"))
B) ...

**0**

votes

**1**answer

49 views

### Excel max number between 2 non-blank cells A, in column B (given no blanks in B) [closed]

I need to calculate the maximum number between 2 consecutive non-blank cells in A, but using the values in B. ie. use a formula in column C that will give me the results I manually plotted in D. ...

**0**

votes

**1**answer

31 views

### How can i create click function between A1 to A10 prompt inputbox for user to enter numbers in vba

I am trying to create input box for selecting range (A1 to A10), every time when i click any cells between 1 to 10, i want to prompt input box to enter numbers.
That's what i did but it's not working ...

**0**

votes

**0**answers

29 views

### How to combine two sheets based on their dates?

I have two sheets: one is stock index, one is air pollution value. I want to match the air pollution value to each trading day. How can I do it on Excel?
the first sheet looks like:
01/01/1997 39
...

**0**

votes

**1**answer

40 views

### Function excel for split a cell, vlookup into a column and concatena

I've one range with the value in column 1 and I want to look for the cells in Column 2 if they are in the range of col 1. When they are in the range I want the function to give me the value is col 2 ...

**-1**

votes

**0**answers

16 views

### Excel time and date query

I have an excel sheet where in column A has dates and column B has time, i tried IF function but in vain.
I want if the column A has same date in row then column B should not have the same time in ...

**3**

votes

**2**answers

49 views

### Round cell values on end of edit in Excel

I want to build a worksheet that when numbers are entered into a cell and the user ends the edit on that cell they are automatically rounded to the nearest 0.25. I do not want to have two columns, one ...

**-1**

votes

**1**answer

39 views

### Formula issue Microsoft Excel

I need help urgently for a work problem.
So basically, the summation of opening stock and production give total stock.
While closing stock is ((opening stock+production)-sale)) and stock cover is ...

**-2**

votes

**0**answers

31 views

### Excel Sort multiple rows at once

How do I sort all the cells of each row in a worksheet?
I have 3 columns: Option 1 > Option 2 & Option 3;
Not all rows (60+) are sorted A, B, C
I can sort each row but due to the number of ...

**1**

vote

**1**answer

17 views

### Count number of cells that match certain criteria with formulae

Suppose I have column with dates:
2015-01-10
2015-08-12
2014-01-02
2015-01-28
2016-10-14
I want to count how many dates fall to January. I would supopse that formulae is like this ...

**0**

votes

**1**answer

19 views

### Convert string Jan to January, Feb to February, Mar to March etc

I would like to be able to convert the string value Jan to January, Feb to February etc but I don't know how.
I have a table, populated some how, with the months, which looks like
...

**0**

votes

**0**answers

18 views

### All Data Option Filter Validation

I am creating a dashboard that has five inputs that filters my data by the custom data validation combination. My dashboard updates correctly based on the custom combinations, but I am unable to add a ...

**1**

vote

**1**answer

41 views

### Copying cells to another sheet and inserting rows for the copied cells

I am trying to create a sub that copies information from one form (which has four cells) to another sheet.
When it copies the information it also creates a new row.
Each form has a maximum of ten ...

**0**

votes

**1**answer

18 views

### How to compare differences for multiple possible pairs of cells in Excel

In Excel, I have 7 columns of data (numbers) spanning 100 rows. For each row, I want to check whether at least one number exceeds all other values by a certain value (say 0.2 or greater). If so, the ...

**0**

votes

**1**answer

47 views

### How to use INDIRECT with a SUMIF formula for a date range

I'm trying to get a SUMIFS formula to dynamically work with multiple tabs in an excel workbook. I have numerous tabs in this workbook, each that contains client data I update daily. In the example ...

**1**

vote

**2**answers

36 views

### Excel Formula difference between two dates

I have two dates as the following , I want to get the duration spent between them.
6/29/2015 12:18:04 PM
Regards,

**1**

vote

**1**answer

58 views

### Excel: Write a 1 or 0 value if condition is met

Sorry if this has already been posted, but I just started today playing with Excel macros and I need a hand. I have a data column with values and I would like that if the conditions were met, the ...

**0**

votes

**1**answer

40 views

### Excel: Is it possible to copy cells only if cells match in other columns?

If it's an EXACT duplicate, not just some answer that MAY suggest an answer then please provide the link in a comment.
Say, I have dates in column A of Sheet-1, and also dates in column A of Sheet-2. ...