How to lookup repeated data?

[PERERA]

Say I have a large list of data and the left most column (A) has many repetitions as below.

Country¦Project¦ Month¦ USD
AT¦ A¦ 1¦ XXX¦
AT¦ B¦ 6¦ XXX¦
BE¦ C¦ 4¦ XXX¦
UK¦ A¦ 1¦ XXX¦
UK¦ A¦ 2¦ XXX¦
USA¦ A¦ 4¦ XXX¦
USA¦ B¦ 5¦ XXX¦

With out filtering in the same sheet I would like to use a function similar to lookup and get all the data related (summary) to one country. I would like to see all the entries (as if I have used the advance filter) please. Thank you
This message was edited by PERERA on 2002-10-14 11:07

 

[ThePencilQueen]

How about a pivot table with country in the page, project in the rows, month in the columns and sum of dollars in the data.

You could then use the drop down to create a new page for each country and copy and paste to a separate summary sheet.

Of course I could be talking out of my a**e

 

[PERERA]

Thank & sure all what you said will work. But I want to publish the data in the web with no knowledge about HTML or that sort (I have XP, perhaps using FrntPage2002) and it seems that active spreadsheets do not support pivots or filters but will support formulas. So I would like to have one sheet as kind of a template (series of formulas) then source sheet hidden with all the data. Formulas may be liked to a validation list to act as a variable!! Does it sound complicated or do you need some more information?

 

[ThePencilQueen]

It sounds VERY complicated - have you looked into importing into access? Access has some very nice user friendly ways to put data onto the web without getting too tied up in html?

 

[PERERA]

In fact data is extracted from Access. I did not looked in to the possibility of publishing directly from there. I assumed it would be difficult! Good if it is easy and does not need coding I will go for that option. Could you direct me to any good sites that I can get some info on this please?

 

[ThePencilQueen]

The best place I've found is in the Access help files - try searching for statis and dynamic format (static involves uploading your spreadsheet/data as is, dynamic allows it to be updated/added to once it is online)

Then it is just a case of creating a query/report showing the data you want and exporting it as html files (File|Save as HTML| follow the wizard)

 

[chef]

have done this a few times myself...prob is trying to explain solution in a couple of replies..

i am off on holiday just now but if you want to private message me with an example of your worksheet i could probably help you out..i am back to work next monday

next...you dont really need to know html to publish your work and i can talk you through how to hide your main sheet so that no user would see.

regards
chef