All Questions
51
questions with no upvoted or accepted answers
3
votes
1
answer
783
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
947
views
Turn cell references in Excel hyperlinks into the text in the referenced cells
I have a column of bare URLs in Excel. Say it's Column A. I want to replace each URL with a friendly text link, like "here." Suppose A1 contains the link https://www.website.com/content/...
1
vote
1
answer
82
views
Custom Information Message for user based on another cell value
I am busy with an optimization spreadsheet which needs to display an information message, maybe similar to the Input Message for data validation (but I am open to suggestions). This message will be ...
1
vote
1
answer
732
views
How to fit a polynomial trendline such that only the leading two coefficients are non-zero in Excel?
I have data on the displacement of an object over time and a cubic trendline fits it very well.
I know that the initial displacement is 0 so I can tick the "set intercept" box.
However, I also know ...
1
vote
0
answers
250
views
What is the VBA equivalent of using an array formula to calculate the max non-blank value of a range?
I have the following array formula in Excel: {=MAX((C9:C44<>"")*(ROW(C9:C44)))} that calculates and returns the row number of the last non-blank cell in a range.
I could reference the result in ...
1
vote
1
answer
83
views
Open a spreadsheet with broken formulas
I am using code the generate a spreadsheet, including a formula for a cell on certain rows. When I do this, there is something wrong with the formula, and when I try and open the spreadsheet, Excel ...
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 ...
1
vote
0
answers
218
views
In Numbers/Excel how can I do a SUMPRODUCT only on rows matching a specific criteria
Let me explain my spreadsheet a little bit. Here is a good screenshot of it:
I have created this spreadsheet essentially because I have gotten tired of the usability issues of just about every ...
1
vote
0
answers
116
views
In Excel, is there a way to autofil formulas that link to other files with information from other cells in the current spreadsheet?
I have a master spreadsheet that collates data from other spreadsheets, and shows this via linked cells - for example, I would have ten identically set out spreadsheets, each showing a different total ...
1
vote
0
answers
246
views
Dynamic timeline spreadsheet with selective stickiness?
I'm trying to create my first spreadsheet ever, but there's one function that I'm finding very difficult to find guidance for from the internet, just like the title of this post felt a bit clumsy to ...
1
vote
0
answers
90
views
Excel: Conditional formatting, simplifying retrieving data from a range of rows
I am trying to create a schedule for a limited number of trainings (around 50) that are distributed between a limited number of venues (around 15), and I am looking for a smarter approach than the one ...
1
vote
1
answer
739
views
Find highest number of consecutive outcomes for each person in list
I've got an Excel list that looks like this (with the name in column A and the score in column B):
N. Nam Score
1. Jim 7
2. Jim 4
3. Jim 4
4. Jim 7
5. Jim 7
6. Jim ...
1
vote
1
answer
92
views
Can I (and If so How?) use the MATCH function to search two (or more) different spreadsheets for a value?
First I'll explain what I already have (which works), then explain my question, which is about expanding it:
I have two different spreadsheets that will be worked on at the same time, I needed to see ...
1
vote
2
answers
11k
views
How can I pull specific rows in Excel into a new spreadsheet
I have several spreadsheets containing data I need to analyse. I have a randomized list of numbers that dictate which rows in each spreadsheet I need to code for my data sample.
Is there a formula I ...
0
votes
0
answers
41
views
Excel SUM function and + operator different for blank text
The + operator in Excel gives an error when adding two cells whose values are empty text, while the SUM function treats these cells as zero (as I expected). I am trying to understand if this is the ...