Excel Links.

[marckssg]

Hi,

I have an excel data source I am trying to link to a table from a SQL database.

Ideally the common links would be consultant name and week number however, the data in the excel spreadsheet is presented as follows;

Consultant Week 1 Week 2 Week 3
Joe Bloggs 20 5 18
Ted Smith 15 4 2
Dave Clarke 2 30 14

So when I make the excel spreadsheet a named range so it appears as a table it sees the column titles as Consultant, Week 1, Week 2 and Week 3.

Over the year the number of columns will grow as Week 4, 5 6 etc gets added and the end user will want to be able to go back and do historical reporting across the year.

In my SQL database I have;
Consultant Week Number
Joe Bloggs 1
Joe Bloggs 2
Joe Bloggs 3
Ted Smith 1
Ted Smith 2
Ted Smith 3
Dave Clarke 1
Dave Clarke 2
Dave Clarke 3

So as an end result what I'd want to see is

Consultant Week Number Value
Joe Bloggs 2 20

For this report I am limited to using Crystal Version 9, coming off SQL Server 7 database linked to an Excel 2003 spreadsheet.

Importing the spreadsheet into SQL Server is not an option and changing the format of the spreadsheet is not an option, neither is accessing the source data used to produce it each week, so I feel a little limited in what I can do right now!

In the absence of physcic linking based on the current date has anyone else come across this problem, if so whats the best way of dealing with it?

Cheers

Marc

 

[IanWaterman]

Have not tried this but my best guess would be to link the SQL to Excel On consultant name.

Then create formula which looks up Excel value

If sqlDB.Week = 1 then ExcelWeek1 else
If sqlDB.Week = 2 then ExcelWeek2 else

etc.....

 

[marckssg]

Hi,

Thanks for the reply, a key detail I ommitted is that I need to show the weeks relevent to a particular month, so it could be weeks 1, 2, 3, 4, 5 for January 2008, 6789 for February 2008, but planning ahead the week numbers per week will adjust slightly for subsequent years.

Plus if I ran the report in the 3rd week of January I would not want to include any future weeks at this point, so weeks 4 and 5 would be left out in the example above.

I have tried and failed to get the excel dump changed but thats a go so I only have what I have to work with if that makes sense!


Cheers

Marc