Data Validation

[muhammadali]

hello all
this is my first post in any excel forum.hopefully i am posting in the right category.
my sheet has two tables (table1,table2)
i have set a data validation list that is in green if i change the value in the list the formula should return value as per criteria.
for e.g
ammar pays instalment of 200,000 per month now if i change the green box to quarterly then should return date of quarter ending and total amount of quarter,same with semi-annual and annual.for reference sample is attached.

thanks for the help

 

[Khalid NGO]

this is my first post in any excel forum.hopefully i am posting in the right category.

Hi Ali, welcome to the forum .

Please start a new thread for your query.

Go to the following link and click on Post New Thread button at top right, set a meaningful Title and post a sample file with your expected results:
http://chandoo.org/forum/forums/ask-an-excel-question/

But first I suggest you to start from here:
http://chandoo.org/forum/threads/new-users-please-read.294/

Regards,

 

[Luke M]

Edit: Moved these posts to a new thread.
------------------
Hi muhammadali, and welcome to the forum!

As to the question, can you clarify which cell(s) you want us to look at? You stated two tables, but there's actually 3 different areas with data, and there are no XL Tables. I'm not sure where to begin looking, which cells are the inputs, and which are the outputs.

 

[muhammadali]

thanks for the reply luke
if you go to name manager you will find both tables where as the one at bottom is the place where i need values in these are the columns
Date :the formula should return value in this cell according to the value set in cell G18
Amount:same as bove

e.g
consider these values
name ABC
date:1-jan-2015,1-feb-2015,1-march-2015
amounts:150,000 respectively
i consider G18 is set on quarterly then from then it should return
date:march(Quarterly)
amount(450,000)

hope this will help

thanks

 

[trprasad78]

Hi Muhammadali,

i need few clarification,

Which cell you change ? G19 ?
Where out put needs to change ?
What needs to be change

can you please mention the cell name as per your file.

Regards,
Prasad

 

[muhammadali]

hello trprasad

if i change the value in cell G18 it should reflect changes in C22 and E22
e.g
if G18 is changed to quarterly
then C22 should return the month of quarter
and E22 should return sum of three months
to return monthly values i am using Vlookup which uses table2 but so far i am unable to get what i explained above

hopefully this clarification is enough

thanks

 

[trprasad78]

I used sumif() function to calculate quarterly, also i added one column to define Q1,Q2,Q3.

please check it may help useful.

 

[muhammadali]

hello trprasad

you solution is partial helpfull. it is returning the right month in c22 but the amount it returned is not correct. i need the sum of 3 months plus you have not accounted for semi-annual or annual cases?


i appreciate you help