• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Happy-o-Meter

B

bushvilla

New Member
Hello,

I have a spreadsheet which I did get free from the web and want to use this for my place of work. I'm having trouble with the formula in my lookup value on 'My database' tab which links with other tabs in the SS. I'm not very good with formulas and wondered if I could get some help?

Basically the widgets should show a dial numbering between 1-3 depending on morale and I want to be able to use the drop-downs to show month and the week of each month so people know how the team are feeling.

I have attached the spreadsheet and would be greatful for some advice?
 

Attachments

You could just use VLOOKUP for date & AVERAGEIF/SUMIF & * for Month instead of OFFSET
 
Last edited:
BushVilla

There is more deep seated problems in the file than just the formula. The Active X drop down is looking at the My DropDown Menus Page and these so called Dates are not dates at all but text. The information in the My Database Page Column C on the other hand is dates. This won't work as you are pushing text to C4 of the My Database page and expecting a lookup to return a value on non matching data types.

So what you need to do is address this problem then your file should start to return a result. I will have a crack at getting your data types so they are like.

When you do fix it this is the formula for C8 of the My Database page

=VLOOKUP(C4,$C$13:$D$55,2,0)

Test it copy C13 of the My Database Page into C4. Data types are like so result is assured.

Now for an average formula for C10.


Take care

Smallman
 
Hi @bushvilla

In Sheet "My Database Page", In C8, you can use formula as..
=INDEX($D$13:$D$55,INDEX(MATCH(C6&DATEVALUE(C4),B13:B55&C13:C55,0),,))

and in C10, use formula as..
=INDEX($E$13:$E$55,INDEX(MATCH(C6&DATEVALUE(C4),B13:B55&C13:C55,0),,))

PS.. If you have already grip on ARRAY formula, it can be shorten :)
 
Hi Debraj,

I pasted the below as you mentioned and Lookup Value 2 (C10) is working great but Lookup value 1 (C8) is saying 'False'...any ideas as to why?

In Sheet "My Database Page", In C8, you can use formula as..
=INDEX($D$13:$D$55,INDEX(MATCH(C6&DATEVALUE(C4),B13:B55&C13:C55,0),,))

and in C10, use formula as..
=INDEX($E$13:$E$55,INDEX(MATCH(C6&DATEVALUE(C4),B13:B55&C13:C55,0),,))
 
You must log in or register to reply here.
Back
Top