Conditional linking in Excel

[Howi]

Can anyone help with the following problem:-
I have 5 identical workbooks that contain one worksheet,I want to collect the information from these 5 workbooks into 1 new workbook.
Inside the 5 workbooks there is a column that contains a number between 1 and 16 based on certain test criteria.
I only want the row of data to be linked into the new workbook if the number in the above column is above 10.
In other words the new workbook should only show records from the other 5 workbooks if the number in the above column is > 10. I have been assured it can be done but no one could tell me how!!

 

[shetlandbob]

Write a macro to open up your 5 spreadsheets (in turn) and interogate the value in question, if its above 10 then link to the new workbook.

Record macro's to give you a starting point on how to do this.

 

[SkipVought]

Howi,

I try to use MS Query via Data/Get External Data... to access data from other workbooks/worksheets, rather then copy 'n' paste. As bob suggested, ounce you arrive at at method of getting the data, put it in a macro.

If you take any macro route, post in the VBA forum707

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[GlennUK]

You could use an IF function, to test the "above column" ( whatever that means ), and then either link or return null depending on the result. So, something like this:

=IF([FileOneOfFive.xls]Sheet1!$B$4>10,[FileOneOfFive.xls]Sheet1!$F$56,"else test was less than or equal to 10")

where the formula tests cell B4 in an external workbook, and if that is greater than 10 then it links cell F56, otherwise it gives a string of "else test.....".


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Howi]

Thanks fellas - that should give me something to work on

very much appreciated