Brilliant. Simple but effective solution. Thanks!
Posts by GradB


Thanks! That's great. I'm trying to understand the formula; how come numbers still change from 31 to 1 even when you have for example in E7 the formula D7+1 (it should be 32, right?). What am I missing here?

Hi, I have created a small calendar in Excel. I'd like to have the date of the current day highlighted in, say, yellow.
How could I do that? I'm attaching a file which explains it all.
Thank you.

Like this ?
(Please add your XL version to your profile. Thx)
Hi that's a novel idea that I haven't thought about either. Thanks.

Hi, that is very interesting because I have the opposite experience.
I'm attaching a screenshot with the formula which should be correct, and that returns a blank cell. See red rectangle No.1.
The only Russian I noticed was the name of the sheet which you probably gave, not me. See red rectangle No.2.
Can you help me understand what the formula should do, how it works, and that may help me come up with a fix for it? Thank you.

I'm sorry but I don't understand  there are no "@" signs in formulas you provided. There is no Russian text there either (apart from the name of the sheet).
The formula you pasted in your last reply
=IFERROR(INDEX(A2:L43;MATCH($N2&$O2;A2:A43&B2:B43;0);5);"")
returns a blank cell.Example1.xlsx
What am I doing wrong?

See "Sheet1"
Formulas are written in yellow cells.Hi
thank you for sharing your solution. I have a problem with your formula in column "P"  the moment I get into the cell and press Enter, the value turns to "N/A" and I can't get it back to show the date. See the attachment to understand what I mean.
Maybe you can unpack the formula to me so I'd know how it works, please? That would help me understand it.
Thanks again! Example1.xlsx

Hi, I'm generating a monthly report. In it, there are invoices with items listed. An invoice can have at least one item listed or multiple (2, 3, 6, even more).
I generate the report by copying the information about invoices from a database and paste it in this Excel spreadsheet (attached to this message). I then created a report that neatly organizes the information but the problem I have is that the report spreads over a few pages because there are many blank rows.
I would like to create a report that would have no blank rows. How could I do that? Any thoughts or more handson help?
Thank you and best regardsExample.xlsx

Hi Rory,
this is terrific, thank you. It works exactly as I asked for. But I don't understand the "1E+100" part  can you explain this one, please?
Thanks!

I would like to know which formula I can use to report which row is the last one that has numbers filled in. More specifically, i'd like to show the date for which the numbers are there. In the case of the attached file, the formula should give me the result 29.01.2024.
Thanks!

INDIRECT() is the simplest way to do this but it is a volatile function  it'll recalculate every time any other formula is recalculated (Like the NOW() function). It's not usually a problem but in a big workbook a lot of volatile functions could start to be a drain on resources... just something to bear in mind.
Nice to see a selfanswered Q
Thank you, noted!

I think I found the solution.
The formula that should be used in conjuction with the SUM is INDIRECT.
I'm attaching the same file, but with the correct formula.
Of course, if there's any other  more elegant way  I'm happy to hear about it!
Thanks.

Hi,
I'd like to create a formula that would be generated based on the input in two other cells.
The input in those two cells would represent the cells (location) where the formula should start and where it should end.
For example, I'd like to create a SUM formula that would take input from such two cells as start and end of calculation.
I'm attaching an example.
Thank you and best regards.

Hi Carim,
thank you, this is exactly what I wanted to achieve. So, it's AND and not IF! See? That's why I come to this forum.
So, what happened? Is it going to happen again? Is there any other place to go in case this website implodes?
Thank you again!

Hi,
first of all, I'm glad this forum is back (or was it just me?)  I could not access the site for about 2 weeks and felt really bad for losing such a great resource.
Anyway, I'm glad this works again.
A question: I have a list of dates which I'd like to highlight with conditional formatting. I'd like to highlight a date that is within two days (plus or minus) from the current/present day.
I tried using "IF" statement in the formula part of Conditional Formatting but somehow I couldn't get it to work.
I'm attaching a file with more explanation. I hope someone like Carim can help again...
Thanks and best regards.

Hi Carim,
thank you for the formula and the file as well!
I'll have a look and see how this RANK and COUNTIF work together. Am a bit slow in the head.
I'm Ok with having extra columns with formulas in the sheet, so no need for invoking Visual Basic.
Thank you!

Hi Carim,
yes, you're right. If they'd be tied, I'd use some other metric to position one or the other ahead, but for this exercise (as I can then pick it up from there myself) I'd just go with the alphabetical names. Since I replaced their names with numbers (like "Mr.3" instead of "John"), let use this rule in case of a tie: the smaller the number, the better the position. So, in case of the example, it would be Mr. 3 before Mr. 10.
I hope it makes sense to you.
Thanks.
Hi,
Whenever you are facing tied scores, you do need to establish a specific rule in order to deal with tie breaking ...

Hi Carim,
I have an additional question. How can I also identify another member that has the same (one of Top5) performance spots?
Please, see the attached file. In it, "Mr.3" with 16 consecutive submissions is in the 3rd place, but so should be "Mr. 10". Is there a way to capture that, too?
Thanks.

Thank you very much. I knew it must be the Index formula, it's just I couldn't put it together myself. This way, I can learn for the future.
Much appreciated!

Hi,
I have a list of numbers entered in rows.
Each row denotes one measurement of compliance.
For anonymity, I removed the names of 23 team members in the top row and replaced them with numbers from 1 to 23.
I'm struggling with the following: I'd like to set up a leader board and for that I would need to know (at least for the last row in this list) who the top 5 performers are. Performance is the number in the row, e.g. in the attached example sheet, "Mr.19" is the best performer in this last measurement because he's got the highest score (57), with "Mr.22" being second with 40 consecutive submissions.
Please, can you help me with a formula that would identify the top 5 performers, say, in columns Z to AE. There could be a tie in one of the top 5 places, so I added a column for the 6th placed.
Thank you