Hi everyone!
I'm looking for a formula that would return a "$" sign IF there's any values for any categories (for two tabs) from january 1 to january 31 2020. IF there is no values from that period, return a "@" sign in cell D9 (tab "Projection"). I've attached a file for the details. If you have any questions that is not clear on the file, feel free to let me know.
Like always, thanks for the help, always appreciated!
How is this different than your other thread with this question and a similar (or same) file?
Hi 6StringJazzer!
The only difference is that I've added a new tab but what I look for is the same. As soon as I uploaded my file for a second time in my previous thread, I didn't get any feedbacks so I thought that maybe I did a wrong operation. I've tried to erase my previous thread but didn't find how to do it (is it possible?) and start a new one so that people see it. As you answered me, that tells me that there's nothing wrong in my previous one? Am I correct?
Hi:
=IF(OR(SUMPRODUCT(--(TEXT(Paiements!$A$7:$A$20,"mm-yyyy")=TEXT("1/1/21","mm-yyyy")))>0,SUMPRODUCT(--(TEXT(Prêt!$A$8:$A$20,"mm-yyyy")=TEXT("1/1/21","mm-yyyy")))>0),"$","@")
Note 1. As requested, done for January 2021. I couldn't see a cell containing 01/01/21... so I hard-coded it into the formula.
Note 2. There are NO dates in January 2021 (all 2020) in the Paiements and Pret sheets.
You may need to use ; instead of ,
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Your file attached this time!!
I have closed your parallel thread, as it hadn't received any substantive replies so far.
Hi Glenn!
Thanks for the feedback! There was maybe a confusion with the title of my file (2021) but I mentionned the year 2020 in my query/issue. But I was wondering if the formula would be the same (except to change the year) or the formula would be shorter and simpler? In my case, I use the ";" to compose the formula.
I'm trying to make it as clear as possible but I still have some work to do I guess
P.S. I understand regarding the rule for duplication but I thought this time that there maybe was a problem with my original thread since there was no feedbacks after I've attached my file.
1. For 2020, just change the date to 2020 (bits in red). I didn't notice the comment about 2020 in your description. It would probably be better to have the date in a row somewhere, as then it would be easier to copy across... but I couldn't see it. The formula will otherwise be the same.
So, does it do what you wanted?
I wasn't sure about French Canadian keyborards and formatting. Out of interest are your keyboards QWERTY or ASERTY?
If ta thread hasn't received any substantive answers in 24 hr... bumping it is FAR preferable to duplication... and keeps within the guidleines, too.
1. That's what I thought (regarding the year date). No problem! For the date period for the formula, they are mentionned in two rows (tab "Projection" cells D20 for january-1-2020 and D21 for January-31-2020) but I configured the date format as "DD-MMMM-YYYY".
It's a QWERTY keyboard.
Sounds good and how do we BUMP a thread?
You cannot delete a post, or a thread. A Moderator can do it but we generally do not delete posts unless it is spam or a gross rule violation (like abusive language). We have a rule against duplicate threads, which is why I asked about this. Glenn closed your first thread so that is no longer an issue.Originally Posted by Goodvibe
As a relatively new member, please take the time to review our rules. There aren't many, and they are all important.
You can bump a thread by adding another post to it. However, we discourage this unless a thread has gone at least 24 hours with no response.
1. No... there are January 2021 dates in B16/B17 of Projection and NO dates in the cells you mentioned..... unless my eyes are playing tricks on me
QWERTY: thanks!
bumping. Just make a quick reply to your own thread saying something like "bump".
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
6StringJazzer:
To be honest, somebody told me regarding the duplication rule not too long ago but like I said earlier, I thought there was a problem with my thread. Since we can't delete our post. Now I know that there is the BUMPING post option. Is there a concrete example somewhere to have an idea how to do it? Sorry again for the inconvenience or babysitting that it cause lol :P. Thanks for letting me know how the forum works!
bump
(Bumping SOLELY for the information of Goodvibe).
(in effect this has bumped your thread back to the top of the pack).
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
@Glenn:
Sorry for the delay! Something came up. I've translated your formula (have to change names of each functions for the french version and replace the "," for ";"). It works for January but if I'm not mistaking, since the dates in the formula are not referenced by a cell, I cannot copy the formula for the following months, am I correct?
My mistake by the way regarding the referenced cells, I told you they were in tab "Projection cells D20 and D21 (that's in my original file) but it's in "Projection" D16 and D17. When I copied the formula for the following months, it's still January that is in the formula (now I know I should mention that I will copy down the formula for the following months, rookie mistake :P). Since my date format is in french (dd-mmmm-yyyy) maybe you won't see the dates shown on your side (if that's the case, could you just show me the adjusted formula with cell D16 as the JANUARY-1-2020 and cell D17 as the JANUARY-31-2020 if you don't mind?
Thanks for your patience Glenn and for your help
Last edited by Goodvibe; 02-02-2021 at 03:28 PM.
Glenn:
Finally solved the issue. I didn't use your exact formula because of the date period that I couldn't copy for the following months (next time I will mention the cell reference for the date period
The formula was this one:
=IF(OR(COUNTIFS(Paiements!$A$7:$A$10;">="&Projection!E$16;Paiements!$A$7:$A$10;"<="&Projection!E$17)>0;COUNTIFS(Prêt!$A$8:$A$15;">="&Projection!E$16;Prêt!$A$8:$A$15;"<="&Projection!E$17)>0);"$";"@")
I've updated the file with the formula in tab name "Projection" cell D9 if it can help someone who has this type of issue (not sure if the functions will be translated on your side though since my Excel is configurated in french)
Thanks again for helping me out Glenn! I appreciate it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks