+ Reply to Thread
Results 1 to 18 of 18

Data gathering from multiple sheets

  1. #1
    Registered User
    Join Date
    02-26-2008
    Posts
    16

    Data gathering from multiple sheets

    last week i worked out how to combine two lists to report the common data, i now need a way to automatically search 3 seperate spreadsheets for the above data and report further information from the 3 sheets into one final sheet.

    basically, can a function in excell search other spreadsheets and report the results? (the data to be reported would always have the same relative position to the term searched for)

    also is it possible to make report forms to print, i'm hoping i can just do this with the layout of the final spreadsheet

    (i know this all sounds a bit like Access territory but is it possible with excell?)

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Can't say for sure without more details, but the VLOOKUP function can be used to retrieve data from a table

  3. #3
    Registered User
    Join Date
    02-26-2008
    Posts
    16

    Thumbs up

    ok, how can i explain this

    i have a list of data, say

    ref color size
    a1 red 6mm
    a5 green 7mm
    a77 blue 4mm

    and a reference, say a5, and i want to know the size of a5, its clearly 6mm.

    now say that table is 500 items long, and i've cross referenced 20 of them via another source that i need more information on, can i automate the above operation, by looking up the 20 references (in column 1, and reporting whats in column 3 on the same row)?

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    Is some of the data (e.g. color) on one spreadsheet and other attributes (e.g.) size on a second spreadsheet or are all attributes on both sheets, just not for all references? Are the spreadsheets within the same workbook?

    Assuming the second situation and that the sheets are all in the same workbook (or can be moved to the same workbook)
    Label Table1 in sheet 2 and Table2 in sheet 3 to represent your data

    Please Login or Register  to view this content.
    where your reference is in A2 and the results are in column 3 of your data tables. Does this work for you?

    ChemistB

  5. #5
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    color is in one sheet, and size in another, i could probably copy them to the same workbook (would select all then copy paste work?)

    the only thing on the sheet i wish to populate is the ref#, the lists including size and color have the same ref# next to the data i want to return to my result sheet.

    also how do i label tables?

    finally would that forumula fill down so it would search for the ref numbers in cells a2-a10 and report the size and color for all?

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

    1. To move the worksheet to the same workbook, have both open, right click on the worksheet tab and select Move/Copy and then select the workbook you want to copy it into. Rename the tab if you wish.

    2. To name a range; highlight the range and then type into the "name box" (that's the white text box in the top left which normally displays your active cell) the name you've chosen (can't begin with a number or have spaces or be restricted eg can't name it B5 as that's a cell name). Hit enter you have a named range.

    3. Since the data doesn't overlap (color in 1 table and size in another) and assuming both tables have the value you want in column 2. In your master spreadsheet in column B I'd put;
    Please Login or Register  to view this content.
    If the VLOOKUP doesn't find a match (e.g. returns an error), you'll get a blank space. Let us know if this works for you.

    ChemistB

  7. #7
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    i'm getting lots of blanks

    and i dont really know what the parts of that forumula are doing to correct it ^^

    got a link to a good nubs guide reguarding it? or could you give me a quick detail or each part? like, why its basically entered twice?

    edit: also, how do i direct it to look in the other sheets?

    edit2: i'm starting to make sense of it, but how is ISERROR a logic test? the discription of it confuses me.
    also what does ,"", do? and why are there two VLOOKUP functions? how does it all interact ^^
    Last edited by chris.leng; 03-06-2008 at 12:04 PM.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    The electronic Excel Helpfile on VLOOKUP is pretty good. Look at that to see what the individual terms in the function are doing.

    Okay, the first time the VLOOKUP is mentioned, it's checking to see if that function produces an error (in which case it returns a blank). The second time is when it produces a result. For troubleshooting, we just need the second part of the formula so just go with
    Please Login or Register  to view this content.
    If that returns a #NAME error, then it's not recognizing the name of your table most likely. If you named the range as we discussed, it should automatically know that range ColorTable is in Sheet2 (provided you did move everything to the same workbook)

    If your formula returns #NA then it is not finding your value in the first column of ColorTable. Double check that they are the same. Blanks and invisible characters count!

    It would help if you uploaded a zipped example of what you've done and we can troubleshoot it.

    ChemistB

  9. #9
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    *smacks forehead*

    01 and 1 are different, filling down on the other tables auto corrected them for me

    i think its working *crosses fingers*

  10. #10
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    col_index_num is the number of the column in the table? i tried, it was

    is it possible for the above to for work between worksheets?
    even if its using tables linked to other sheets to accomplish the same basic thing?
    (of not having to copy a sheet each time someone makes a change)
    Last edited by chris.leng; 03-06-2008 at 01:04 PM.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    col_index_num is the number of the column in the table?

    also is the above possible for work between worksheets
    if not would it be possible to use tables linked to other sheets to accomplish the same basic thing?
    (of not having to copy a sheet each time someone makes a change)
    Yes, col_index_num is the number of the column in the table. It is not the Excel column number, just of the table. For example if your table started in Column E and you want to return a value in column F, that would be column 2.

    To access defined names in another workBook,
    Please Login or Register  to view this content.
    Book3 is the name of the other workbook. If the name contains spaces, it needs single quotes around it like so 'Book 3'!

    How we doing?

    ChemistB

  12. #12
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    hmm, getting there,

    =VLOOKUP(A1,Book3!ColorTable,2,FALSE)

    is it possible to use the whole sheet instead of a table (or would naming the whole sheet [using the select all button] ColorTable have any adverse effects on anything?)

  13. #13
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    we have success, i have a perfect printout sitting infront of me, thank you so much for all the help

    edit: oh, having a whole sheet named as a table wont be an issue will it?

  14. #14
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    I have never tried to make a whole sheet into a table. I'd say if it works for you then go for it. We could talk about dynamic named ranges (which change size as you add data but then we're getting into more complexity.

    http://www.contextures.com/xlNames01.html#Dynamic

    Glad things are working for you.

    ChemistB

  15. #15
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    well, it seems to so far, and i guess it would include any future data changes, its just meant i've had to move my reference cell to column A for it to work with the search but thats the only down side.

    the links take a very long time to update initially (pulling data from 3 different 2-5mb spreadsheets takes that long i guess) but after that its almost instant, is that normal?

    EDIT: np, left align works well enough, lol [[[finally how do i make the forumula's treat all the returned searches as text? most are text but the odd part number etc is just figures and this gets formatted as a number and aligned differently to the rest ^^ ]]]

  16. #16
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    to re-open an old thread, i've got another question

    if the term i'm searching for is returned in more than 1 place how do i get both results to disply?

  17. #17
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577
    I'd have to have a better idea of how your workbook is laid out. VLOOKUP does only return the first instance so you definitely will have to shift gears and you'll be looking at more complicated formulas (or even VBA). Also, are you asking for Excel to return multiple results to a single cell? or would they be in a series of columns/cells?

    ChemistB

  18. #18
    Registered User
    Join Date
    02-26-2008
    Posts
    16
    different cells, i thought it would be asking too much tho, thx anyway

+ 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