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?)

Can't say for sure without more details, but the VLOOKUP function can be used to retrieve data from a table

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)?

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

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?

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

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

*smacks forehead*

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

i think its working *crosses fingers*

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

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?)

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?

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

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 ^^ ]]]

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?

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

different cells, i thought it would be asking too much tho, thx anyway