Need help with a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

andrew.carroll@europe.com - 08 Nov 2006 10:55 GMT

I am trying to create a formula that will look for a value on another
worksheet and return a number of values from the same row of each
instance it finds....

The worksheet I am linking to is formatted as follows...

Country    Provider    Technology    Definition    Cost    2004    2005    2006    2007    2008
UK    A
France    A
Germany    B
UK    C
Spain    A
UK    C

I want to lookup all instances of a country I input in cell G3 on a
different worksheet, and have it return the data from the other columns
for each instance of that country that it finds. Ideally I would like
it to return the column value based on the title of each column, but
that's a separate problem.

At the moment I am using the following array formula, but it is
returning a NUM# error.

{=INDEX("'"&$G$3&"'!"&A$1:BC$100,IF("'"&$G$3&"'!"&$A$1:$A$100=$G$2,ROW($A$1:$A$100),""),
COLUMN(B1)}

Any advice would be greatly appreciated.

Many thanks

Andrew

Reply to this Message

Ron Coderre - 08 Nov 2006 12:50 GMT

Try something like this:

With
Your sample table on Sheet1, in Cells A1:J7

Then
On Sheet2
G3: (a country name)
H2: (a year, e.g. 2004)
J2: (a year, e.g. 2006)

This formula finds each Country referenced in cell G3 and returns the
corresponding values from the column referenced by the Year in H2
H3:
=SUMIF(Sheet1!$A$1:$A$7,Sheet2!$G$3,INDEX(Sheet1!$A$1:$J$1,1,MATCH(Sheet2!H$2,Sheet1!$A$1:$J$1,0)))

Copy H3 to I3 to harvest the sum for the Year in cell I2

Adjust references to suit your situation.
Post back with any questions.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

> I am trying to create a formula that will look for a value on another
> worksheet and return a number of values from the same row of each
[quoted text clipped - 27 lines]
>
> Andrew

Reply to this Message

andrew.carroll@europe.com - 08 Nov 2006 13:35 GMT

Ron,

Thanks for the quick response....

Only issue I think I see with that formula is that it would not work
with text entries - am I correct ? The first few columns after the
Country contain text fields, so I assume SUMIF would not work for
these.

Many thanks

Andrew

> Try something like this:
>
[quoted text clipped - 55 lines]
> >
> > Andrew

Reply to this Message

Ron Coderre - 08 Nov 2006 13:49 GMT

That's true....So...I have questions:

How would you want to handle "UK" which has 3 Providers?
Would you want totals for UK_A, UK_B, and UK_C?

And, what about the other columns of information?....How would you want to
handle the lookup of information for those?

I think if you filled in the sample data and gave an example of how you'd
like the results for UK to display, we'd be able to give you a more tailored
solution.
***********
Regards,
Ron

XL2002, WinXP

> Ron,
>
[quoted text clipped - 68 lines]
> > >
> > > Andrew

Reply to this Message

andrew.carroll@europe.com - 08 Nov 2006 14:22 GMT

I was thinking of just bringing the data for the selected country (and
columns) into the 2nd worksheet, and then managing what subsets of this
is used within that worksheet (using validation lists). There are a lot
more columns in the original sheet than I have shown, so your method of
using the heading to choose the column is what I need - I just need it
to work for text fields also.

Really appreciate your help on this...

Thanks

Andrew

> That's true....So...I have questions:
>
[quoted text clipped - 85 lines]
> > > >
> > > > Andrew

Reply to this Message

Ron Coderre - 08 Nov 2006 14:48 GMT

Maybe this is an approach to consider:

Assumptions in this example:
Sheet1 contains your sample data in cells A1:J100
(Note: A1 contains the title "Country", instead of a blank)

Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: Country
B1: Provider
C1: Technology
D1: Definition
E1: Cost
F1: 2004

Insert>Name>Define
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$F$1

I1: Country
I2: (a country name...possibly from a dropdown list)

Insert>Name>Define
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$J$100

Notice:
you are on Sheet2, and creating a
Sheet2-level range name, but
the referenced range is on Sheet1.

The reason:
An advanced filter cannot SEND data
to another sheet, but it can
PULL data from another sheet.

Now...set up the Advanced Data Filter:
<Data><Filter><Advanced Filter>
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]

That should pull all rows from Sheet1 that match the Country value in
Sheet2!I2.

Note: If you want to run that Advanced Data Filter repeatedly,
you'll need to re-select the List Range "Database" each time

....OR...if you're feeling a bit ambitious...

You can build a short macro to automatically re-run the filter:

Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insert>Module

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!Criteria"), _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=False
End Sub
'---Start of Code-------

To run the code:
Tools>Macro>Macros (or [Alt]+[F8])
Select and run: PullMatchingData

(Change the sheet names and range references above if they differ from your
actual structure)

To test, change the Country value in I2 and run it again.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP

> I was thinking of just bringing the data for the selected country (and
> columns) into the 2nd worksheet, and then managing what subsets of this
[quoted text clipped - 98 lines]
> > > > >
> > > > > Andrew

Reply to this Message