Getting data from two Excel worksheets

[Deb100]

I am getting the majority of my data from one worksheet, however, I need 1 field from another worksheet.

My data is grouped by Client ({tbl.Client}&", "&{tbl.Initials}) then by Stock {tbl.Stock}.

The data looks like this in the report:

Bloggs,J

Date______Stock_____________Holding____Price
30/05/06__ABC Ltd___________1,250.00___12.68
05/10/06__current holding____-1,250.00___???

05/05/06__DEF & Co__________2,675.00___8.97
05/10/06__current holding____-2,675.00___???


I need to show the current price for the stock (???). This is held in a worksheet, which looks something like this:

Date_____ABC Ltd___DEF & Co___GHI Inc___
________ABC_______DEF_______GHI_______
_________13.54_____8.01_______5.27______ *These cells hold a formula (=B2093, =C2093, =D2093 etc).

Row 2093 holds the latest price for each stock.

How do I get the value from either row 3 or 2093 for the stock in my report?

 

[synapsevampire]

Pivot your data in the spreasheet into an intelligent table design.

Not sure why you would have 2 sets of headings in ther eto begin with, but spreadsheets are very flexible and allow for od designs, unlike databases and tables in general.

Tables are designed to NOT require that a user know WHERE a specific data point is, you use logic to extract it.

So if you pivot the data to look something like a table you'll have no difficulty:

field1 field2 field3
data1 data2 data3

-k

 

[Deb100]

Thanks, Synapsevampire, will have a go.

Deb100