All Questions
64
questions
0
votes
0
answers
68
views
Need Help Migrating A Drop Down List From One Workbook To Another
I need some help migrating a drop down list and the data selected from that list to another workbook.
I have a list of people that need to be contacted each week, with an updated file that is sent to ...
0
votes
1
answer
38
views
Excel Conditional Formatting Cells in a row
I have an excel spreadsheet organizing material orders, I have 2 columns "Date Expected" and "Date Delivered" I have 1 row functioning the way that I want it to which is if "...
- 3
-1
votes
1
answer
63
views
Returning value within two column when it match 3 criteria
I would like to return a value 4.42295, if i choose MYR. If I choose SGD will return 1.32963 as picture show below. I had tried some formula in the picture but seem like cannot solve what i need. ...
0
votes
2
answers
166
views
How to TEXTJOIN/CONCATENATE based on constraints for specific no of rows count
I need to concatenate certain attribute value based on the sequence number for specific material number and a formula to automate the process.
I have columns as listed in the below screen shot and I ...
- 13
1
vote
1
answer
1k
views
How do I apply the MATCH() function only to a filtered range? Or is there another way to do that?
I have a filtered list of values. Let's say I have the numbers from 0 to 5, and 2 is filtered out:
A (with auto-filter against "2")
0
1
3
4
5
Now I want to find out whether one of the ...
- 2,423
1
vote
1
answer
350
views
Hyperlink does not change along with cell when filtered
I’m having a problem with hyperlink when using filters. So let’s say I have these table rows:
A
B
C
I have a hyperlink to cell C using the usual hyperlink option. But if I change the filter from “A to ...
- 11
0
votes
2
answers
317
views
Get unique IDs with date 1 or date 2. For IDs with both date, obtain row with most recent of the 2 dates
I have been trying to retrieve the row in the following sheet (sheet1) where the dates are matching dates in sheet2 without duplicating the IDs in Sheet1 (Column B)
Sheet1
Sheet2
In other words, I ...
1
vote
0
answers
87
views
Hours turned/paid/worked
I am attempting to create an Excel sheet that provides a few calculations based on three things:
Hours turned by specific technician (4 different techs)
Amount of hours worked by each tech (up to 8 ...
- 11
0
votes
0
answers
80
views
Creating a technical excel data sheet to display efficient data
I am creating a table in excel that holds milestone achievement data with their respective amount. This table has multiple milestones that have only one amount, meaning that a set of milestone ...
0
votes
1
answer
70
views
Excel Sum Table based of criteria missing from another table
I have 3 tables. Table1 is a list of items w/ a sum attached to them.
Table 2 is a list of "official items" and a category.
Table 3 is a category that needs a sum.
i.e.
Table 1
A.
B
Parrot....
- 1
2
votes
1
answer
66
views
Proper Syntax for AND OR NOT Excel Formulas
I was practicing some basic formulas in excel. The last 3 I worked on are as follows:
=IF((OR(C4="Hourly",C4="Contract")),"Yes","No")
=IF((AND(B4=&...
- 23
0
votes
1
answer
216
views
Is there a way to alias formula blocks for later use in the same formula in MS Excel?
Is it possible to alias a formula block and refer to it later in the same formula? For example, suppose I have an IF expression and the logical test component is a complex formula in itself which I ...
0
votes
1
answer
114
views
How to decrease the number of cells that are selected in the formula in Excel?
I'm using the AVERAGE function and drag/expand it along one row (e.g. from A1 to D1), the cells that are selected should decrease by 1 row in each new column.
For example:
A1
=AVERAGE(A2:A10)
B1
=...
- 1
0
votes
1
answer
377
views
Autofill Blanks with Zeros if no other Numeral Present
I have a working system for giving employees an opportunity to track their goal progress on a week to week/monthly basis. I've now protected all cells except the 'Monthly Admission Goal' and grid ...
- 1
0
votes
0
answers
1k
views
Evaluate function not working
In the example, entering the combined values in K2 as a formula produces the result in J2. J2 is value copied and pasted in J4. To confirm that it is a formula that will not produce an error, the ...
- 13
0
votes
1
answer
494
views
In Excel my formula is skipping the first two cells of a table
Hi I have an Excel Spreadsheet and I'm trying to convert a table with multiple rows and columns into just one column for sorting and filtering. But the issue is that it doesn't start at cell A2 where ...
-1
votes
1
answer
458
views
how to distribute clients with due amounts over agents based on amounts on excel
i'm not sure how to put it right but bear with me.
let's say i own a company and i have like 5 clients who owe me money.
so, i hired 3 agent to try retrieve the money for me for a commission of 20% of ...
0
votes
1
answer
1k
views
How to transpose and skip cells?
thanks for being so helpful. I have columns where every 6th column had an alphabet letter: a b c d e f, so that column 1 has letter a, column 7 has letter b. I want to transpose that into rows of a b ...
0
votes
1
answer
124
views
How to find difference of cells with respect to first cell, where first cells are different on two excels?
How to find difference of cells with respect to first cells, where first cells are different on two excels?
Table1
Table2
Table3
Below are the 2 excel sheets (table1 & table2) and I want to ...
0
votes
2
answers
7k
views
Cell referencing change with filter
I am quite new to Excel and just figuring out how to do things. I have a question about cell referencing between sheets.
Say I have on Sheet1 this table:
DIMENSION VALUE
X 0
Y 1
Z ...
- 103
0
votes
0
answers
2k
views
EXCEL: Can I reference text within a cell, for a file path, in a function?
I have the following VBA module in Excel which allows me to do check if a file exists in a given folder:
Function FileExists(FilePath As String) As Boolean
'Declare variables
Dim FileName As ...
- 1
3
votes
1
answer
248
views
Filtering over merged downwards cells in MS Excel-2016
Note: This question has been asked back in 2010 on this site here. And yet, I would like to post it one more time, showing what I did and what didn't work. Besides, Excel has changed quite a bit since ...
- 131
1
vote
2
answers
2k
views
Formulas `A1:A` and `A1:Z` give invalid name error in Excel?
Selecting a range of definite size (e.g. A1:A2) works just fine, but selecting a range to the last row does not. If relevant, I am using Office 365.
- 11
1
vote
3
answers
1k
views
Extracting Text Between two Characters in Excel
I have an xls collumn with the following template:
Model : QE85Q80T,Diagonala cm / inch : 216 cm / 85 inch,Smart TV : Da,Format : Ultra HD 4K,Tip ecran : QLED,Rezolutie (pixeli) : 3840 x 2160,Tuner ...
- 31
1
vote
0
answers
304
views
What does ' '!$A$1 means in Excel?
While looking for a dynamic tournament-schedule in Excel, I stumbled upon this formula:
=IF(' '!$A$1=0;D7; ...)
Usually, I use the "!"-Operator when referring to another worksheet.
What ...
- 11
0
votes
2
answers
584
views
Excel VBA-How to clear values of rows but keep formulas in a row when certain condition meets?
Lets say I have 10 groups of data sets.
I want to compare cell values of "E" and "G" for group 1 data set. If "E"cell value="G" cell value, I want to clear ...
- 5
1
vote
0
answers
42
views
How best to find next/last meeting across multiple Excel sheets?
I'm pretty rusty on my excel and have been asked to help someone with a slightly tricky problem.
We have a number of projects going on where we are meeting the same companies for different reasons. To ...
- 11
1
vote
2
answers
2k
views
SUMPRODUCT + INDIRECT reference, How to make it work if i'm adding rows?
I have a question/problem, I have an excel formula that looks something like this
=(SUMPRODUCT(((INDIRECT("'"&G9&"'!"&$A$9)=$I$6)*(INDIRECT("'"&G9&"'!"&$B$9)))))/1.16
It is ...
0
votes
1
answer
143
views
Populating Data from a Column Multiple Times in Excel
I currently have a spreadsheet that contains a repeating set of data in specific columns with one field in each row being blank, such as the following:
I also have a column of numbers I wish to have ...
0
votes
1
answer
45
views
Microsof office 365 excel formula
I am trying to create a function that will take multiple inputs (cells) and output into one cell.
Cells B9, B10, B11, and B12 all have different numerical inputs. I would like to use 4 different ...
- 3
-2
votes
2
answers
2k
views
How to extract part of filename in an excel cell? [closed]
I need to extract part of a file name in an excel cell.
The name of the file is " AOL Report Sheet 01-08-2019 ". I would like the date part to be referenced in the cell.
0
votes
0
answers
334
views
Why are array formulas not filling adjacent cells as they should?
When using formula arrays with multi-cell results, the returned array is outputted in the adjacent cells as the formular arrays (as I've experienced in the past and as documented).
Why is it that if ...
- 1
1
vote
0
answers
221
views
Excel INDEX MATCH across 2 sheets & Update data with VBA
I would like some help with the following tasks.
I will in the future add an additional sheet with 3 columns. A would be a date, B a Wo number and C would be the date which the Wo was closed by ...
2
votes
2
answers
2k
views
Highlight ONLY THE FIRST of all min/max values in a row/column in Excel
I'm trying to THE TITLE, that would be the general question; in my specific situation I need the minimum value in each row of a matrix, using conditional formatting of course.
I've only been able to ...
- 145
0
votes
1
answer
1k
views
Excel - How can I probe all the precedents of a given cell?
I'm a relatively naive excel user who's been dropped in the middle of an ocean of cells. I've been tasked with coding the value of one cell in a workbook into python. Of course, this cell has multiple ...
- 101
-2
votes
1
answer
72
views
HOW TO SEPARATELY VISIBLE A ROW CONTENT BASED ON VALUE-without VB
I have some data from 2A to 9D.
I want a separate box and need to show all details of particular row based on the age I wish.
In the example I want to filter all data of a customer with age 15.(I ...
- 1
3
votes
4
answers
21k
views
Copying Chart to Another Sheet with Updated References in Excel
I have made a chart within a sheet in Excel. Within the sheet, the chart refers to specific cells in the sheet to display (i.e. cells that contain my data points).
I would like to know how to copy ...
- 131
-1
votes
2
answers
751
views
How to add a decimal to a 5 digit number
I get a column of numbers that looks exactly like this:
4320923
What I need to do is turn the number into this:
+43.20923
How do I do this in format cell? I've been messing around and it looks ...
0
votes
1
answer
1k
views
How to add value to same cell in Excel
I frequently do stock audits in my store by scanning barcodes into Excel. I use the Vlookup() function to avoid data errors. At times, I scan the same barcode again and again, always adding just one ...
2
votes
1
answer
113
views
Formula inside conditional sum (Excel)
I have a VBA function, that check cells and returns an integer. (for example CheckThis(A5) => 23 )
How can i do something like this:
=SUMIF(A1:E1,Checkthis(N) = 23)
I tried various possible methods ...
- 83
0
votes
1
answer
80
views
Excel: Data across multiple columns and rows
Currently have a table like this:
How can I consolidate and count the hours per person spent on each day. Need to be on seperate lines due to working on different tasks but want to bring in all ...
- 1
0
votes
1
answer
160
views
Shared Excel doc with one user who's changes aren't saving for others to see
So my office uses tick sheets to catalogue tasks performed throughout the day in order to input them into the time-card/work-volume prediction system at the end of the day. A few years ago, when this ...
1
vote
2
answers
98
views
Cover a formula in drop down list with a word
I have a list in Sheet2 J3:J7 of calculated values.
I would like to insert it as a drop down list into Sheet1 B10, which is simple enough, however:
I want the list to use the words: apple (J3), ...
- 37
0
votes
3
answers
1k
views
Where’s the syntax error in my formula?
I'm trying to use this formula, but Excel keeps telling me there's an error.
=SI(NB.SI(A2;"*D*");"Data";"SI(NB.SI(A2;"*V*");"Voice";"Autres")")
(In English:
=IF(COUNTIF(A2;"*D*");"Data";"IF(COUNTIF(...
- 363
0
votes
1
answer
232
views
In search of a a random “sentence” or “line” generator based on predefined “sets” of words in excel
Not sure where I am supposed to ask this question, (if this is the wrong place, please help me locate the correct location).
I am in search of a a random "sentance" or "line" generator based on ...
- 101
1
vote
1
answer
183
views
How to get Excel to extract information for a given date without duplicates and skip empty cells?
So I have a table which contains companies that a group of people will visit on given dates. I'm trying to make a formula that will have index for the companies and match it for the given date. When ...
0
votes
1
answer
205
views
Ticket report - Auto populate from other worksheets into specific form
Hello ladies and gentlemen, I'm quite a dummy in excel and programming in general and I have a question related to building a report:
I must present a ticket report of which tickets are opened, how ...
1
vote
1
answer
499
views
Excel | How Can I Increment A Formula By 1 For A Group Of Formulas
Currently what is happening is that Im attempting to increment the formula +1.
for example here is what is in b1-b4:
Cell | Formula
b1 | =(mark!A8)& " - "& (Config!A4)
b2 | =(mark!A8)&...
- 15
0
votes
1
answer
73
views
Excel TODAY() function
I have two tables in excel
The first table is called Oranges, it shows where the oranges were produced and where they were exported to:
The second table is supposed to show the total oranges shipped ...
- 5
0
votes
1
answer
2k
views
How do I convert sql to excel syntax?
I have two sheets in an excel file, "sheet1" and "sheet2".
The first sheet has a table called Oranges, it shows where the oranges were produced and where they were exported to:
On the second sheet I ...
- 5