I have tried to use sum and match functions, but it is adding the whole range instead of the matched row. I have also added data validation using a custom formula to deter using text on a cell, but it does not seem to work.
I have tried to use sum and match functions, but it is adding the whole range instead of the matched row. I have also added data validation using a custom formula to deter using text on a cell, but it does not seem to work.
Last edited by Micfree1; 08-22-2023 at 01:36 PM.
Given you have 365, you can usecopied downFormula:Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
In B2 copied down:
=SUMPRODUCT((August!A$4:A$8=A2)*August!$D$4:$EZ$8)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
As you mention BYROW, maybeFormula:Please Login or Register to view this content.
Similarly, with Ali's solution:Formula:Please Login or Register to view this content.
Thank Ali and TMS. All solutions work.
Could you look into data validation custom formula please? I do not know why data validation formula does not work
Do you need it really???
Which column is it?
DV for A4 should be this, surely?
=ISNUMBER(A4)
Hi Ali,
Basically users will add holiday days for each day. Data should be strictly numbers only, but we know people do not adhere to it. So, data entry in each cell has to be numbers. Tab August- the same range as August!$D$4:$EZ$8
Choose the entire range, D4:EZ8. Under Data Validation, choose Decimal and pick a lower and upper limit. I guess maximum 0 to 24? It will say you have a mix of validation and ask if you want to clear it. Yes, you do.
Thanks TMS! It is a great idea. Will it take 0.5?
That's a decimal, right?
TMS, of course it is. I was looking at the whole number and never crossed my mind that i can use decimals. Thanks again!
You're welcome. Thanks for the rep.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks