• 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.

Dates

G

guitarman

Member
Hi there NARAYANK991


Its me again just to let you know everything is working fine on my sorting trophies and

members that you sorted for me. Except one thing the date when i put the info in the spreadsheet it does not pass the date into the master copy and I don't want to have to filter through it to insert it everything else is inserted Trophy Member etc etc except the date any advise please.

mike
 
Hi Mike ,


Sorry , but it's almost bedtime here in India. I just saw your question , and am not clear on the details.


In your original post , you mentioned that you had the year in which the trophy was won , the member's name and the trophy that was won by that member. Is this date an addition ?


Can you please give more details , and if possible , some data like you did in your earlier question ?


Narayan
 
Hi Narayan


Members----------Trophies-------Date

A,B,C,D,E----------F------------25/2/12

this the spreadsheet I enter onto and then that changes the master workbook. But

unfortunately the date does not alter so I assume that I have done something wrong there

but what I need is a formula to insert the date in the master worksheet when I print it

on my spreadsheet. So I would have to put the formula in the date column on my master

worksheet and then it would alter when I insert it in my spreadsheet.

Mike
 
Hi Mike ,


Things are still confusing ; you talk of a spreadsheet and a master workbook ; what is the document where you have the raw data viz. the members' IDs / names / numbers , the trophies each won , and the date / year in which it was won ? Is this what you call a spreadsheet ? Or is it a master workbook ?


As long as the data is available , I don't think there should be any problem in incorporating it wherever you want to.


Narayan
 
Hi Narayan


The master copy is the original one where all the data is members,trophies,winners etc etc

the spreadsheet is where I quickly put in the winners etc to save me scrolling through

the massive list everything goes in except the date. I want to be able to put the date in

with a coloured cell for each month so I was thinking there must be a formula for doing this to save me from actually finding each individual and putting in the date and cell colour is that clear to you? any ideas.

mike
 
Hi Mike ,


Without the data , I am not able to visualise the problem.


Is it possible you can upload your workbook and post the access link here , or mail it to me at narayank1026@gmail.com ?


Narayan
 
Hi Narayan


There is no problem with the data at all. It is just the date column it is not entering the date. What I need is the formula to enter into the date column on the master copy

which is in another Workbook so in reality this column will acept the date I have printed on my spreadsheet like it does with all the other data. It is something amiss in the master copy date column. So is there a formula that I can enter into this date column so when I enter the date on my spreadsheet it automatically inserts itself in the master copy

without me having to go into the master copy and scrolling around the column to enter it

Mike
 
Hi Mike ,


I did not mean to say there is any problem with your data ; what I meant was that without the workbook in front of me , I am unable to visualise the problem.


Anyway , let me take a stab at it :


1. I assume you have two sheets , let us call them SheetA ( raw data ) and SheetB ( master copy )


2. Let us assume the raw data is in 3 columns , A , B and C , with the dates in column C.


3. Let us assume that SheetB is summarising the data from SheetA ; so what is required is that the dates from SheetA appear in SheetB in the relevant places e.g. say cell A5 on SheetB contains the member's name , B5 contains the trophy this member won ; you want that C5 should contain the date on which this member won this trophy.


This is done by matching the data in A5 and B5 with the raw data ranges in SheetA ; let us assume this data is in the range A10:A375 , B10:B375 and C10:C375. The formula to be entered in C5 , in SheetB , will be :


=INDEX(SheetA!$C$10:$C$375,MATCH(A5&B5,SheetA!$A$10:$A$375&SheetA!$B$10:$B$375,0))


Is this what you are looking for ?


Narayan
 
Hi NARAYAN


I have tried that formula and it gives me #N/A.Do you think it would be better if I just asked it to match just the DATE and not the rest of the info. The date is in (H) on sheet A and also on sheet (B). So could I say something like Match sheet A H2:H120 and then it would put all the dates in sheet B. What do you think?

Mike
 
Hi Mike ,


You can afford to use a MATCH on only the dates , if you are sure that all the dates are unique ( there are no duplicates , as otherwise MATCH will always return the first matched entry ).


I forgot to mention that the formula is an array formula , to be entered using CTRL SHIFT ENTER ; however , that is not the reason you are getting a #N/A result ; this means there is no match.


Can you verify whether a match is available or not ?


Try the following :


1. In a blank workbook , rename two sheet tabs to SheetA and SheetB.


2. On SheetB , in cell C5 , put in the above formula i.e. :


=INDEX(SheetA!$C$10:$C$375,MATCH(A5&B5,SheetA!$A$10:$A$375&SheetA!$B$10:$B$375,0))


using CTRL SHIFT ENTER.


3. Assuming the whole of SheetA is blank and cells A5 and B5 are also blank , you should get 0 in C5 ( SheetB ).


4. Enter any member name / number and any trophy name / number in A5 and B5 ; you should have #N/A in C5 ( SheetB ).


5. Put in the same values in any cells in column A and column B ( the cells should be on the same row ) on SheetA ; you should get 0 in C5 ( SheetB )


6. Put in any data in the corresponding cell in column C in SheetA ; C5 on SheetB should have the same data.


Narayan
 
Hi Narayan


Sorry I have took a long time replying been out on business.I have tried this formula

and it just returns #N/A and I followed your instructions exactly as you said but to no avail. All I really want is just the date which is in column (H) on both my spreadsheet and my master copy. So when I put the date in on my spreadsheet I just want it to appear on my master copy. So it has to be something like ????(H$2:H$120) On my master copy. And on my spreadsheet I write in the date i.e.28/02/2012 and it should appear on my master copy in column (H) the same in the appropiate row. and all the dates are unique

Any ideas?

Mike
 
Hi Mike ,


I can't think of anything ! If you can send me the file , I can return it within an hour , but this dialogue is not leading us anywhere.


Of course , it is now late at night here in India ; even if you send me the file , I can respond only tomorrow morning.


My email ID is narayank1026[at]gmail[dot]com ; however , if you can upload your file and post the link here , anyone else may also be able to help you out.


Narayan
 
Hi Narayan


I have discovered the problem it is with my original formula it does not include the date.I don't know how I missed it but I did. This is my original formula that works fine for all the Trophies Members etc

=SUMPRODUCT(($A$2:$E$1250=$I3)*($G$2:$G$1250=J$2)) But as you can see there is no facility for the date which is in column (H) so that should go $H$2:$H$1250 so where do I enter it in that formula.

Mike
 
Hi Mike ,


The formula will change , since what we need to be displayed is the matching date , and not a sum / count. Going by the addresses you have given , try the following :


=INDEX($H$2:$H$1250,MATCH($I3&J$2,$A$2:$A$1250&$G$2:$G$1250,0))


entered as an array formula , using CTRL SHIFT ENTER.


The only problem with this is if a member has won the same trophy twice ; in this case , the above formula will return the first year in which he / she won it.


Narayan
 
Hi Mike ,


Sorry about my earlier post. You can use the same SUMPRODUCT formula as follows :


=SUMPRODUCT(($A$2:$E$1250=$I3)*($G$2:$G$1250=J$2)*($H$2:$H$1250))


The same caveat given in my earlier post ( regarding a member winning the same trophy twice ) , will still apply. But in this case , because you are using SUMPRODUCT , the years will be summed up to give an invalid result.


Changing the formula to :


=LARGE((($A$2:$A$13=$G14)*($C$2:$C$13=L$2))*($B$2:$B$13),1)


entered as an array formula ( using CTRL SHIFT ENTER ) will always give the latest year in which the trophy was won.


Narayan
 
Hi Narayan


I have put that formula in and it works but the date it gives is 00/01/1900

it must be right because Excel encloses the formula with {formula} anyway i will try to work it out somehow.

Mike
 
Hi Mike ,


A value of 0 , in a cell which is formatted for dates , will display as 00/01/1900 ( dd/mm/yyyy ).


Wherever a member has not won a particular trophy , SUMPRODUCT will return 0 , which will result in the above display. Check out a cell where there was a non-zero value earlier , with the earlier SUMPRODUCT formula ; with the revised SUMPRODUCT or LARGE formula , you should now get a date in that cell.


Narayan
 
Hi Narayan


Well I have done that with this formula

=SUMPRODUCT(($A$2:$E$1250=$I3)*($G$2:$G$1250=J$2)*($H$2:$H$1250)) and all I get is

Sat,Jan 00,1900 except the 6th one were it displays Wed,Feb 00 1900 I am puzzled why it displays a date which is miles out. And yet the {} enclose the formula saying it is right but it obviously is not. I am getting a bit dispondent about this and feel that i will just have to go into the master copy everytime and insert the date manually. This Excel programme is not very easy to learn for novices such as myself and why they make it so complicated I just don't know

Mike
 
Hi ,


Just a thought - why are you using $A$2:$E$1250 in your formula ? Why not $A$2:$A$1250 ? Offhand I cannot say whether it will make a difference or not , but can you try changing it ? The way SUMPRODUCT works , a range like A2:E1250 will be resolved as a single column list with 5 times the number of elements , and I think it will go row-wise , so this may be a problem.


Narayan
 
Hi


Well I have done that and we have moved up on dates it now displays Mon,Sep,25,1944 so if I keep fiddling with it I might get into the 21st century. Anyway lets not worry about it Narayan I will just enter the date manually it seems the easier solution don't you think

Mike
 
Hi there Narayan


Just to let you know I have managed to sort out the date problem and now everything is working fine. Thanks a million for all your help and assistance greatly appreciated.

Have a Good Day and A Brilliant Life.

Mike
 
You must log in or register to reply here.
Back
Top