how to use my dropdown list

[moshelog]

hello ,

i have dropdown list witch is "date" in cell A1
in cell C1 i put number witch is "money"

d1:d100 i have "dates"
e1:e100 should get the data from c1

so i use in e1:e100
=IF(a$1=d1,c$1,0)
=IF(a$2=d2,c$1,0)
etc ...

my problem is that i can use it only for 1 date
i want to choose date from my dropdown list put money and then choose another date and put money

sorry about my english .
and thx
moshelog

[jeffreybrown]

I cannot visulize your data setup. Can you post a small sample workbook of what you have and what you expect?

[moshelog]

I cannot visulize your data setup. Can you post a small sample workbook of what you have and what you expect?

I'm not near my pc now but I'll do it when I'm back, in the meantime ill
Try to explain

I have calender drop down list
So I need it to be able to choose date from that drop down list and then write
how much money u spent on that day

1.1.2012 200$
1.2.2012 26$
......
...

Thx in advance
Moshelog

[jeffreybrown]

To me this sounds like a simple vlookup.

=VLOOKUP(A1,$D$1:$E$14,2,0)

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Check A1 for a value, checks it against the table D1:E14, returns the match from column 2, finally a zero for an exact match

Please Login or Register  to view this content.

[Herman Merman]

Sounds like you need a sumif function to return the total value of money spent on the particular day you select from your drop-down list.

So what I think you are trying to say is that you have two columns of source data, one for the date and one for the money spent on that date. You have another drop down list (presumably a data validation list) that you use to select a particular date and then you need a formula that will return the total value of spend on that particular date.

Is this right?

[Herman Merman]

Date Sumif.jpg
Screen-shot attached.

[moshelog]

22.jpg
check it out
i3 = my drop down list
g3 = money
c16:c100 = get the money from g3
Each time I choose date from i3
G3 value should be added to C according to the right date

[jeffreybrown]

So what happens when you select 02/01/2012 from I3 and how does G3 gets its value?

Also, posting a picture does not help when trying to test for the right solution.

[moshelog]

when u select 2/1/2012
c17 get 60 and c16 get 0

about g3 - user puts there the value

i3 = date
g3 = user input
c16-c100 = =IF(I$3=B16,G$3,0)
=IF(I$3=B17,G$3,0)
.....
...

[jeffreybrown]

It seems what you have already should work...

Put this is C16 and drag down =IF($I$3=B16,$G$3,0)

Basically I hear you saying, starting in C16 and down, match the date from I3 to and date in C16 and down, when match is found, retrieve amount for G3.

Your formula would do this.

I'm not sure what I am missing if anything.

[moshelog]

It will work only for 1 date and will zero the previous
For example i3 = 1.1.2012
G3 = 60
The result c16 will be 60

Now I'm going to change i3 to 3.1.2012 , g3 = 60
The result c18 will be 60
And c16 will back to 0 ( that part is my problem)

[jeffreybrown]

Now I'm going to change i3 to 3.1.2012 , g3 = 60
The result c18 will be 60 And c16 will back to 0 ( that part is my problem)

Sorry still not seeing your probelm. You say when you change the date, everything but the date that matches will be zero. The data that matches will equal G3.

Please post a sample workbook with two examples. One with the date 1.1.2012 selected and then another page with what the sheet will look when you select 3.1.2012

At this time I can't really duplicate your concern for two reasons.
1) I'm working with a jpg, not actually data
2) Your text explanation is not helping me visualize the problem

[Fotis1991]

Hi

I strongly believe that if you had upload a small sample workbook, you would have your solution now.

[moshelog]

ok here is my files , 2 files - new picture and xls file
, i hope it will help 333.jpg
http://www.2shared.com/document/L0b1Pmbk/33_online.html

All I want is to keep the dates that i received money from people
, and on each date how much money

for example if i got money on
1/1/2012 50$
5/1/2012 30$
10/1/2012 100$
2/2/2012 10$

then the result i would like to get in my excel file is
c16 = 50
c20 = 30
c25 = 100
c48 = 10

i really hope i explain that well now

thx for all ur help guys
moshelog

[jeffreybrown]

Please load your sample file to the forum here by way of the paperclip or manage attachments.

[moshelog]

ok file included now

[jeffreybrown]

Right click on the sheet tab >> view code

Paste the code below to the right side of the screen

You cannot do this with a formula or at least a formula that stays active. You have to paste as values which this macro does.

Please Login or Register  to view this content.

[moshelog]

thx alot problem solved
second time ill upload file from the first moment .

THX

[jeffreybrown]

Not to say it is needed, but for some people (okay maybe just me ), I am very visual and the workbook helps me tremendously. Plus, we see your actual requirements.

Glad we could finally get it figured out. Thank you for your patience

[moshelog]

jeffrey if i want this code global to effect all my sheet ?

i have more then 1 sheet
and they are all smiliar

and also any option to lock date(i4) until user put amount(g4) each time?

[jeffreybrown]

Try this in the ThisWorkbook module

The second part of the macro will lock I4 if nothing has been entered in G4 along with taking away the data validation of I4. In order for the cell to truly be locked if G4 is empty, you must password protect the sheets.

Please Login or Register  to view this content.

[moshelog]

works great ,
the only problem g4 will stay on the last amount u choose unless u change it manual, to fix it
i need g4 get 0 each time u insert date

thx again

[moshelog]

ok fixed :D

i learned something :>


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("G4, I4")
Dim found As Range
Set found = Columns("B").Find(what:=Target, LookIn:=xlValues, LookAt:=xlWhole)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Not found Is Nothing Then
Range("C" & found.Row).Value = Range("I4")
Range("i4").Value = 0
End If
If Not Intersect(Target, Range("I4")) Is Nothing Then
With Range("G4")
If Range("I4").Value = 0 Then
.Locked = True
.Validation.InCellDropdown = False
Else
.Locked = False
.Validation.InCellDropdown = True
End If
End With
End If
End Sub