+ Reply to Thread
Results 1 to 9 of 9

HELP! formula to transfer data and place in proper location month2month..c attached

  1. #1
    Registered User
    Join Date
    10-21-2015
    Location
    california
    MS-Off Ver
    excel 2007
    Posts
    31

    HELP! formula to transfer data and place in proper location month2month..c attached

    So I am trying to get my sheet2 to auto fill from information on sheet 1. If the location is brisbane and the date is 10/01/15 i want the ADD value to appear in the desired location.

    I did example for brisbane 10/01/2015 but if you look at a more complex location/date like almost any fresno etc how can i make this auto fill and go month to month?
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-21-2015
    Location
    california
    MS-Off Ver
    excel 2007
    Posts
    31

    Re: HELP! formula to transfer data and place in proper location month2month..c attached

    I was thinking pivot table or something BUT that is just not working I don't know why but it just scatters it..

  3. #3
    Registered User
    Join Date
    10-21-2015
    Location
    california
    MS-Off Ver
    excel 2007
    Posts
    31

    Re: HELP! formula to transfer data and place in proper location month2month..c attached

    !HELP! Still unsolved

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,515

    Re: HELP! formula to transfer data and place in proper location month2month..c attached

    So for 10/1 and Fresno, would it be an average of all numbers on 10/1 or the Sum? or something else? Are all values taken from the GC column? Does GD column influence any of this?

    Assuming SUM, for the first table on sheet2, in B3 Copied and Pasted (Copy and paste, do not drag)

    =IFERROR(SUMIFS(Table1[GC],Table1[City],$A3,Table1[Date], B$2),"")
    (if averaging values, replace SUMIFS with AVERAGEIFS)

    I custom formatted the cells for 0's to be "--"
    Attached Files Attached Files
    Last edited by ChemistB; 11-06-2015 at 06:50 PM.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    10-21-2015
    Location
    california
    MS-Off Ver
    excel 2007
    Posts
    31

    Re: HELP! formula to transfer data and place in proper location month2month..c attached

    I want them to add the SUM

    SO Sheet1 Fresno 10/01/2015 has multiple numbers...I want those transfered to sheet2 Fresno 10/01/2015 added up already

    Fresno 10/1/2015 1549 0
    Fresno 10/1/2015 1483 0
    Fresno 10/1/2015 1599 0
    Fresno 10/1/2015 991 0

    So the designated spot would be 5622

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,515

    Re: HELP! formula to transfer data and place in proper location month2month..c attached

    See Post 4

  7. #7
    Registered User
    Join Date
    10-21-2015
    Location
    california
    MS-Off Ver
    excel 2007
    Posts
    31

    Re: HELP! formula to transfer data and place in proper location month2month..c attached

    Quote Originally Posted by ChemistB View Post
    So for 10/1 and Fresno, would it be an average of all numbers on 10/1 or the Sum? or something else? Are all values taken from the GC column? Does GD column influence any of this?

    Assuming SUM, for the first table on sheet2, in B3 Copied and Pasted (Copy and paste, do not drag)

    =IFERROR(SUMIFS(Table1[GC],Table1[City],$A3,Table1[Date], B$2),"")
    (if averaging values, replace SUMIFS with AVERAGEIFS)

    I custom formatted the cells for 0's to be "--"
    OMG thank you! so how can I make this fill for all the dates listed? and then continue monthly? I usually drag the little piece at the bottom right but in this case it is seperated by some rows and columns

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,515

    Re: HELP! formula to transfer data and place in proper location month2month..c attached

    As I said, Dragging doesn't work when using table references like I did (there are ways but that's another story) So copy B3 into B17, manually change "B$2" to "B$16, then copy and paste that into the rest of table 2, repeat for table 3, modifying the references each time.

  9. #9
    Registered User
    Join Date
    10-21-2015
    Location
    california
    MS-Off Ver
    excel 2007
    Posts
    31

    Re: HELP! formula to transfer data and place in proper location month2month..c attached

    Quote Originally Posted by ChemistB View Post
    As I said, Dragging doesn't work when using table references like I did (there are ways but that's another story) So copy B3 into B17, manually change "B$2" to "B$16, then copy and paste that into the rest of table 2, repeat for table 3, modifying the references each time.
    Not working on my original spreadsheet for some reason. everything is pritty much the same including the table any idea why? Can I send you private message of the original post to show me what you mean? Maybe one of my tables is messing it up or something
    Last edited by michaelDDW; 11-06-2015 at 08:12 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Populate each item to proper place
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 07-20-2015, 10:50 AM
  2. [SOLVED] Put value in its proper place
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-19-2014, 12:13 PM
  3. Replies: 0
    Last Post: 05-16-2012, 04:16 PM
  4. Help adding IF ISNUMBER in Proper location in Array Formula
    By fridgeymonster3 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-06-2011, 01:00 PM
  5. How to make a graph start at the proper place?
    By Adrian20XX in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-10-2009, 05:55 AM
  6. Place chart at specific cell location
    By Landmine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2006, 12:40 AM
  7. Is this the proper place to ask my question?
    By amkazen in forum Excel General
    Replies: 1
    Last Post: 03-01-2006, 01:25 PM

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