+ Reply to Thread
Results 1 to 66 of 66

Excel 2007 : I need a formula which : When dates match transfer the data from the entire row

  1. #1
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    I need a formula which : When dates match transfer the data from the entire row

    Hi guys,

    There is a workbook with 2 worksheets.
    The first one it's called "Monthly Data" has monthly duration and it's going to be auto-filled with everyday data.
    Dates in first worksheet changes automatically every month. Which means, when a month comes to its end,
    the worksheet will start the next day the new month.

    The second one it's called "Annual Data" has annual duration and the plan is to be auto-filled as well, from the worksheet "Monthly Data".
    Dates here don't change every month.

    What I need is a formula which : when dates match transfers the entire row data from "Monthly Data" to "Annual Data".
    In the attached file you can find the workbook with the 2 worksheets and 3 named ranges ready.
    What's missing is the Excel mastermind to put the appropriate link (formula)

    Thanks in advance

    Spiros
    Attached Files Attached Files
    Last edited by spiros63; 06-04-2012 at 09:20 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Do you mean in D3:

    =IFERROR(INDEX('Monthly Data'!D$3:D$26,MATCH($C3,MonthlyDates,0)),"")

    copied down and across the matrix.

    Note: If you name the data range in the Monthly Data sheet, D3:O26 as Data, then you can use formula:

    =IFERROR(INDEX(INDEX(Data,0,COLUMNS($D$3:D$3)),MATCH($C3,MonthlyDates,0)),"")
    Last edited by NBVC; 06-04-2012 at 09:30 AM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    Do you mean in D3:

    =IFERROR(INDEX('Monthly Data'!D$3:D$26,MATCH($C3,MonthlyDates,0)),"")

    copied down and across the matrix.

    Note: If you name the data range in the Monthly Data sheet, D3:O26 as Data, then you can use formula:

    =IFERROR(INDEX(INDEX(Data,0,COLUMNS($D$3:D$3)),MATCH($C3,MonthlyDates,0)),"")

    Oops !!!

    I didn't see it coming.

    The solution that you gave me is excellent, it works perfectly and the DATA suggestion is wonderfull !!! BUT
    What I didn't thought is that, when the next month starts in "Monthly Data" the previous month transfered data in "Annual Data" are erased.

    My dear mastermind, is there a solution to save the previous data from deletion ?

    Spiros, a penitent member


  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Not with a formula, unless the data remains in the monthly data sheet... you will need VBA if you want to "keep" old data... VBA is not in my scope here. If you still want to pursue it, then hopefully a VBA expert can help.

  5. #5
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    Not with a formula, unless the data remains in the monthly data sheet... you will need VBA if you want to "keep" old data... VBA is not in my scope here. If you still want to pursue it, then hopefully a VBA expert can help.
    Nope !
    VBA is something that I don't understand, thus I can't handle it.
    I'll keep your initial solution which more than enough for this stage
    and I'll try to overcome the difficulty with simplier (non-auto) ways.

    Thanks any way

  6. #6
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    Not with a formula, unless the data remains in the monthly data sheet... you will need VBA if you want to "keep" old data... VBA is not in my scope here. If you still want to pursue it, then hopefully a VBA expert can help.

    Dear NBVC

    My less-auto approach is to create 12 sheets, (1 for every month) for the "Monthly Data".
    Which would be the formula to send the data to "Annual Data" ?
    Keep in mind that, now the auto-date is in "Annual Data" sheet (this sheet now works dynamicaly)
    which means, the first month is the current month (in this case June).

    I tried to do it by myself, but zip!
    I created 3 sheets (June, July, August) and 6 named ranges (data1, data2, data3, and monthlydata1, monthlydata2, monthlydata3).
    My (toooooo many) attempts to combine then in a formula ended up in a perfect zero.
    In the new attached file you'll find the said modifications.

    Would be kind enough to give another try to a poor and humble member ?
    Attached Files Attached Files

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    If you name each table from column C to column O in each sheet something like: JunData, JulData,AugData, etc...

    Then you can use this formula, which indirectly looks at the appropriate table names based on the month in column C of your summary sheet:

    so in D3 of Annual Data:

    Please Login or Register  to view this content.
    copied down and across.
    Attached Files Attached Files

  8. #8
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    If you name each table from column C to column O in each sheet something like: JunData, JulData,AugData, etc...

    Then you can use this formula, which indirectly looks at the appropriate table names based on the month in column C of your summary sheet:

    so in D3 of Annual Data:

    Please Login or Register  to view this content.
    copied down and across.

    I don't know what's going on, but in the summary sheet nothing appears.
    I dowload your file (in order to be sure that I didn't made a mistake) with the same results.

    Can you please check if something is wrong or missing ?

    I apologise for the inconvenience

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    In my file you don't see a formula in D3:N258?

  10. #10
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    In my file you don't see a formula in D3:N258?
    In your file, everything is in order, the formula is in place (D3:N258), the named ranges are the way they should be(JunData, JulData etc), BUT,
    in the summary sheet, where the formula is applied (D3:N258) these cells are empty.
    As far as I can understand (don't forget, we are not on the same level - I'm struggling to keep up) in these cells should appear the data from the other sheets.
    They don't.

    I'm literally confused,
    Should I expect these cells (D3:N258) to be filled by the formula or not ?



  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I need a formula which : When dates match transfer the data from the entire row

    So Spiros...In Annual Data Sheet, you wait to see a "mirror" off the particular month that you choose in C2...

    In this case, i confirm that i see an empty range..No results at all.

    I am sure that NBVC, will take a look to this and will give you, your result..
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Hmmm... that is strange...

    Attached is a picture of what I see...
    Attached Images Attached Images

  13. #13
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I need a formula which : When dates match transfer the data from the entire row

    ...Real strange.... What do you see in my file??
    Attached Files Attached Files

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    I see the results fine...

    see attached pic of your file.

    ... maybe it's a Greek thing
    Attached Images Attached Images

  15. #15
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    I see the results fine...

    see attached pic of your file.

    ... maybe it's a Greek thing
    So guys,

    what's the verdict ?


  16. #16
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by spiros63 View Post
    So guys,

    what's the verdict ?

    Is it possible the problem to be something like the comma (,) & Greek question mark ( problem of Excel ?

  17. #17
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I need a formula which : When dates match transfer the data from the entire row

    .. The verdict is that i will be be completely crazy...Is this possible??

    How can i get a picture of what we see?

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    If you open a workbook that is attached here, your Excel should automatically translate it to your settings.....

    Can you re-attach your workbook and a pic of what you see?

  19. #19
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by Fotis1991 View Post
    .. The verdict is that i will be be completely crazy...Is this possible??

    How can i get a picture of what we see?
    I' ll upload right now pictures from all the worksheet

  20. #20
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    If you open a workbook that is attached here, your Excel should automatically translate it to your settings.....

    Can you re-attach your workbook and a pic of what you see?



    This is what I see plus the workbook
    Attached Images Attached Images
    Attached Files Attached Files

  21. #21
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    it might be the text function that is causing the issue for your language...

    TEXT($C3;"mmm")

    in Greek, the "mmm" which means month abbreviation might be different.

    if you go to C2 and go to format the cell, in the Number tab, how is the format displayed for the date?

    whatever the month part is, that should replace the "mmm" in the formula. (there are 2 occurances)

    or try replacing the TEXT functions with:

    =TEXT($C3,"[$-408]mmmm")
    Last edited by NBVC; 06-05-2012 at 10:09 AM.

  22. #22
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Here is your workbook again with revision to TEXT() function.
    Attached Files Attached Files

  23. #23
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    it might be the text function that is causing the issue for your language...

    TEXT($C3;"mmm")

    in Greek, the "mmm" which means month abbreviation might be different.

    if you go to C2 and go to format the cell, in the Number tab, how is the format displayed for the date?

    whatever the month part is, that should replace the "mmm" in the formula. (there are 2 occurances)

    or try replacing the TEXT functions with:

    =TEXT($C3,"[$-408]mmmm")

    I went to C2 and changed the format from date to number. The displayed number is : 41061.00
    I also changed the text formula according to your instructions. Nothing happened.

    I don't know. It's not my lucky week.
    Nothing goes as it should.
    I'm ready to open a new post asking help for a relatively not that difficult formula.

    I better go to sleep. Nothing good is going to happen


  24. #24
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Try opening the last file I sent.

  25. #25
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    Here is your workbook again with revision to TEXT() function.
    I hadn't see your post, because I was replying to previous.
    As I described I did apply the revised TEXT function. zipppppppppppppppppppppppppppppppppppp

    I need immediately medical attention.
    Psychiatric

  26. #26
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    Try opening the last file I sent.
    I did

    S.S.D.F.


  27. #27
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    In C3, go to Format and choose Custom.. what appears in the Type field?

  28. #28
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    I am probably contributing to your head-ache. I found a small error in my last file...

    Try this attached one.

  29. #29
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Dear NBVC

    To be honest, I don't feel confortable, wasting your valuable time on a subject which clearly looks like as program handicap.
    In this forum I've found brilliant minds (you, daddylonglegs, fotis1991, abusseta and others) dedicated to helping others.
    It's a pitty to waste your time on this f*****ng subject.

    Drop it, let it go, set us free from the cursed thing

    Thanks for your time

  30. #30
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Don't worry about it.. that's why we are here... to help.

    Try my last file.

  31. #31
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I need a formula which : When dates match transfer the data from the entire row

    I confirm that i feel like an idiot...

    It's really not the first time that i work with text function...

    I change the "mmm: to "μμμ"(Months in Greek). I changed all the format in all sheets...

    I tried all your suggestions....

    I'll keep trying...

  32. #32
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    I am thinking that the named ranges might also cause an issue, since the month translation to Greek is not going to be JUN, JUL, AUG.... i.e. named range for June will have to IouvData for JunData, ΙουλData for JulData, ... etc.
    Last edited by NBVC; 06-05-2012 at 11:14 AM.

  33. #33
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    I did

    Nothing new.

    It is allowed to open new post (different subject) while the previous post is still open ?

  34. #34
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    See my last post above..... i.e. changing named ranges...

    Also, if you want to keep English, maybe you can see if attached works?

    Yes, you can open new thread with new topic.

  35. #35
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Regarding the language, everything I've done is in English. Dates and the format of then included

  36. #36
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Then try opening my last document...

  37. #37
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    I did


  38. #38
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    If you enter this formula in an empty cell in the summary sheet, what do you get?

    =TEXT($C3,"[$-409]mmm")

  39. #39
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    This is what I get
    Attached Images Attached Images

  40. #40
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    replace comma with ;

    =TEXT($C3;"[$-409]mmm")

  41. #41
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I need a formula which : When dates match transfer the data from the entire row

    I think i get it.


    =IFERROR(INDEX(INDEX(INDIRECT(TEXT($C3;"[$-409]μμμ") &"Data");0;COLUMNS($C$3:D$3));MATCH($C3;INDEX(INDIRECT(TEXT($C3;"[$-409]μμμ") &"Data");0;1);0));"")

    Try it Spiros...

  42. #42
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I need a formula which : When dates match transfer the data from the entire row

    NBVC. I have not see your last example. What did you modify on it? I feel very tired to search...

  43. #43
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    it is what you have above, except I did not change the mmm to μμμ... you should not have to... according to this site: http://excelribbon.tips.net/T011782_..._Function.html

    all you should have to do is change the code in the [ ] to conform to the language....

  44. #44
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Smile Re: I need a formula which : When dates match transfer the data from the entire row

    Unbelievable

    There are no words to thank you both of you.
    I dedication like that ........

    Works perfectly !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


    Attached Images Attached Images

  45. #45
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I need a formula which : When dates match transfer the data from the entire row

    Σπύρο, πήγες για ύπνο; (Spiros, did you sleep?)

    And now that we finished? Spiro gotta say, it's sacrilege to put me in the same category with NBVC and the DDL.

    These people are light years ahead of me in Excel

  46. #46
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    So I guess Fotis' alteration worked... well, it's all Greek to me

    Glad you got it fixed.
    Last edited by NBVC; 06-05-2012 at 12:14 PM.

  47. #47
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    So I guess Fotis' alteration worked... well, it's all Greek to me

    Glad you got it fixed.
    Dear NBVC,

    The superb formula =IFERROR(INDEX(INDEX(INDIRECT(TEXT($C3;"[$-409]μμμ") &"Data");0;COLUMNS($C$3:D$3));MATCH($C3;INDEX(INDIRECT(TEXT($C3;"[$-409]μμμ") &"Data");0;1);0));"")

    that you gave me (with fellow Greek Fotis language assistance) worked excellent.
    When I tried to use it in another worksheet didn't work because you've determine (I can't find how) the "Data" to work only in months (JunData, JulData and so on)
    Could you inform me how to switch JunData, JulData and the other named ranges in Data1, Data2,....Data100 ?


    Thanks once more in advance

    Spiros

  48. #48
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    I am not 100% sure I know what you are asking, but if you go to Formulas tab, then click Name Manager, you will see all named ranges, select for example JunData and you will see the range used in the Refers to box, which you can edit by clicking Edit button.

    Is this what you mean?

  49. #49
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    I am not 100% sure I know what you are asking, but if you go to Formulas tab, then click Name Manager, you will see all named ranges, select for example JunData and you will see the range used in the Refers to box, which you can edit by clicking Edit button.

    Is this what you mean?

    This is what I was expecting to happen.
    When a named range change, the formula should continue working.

    But it doesn't !

    The formula continue working only when there is a month reference to Data. (i.g. SepData)

    That's why I bother you again.
    I thought that, there must be something more.

    In the re-uploaded file you'll see what I mean. I change JunData to Data1 and the formula stopped working
    When I add a new sheet and made a new named range SepData, everything works fine.

  50. #50
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    That is because this part of the formula:

    INDIRECT(TEXT($C3,"[$-409]μμμ") translates to Jun.. so that we can reference that specific month's data.

    If you change the reference to Data1, then you need to somehow automatically determine which data set you need based on the month in column C...

    So maybe if you name January's data Data1, and June's data as Data6 (since June is 6th month), then you can change formula to:

    =IFERROR(INDEX(INDEX(INDIRECT("Data"&MONTH($C3)),0,COLUMNS($C$3:D$3)),MATCH($C3,INDEX(INDIRECT("Data"&MONTH($C3)),0,1),0)),"")

    but I am not sure why you want to change anyway, was there a problem with the naming?

  51. #51
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    That is because this part of the formula:

    INDIRECT(TEXT($C3,"[$-409]μμμ") translates to Jun.. so that we can reference that specific month's data.

    If you change the reference to Data1, then you need to somehow automatically determine which data set you need based on the month in column C...

    So maybe if you name January's data Data1, and June's data as Data6 (since June is 6th month), then you can change formula to:

    =IFERROR(INDEX(INDEX(INDIRECT("Data"&MONTH($C3)),0,COLUMNS($C$3:D$3)),MATCH($C3,INDEX(INDIRECT("Data"&MONTH($C3)),0,1),0)),"")

    but I am not sure why you want to change anyway, was there a problem with the naming?
    Hmmmmmmm...

    The idea was, these sheets with their data and named ranges to work independetly.
    Which means, Sheet 1 and named range Data1 refers to the first month or period in general (could be week).
    I.g. This month (June) should be sheet1 and named range1, in order to have the ability to have the second week of August 2013 in sheet35 and named range Data35.

    May be the original thread wasn't right.
    What I really need is to match dates and data in lets say random order.

    Is this possible ?

    By the way: the new version of the formula works also great !!!

  52. #52
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    So when you are in the summary sheet, how would you identify where to look for the data? What "clue" is the logic supposed to follow?

    I am not sure why 51 posts later, we decide maybe the original request was wrong.

  53. #53
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    So when you are in the summary sheet, how would you identify where to look for the data? What "clue" is the logic supposed to follow?

    I am not sure why 51 posts later, we decide maybe the original request was wrong.
    My dear NBVC,

    Please forgive me and try to remember, 1. we are NOT on the same level of understanding (struggle to keep up) 2. English is not my strongest point (poor to be exact)
    I was thinking that the original thread was right. In my mind looked like simple to match the same date rows in 2 different sheets. i.e. data transfer when 2 dates match.
    Obviously not having the knowledge how to do it, I made fault assumptions.

    Any way you gave me 2 wonderfull solutions, I'll work with them and
    when I'll be able to understand better how Excel works I'll be back.

    I apologise for the inconvinience


    By the way the new version of the formula works better than the previous one and doesn't have first's issues
    Last edited by spiros63; 06-11-2012 at 01:43 PM.

  54. #54
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    I am not trying to be a bad guy about it. I apologize if I sound that way. I do want to help. I am just trying to understand your requirements and it seems we worked a lot to get to where we were, and now it seems that isn't what you really wanted.

  55. #55
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    I am not trying to be a bad guy about it. I apologize if I sound that way. I do want to help. I am just trying to understand your requirements and it seems we worked a lot to get to where we were, and now it seems that isn't what you really wanted.

    Okay

    Since you don't send me to ....

    I'll prepare a detailed example and you'll find it tomorrow (as you can realize in my time zone is 21:20 at night) and I have to leave the office (company policy)
    Hopefully this time I'll do better !!!

    Thanks for your patience
    Last edited by spiros63; 06-12-2012 at 05:53 AM.

  56. #56
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    I am not trying to be a bad guy about it. I apologize if I sound that way. I do want to help. I am just trying to understand your requirements and it seems we worked a lot to get to where we were, and now it seems that isn't what you really wanted.
    Dear NBVC

    In the uploaded file "Data Transfer", worksheet "Info" there are all the available information.
    In this file you'll also find two approaches.

    Thanks again for your time !
    Attached Files Attached Files

  57. #57
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    So I am having difficulty understanding how you make your choices, i.e daily, weekly, monthly.. Are tabs 1 to 14 always going to be the same (i.e. the dates in column C will always be the same whether you pick daily, weekly, monthly)? Basically, I want to know how the data will differ if you pick daily, weekly, monthly, etc...?

  58. #58
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    So I am having difficulty understanding how you make your choices, i.e daily, weekly, monthly.. Are tabs 1 to 14 always going to be the same (i.e. the dates in column C will always be the same whether you pick daily, weekly, monthly)? Basically, I want to know how the data will differ if you pick daily, weekly, monthly, etc...?
    So,

    Those tabs have been made only to show that the formula stopped on 12 month period.
    The main question is, if the equation's element MONTH can be replaced with something like (YEAR(MONTH(DATE.
    If this can be done, I think all the problems will vanish because the restriction of 1, 2, 3... month will be out of the picture.
    Happiness = (Consumption/Desire)

  59. #59
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    This formula will use all 14 tabs based on first year being 2012....

    Please Login or Register  to view this content.

  60. #60
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    This formula will use all 14 tabs based on first year being 2012....

    Please Login or Register  to view this content.

    Works perfectlly !!!!!!!!!!!!!!!!!!!!!!!!!
    If I want to expand it (much more tabs) what should I change ?

  61. #61
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Add more tabs in same sequential order, and name the data in each tab in a similar and sequential pattern as what you've got.

  62. #62
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    Add more tabs in same sequential order, and name the data in each tab in a similar and sequential pattern as what you've got.
    Do you really mean, that without change anything in the formula I can have as many tabs as I want ????????????????
    Last edited by spiros63; 06-12-2012 at 12:01 PM.

  63. #63
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need a formula which : When dates match transfer the data from the entire row

    Yes, as long as the pattern is consistent (no skipping months either)... and the start is January 2012.

  64. #64
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: I need a formula which : When dates match transfer the data from the entire row

    @ NBVC

    If, after 62! post,persist in saying "it's all Greek to me", then you must begin immediately intensive Greek lessons.

    In this case, I'm a volunteer ..

  65. #65
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by NBVC View Post
    Yes, as long as the pattern is consistent (no skipping months either)... and the start is January 2012.

    Well in this case,

    It's official !!!

    You've replace Zeus in my 12 Gods Pantheon !!!!!!!!!!!!!

    This cake end up a 100 floor cake accompanied with a ton of cherries on the top

    There is nothing more to say...

    Speechless...

  66. #66
    Forum Contributor spiros63's Avatar
    Join Date
    04-23-2012
    Location
    Voula Attikis - Greece
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: I need a formula which : When dates match transfer the data from the entire row

    Quote Originally Posted by Fotis1991 View Post
    @ NBVC

    If, after 62! post,persist in saying "it's all Greek to me", then you must begin immediately intensive Greek lessons.

    In this case, I'm a volunteer ..
    Dear Fotis,

    You can't imagine what I can do with this, essentially, auto-formula .
    My hands are no more tight. Now the sky is the limit.
    62 posts is nothing in front of the outcome


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1