Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Location
    Calgary, Alberta, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pasting data in other tables (Excel 2003)

    I have a workbook which decides on distances of places based on temperatures .

    I am trying to figure out how i can paste the data from one worksheet to another automatically. Probably need a macro
    The contents pasted would be dynamic so if the table in the decider worksheet changed so would the one on the Sheet1 page.

    this basically what I am trying to accomplish:
    a button called "Get Distances" which populates a table from data in the decider page into a table in sheet1

    the code behide the button would have a procedure something like this
    Find Place1 on worksheet "decider" in this range(G6 to O6) when Place1 is found, copy the values in the 6 cells
    below it and paste the contents into this range (F91:F97) on sheet1 worksheet

    then move on and find Place2 in the Worksheet "Decider" in range G6 to O6,
    copy the values in the 6 cells below it and paste them in (E91:97) in sheet1 worksheet

    then move on and Find Place3 on the worksheet "Decider" in range G6 to O6,
    copy the values in the 6 cells below it and paste them into (g91:97) in sheet1 worksheet

    the procedure repeats through 9 places but not all 9 places will be picked

    I am stuck because I cant use a function like offset because the data moved over may need to be adjusted.


    Then once that data is moved into the table on sheet 1, and all adjustments are made to the distances
    another button will transfer the information from this table into the schedule worksheet.

    the information will be paste on the same row as the place#
    so,
    The data on the row in column c with the string "place1" will be pasted on the same row at column u based on where ever place1 is
    located in column C and pasted every time place1 is typed in Columnc on the same row


    then when place2 shows up in column c on the schedule worksheet the data on the right of place2 in the table from sheet1
    will be pasted on the same row where ever place2 until place3 is entered in column c on the schedule page. This continues for all
    the places listed in the table on sheet1

    I have copied and pasted what I am trying to transfer in for place1 and place2 through out the workbook
    to help describe what I am tring to do.


    any help would be great, I am learning the VBA code to do this but must start off from a book.

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting data in other tables (Excel 2003)

    I am not sure exactly what you are trying to do. It seems how you imagine doing it, from what I understand (admittedly, I don't understand it well), could be done much simpler with formulas and lookup tables.

    Having lookup tables or some "linking" to the other sheets, the formulas will always be uptodate. Otherwise you would have to have macors run constantly to ensure that everything is updated. Granted they could be triggered on cell changes (or better yet, sheet activation), lookup tables (being formulas) are much faster, easier to create, adjust better if changes are made to the spreadsheet, and don't trigger "macro warnings".

    Could you explain what you are trying to accomplish (not how you envision doing it, sometimes we have "alternatives"). What data do you have and what info do you want to extract from the data?

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting data in other tables (Excel 2003)

    I looked at what you had again. If I understand the first part, If you add the labels:
    "Place1" in Sheet1!F90, "Place2" in Sheet1!G90, ..."Place6" in Sheet1!K90

    and you add to Sheet1!B91, the label "Name", you can then populate the rangeSheet1!F91:K97 with an index and and matches. In the cell Sheet1!F91 enter:
    =INDEX(Decider!$G$7:$O$12,MATCH($B91,Decider!$F$7: $F$12,0),MATCH(F$90,Decider!$G$6:$O$6,0))

    and copy f91 to clipboard and paste it into F91:K97. This will lookup the appropriate values from the table. It essentially grabs the info from the range
    Decider!$G$7:$O$12
    The row it will use will be:
    MATCH($B91,Decider!$F$7:$F$12,0)
    which is a match for the item in column B which is the same as in column F in Decider (in the appropriate range).
    The column is determined similarly:
    MATCH(F$90,Decider!$G$6:$O$6,0)
    which is a match for the item in row 90 which is the same as in row 6 in Decider (in the appropriate range).

    When you make changes to row 6 in decider it will put different items in sheet1.

    I am still completely confused on the second part of what you want...

    Steve

  4. #4
    New Lounger
    Join Date
    Sep 2005
    Location
    Calgary, Alberta, Canada
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting data in other tables (Excel 2003)

    Thanks Steve I actually took what you gave me and read up on the INDEX function a bit and fiqured out that you are right that a lookup table is alot more
    effective then a VBA program, Iguess its better to Use excels functios first. So I used a simple match in F89 I wrote Place 1 to Place6 in K89. Then with a Match Function Match F89,Decider!$G$&:$O$7,0)

    Then for columns F91 to K97 I used the INDEX(Decider!$G$6:$O$14,1,F89) and changed the row search and the coluymn as needed.

    So thanks for planting the seed.

    Here is a better Explaination for the second part,

    What I am tring to do with the schedule is load it by referenceing to the table on Sheet1.

    If you look in column c in the schedule page there is a value of Places for each row ....Place1, Place2.through to..Place6.

    this I figure is a way of calling up values to fill in distances for that row starting at column U.

    worksheet schedule at C12=Place1
    Lookat the table on Sheet1 B91:B97 and find Place1
    when place 1 is found (B92) take the values from F92:K92 and show them in the schedule starting at U12 on the same row at Place1

    worksheet schedule at C13=Place2
    Lookat the table on Sheet1 B91:B97 and find Place2
    when place 2 is found (B93) take the values from F93:K93 and show them in the schedule starting at U13 on the same row at Place2




    I am trying to fill in the schedule from the data in the table on sheet1 but am trying to figure out anoffset or index system to do this.
    Is a macro needed for this or would it require alot of future maintainace?

    I hope this is a better explanation.

    Thank you.
    Allen

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting data in other tables (Excel 2003)

    How about in U12:
    =IF($C12="","",VLOOKUP($C12,Sheet1!$B$92:$K$97,COL UMN()-16,FALSE))

    Copy from U12 to clipboard and paste to U12:Z35.

    The 16 is from the "offset" you want in (eg) col U (= col#21) the 5th column from the range B-K [B(1), C(2), D(3), E(4), F(5)]

    If nothing is in col C it will display "nothing" (actually a null string). If the lookup value can not be found, it will display a #NA error.

    You could use instead:
    =IF(Iserror(VLOOKUP($C12,Sheet1!$B$92:$K$97,COLUMN ()-16,FALSE)),"",VLOOKUP($C12,Sheet1!$B$92:$K$97,COLU MN()-16,FALSE))

    Which is hide any "errors" from the lookup.

    Steve

Posting Permissions

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