error in code please help

[ALBUNDY]

Im doing the same project as KnAsTa and I have similar problem with the formula.

=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)
It will not work and instead it posts #N/A. THe formula works when the first bit is A1-A10 but not B. It should make no difference as the column B has similar results to A.

Any help would be greatly appreciated
Thanx

[Ian Mac]

On 2002-04-15 01:54, ALBUNDY wrote:
Im doing the same project as KnAsTa and I have similar problem with the formula.

=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)
It will not work and instead it posts #N/A. THe formula works when the first bit is A1-A10 but not B. It should make no difference as the column B has similar results to A.

Any help would be greatly appreciated
Thanx

What do you mean by 'similar' results, a NA error means that the lookup or match is NOT in the list your looking at.

What I think you should both do is post a sample of the data your look at, and give us a few examples/senarios.

I can't be bother to type waht Aladin has written a hunderd time (Thanks Aladin)...

'The best way to do is that you select an empty cell, type = in thet cell, select the first 10 rows including labels, hit F9, copy what you see behind the =-sign, and paste it in the follow up.'

Also look at:

http://www.mrexcel.com/board/viewtop...c=4967&forum=2

I spent a long time typing it and no bothered to tell me if it was of any use!!

[ALBUNDY]

Thank you, Knasta has posted our range of results

[KnAsTa]

Ok Ian,
i have checked the range of results you have suggested to do several times, and each time it comes up with the right range of Cells i am looking at when i press F9.

In my post about the formula, i have included information about our spreadsheets, and have included the whole of the external spreadsheet we are getting information from.

I have tried to make it as clear as i can.

If u want to see what our main spreadsheet looks like, go to http://undergraduate.cs.uwa.edu.au/u.../Project1.html

thanx

[Ian Mac]

On 2002-04-15 02:38, KnAsTa wrote:
Ok Ian,
i have checked the range of results you have suggested to do several times, and each time it comes up with the right range of Cells i am looking at when i press F9.

In my post about the formula, i have included information about our spreadsheets, and have included the whole of the external spreadsheet we are getting information from.

I have tried to make it as clear as i can.

If u want to see what our main spreadsheet looks like, go to http://undergraduate.cs.uwa.edu.au/u.../Project1.html

thanx

It sounds like we're doing you work for you both, if your happy with that then so be it.

you keep mentioning external sheets BUT looking at the project brief you need to import these to the current book.

"You will write an Excel spreadsheet application with at least 20 worksheets. Of these 19 worksheets will hold the weekly results from the games. There will be one worksheet for each week. "

So why the need for the other books???

Also, looking at the sample data I don't believe you'll get just 19 sheets, in the real world that is. Tha actual example table no the first page of the web site shows that while Liverpool and Man U have played 32 games, Arsenal and THE MIGHTY Newcastle hav only played 30.

firstly, there are 38 games each in the English premier league, not 19.

secondly, not every game is on a Saturday, or the week end for that matter.

I don't know how your project accounts for that???

I'd just like to know your thoughts on the above before proceeding.

[KnAsTa]

Ok, what we have to produce is a main spreadsheet which contains all the formulae for each column. They are then going to test this spreadsheet with their own 19week spreadsheets, so it won't be in a workbook.

It is true that we will not just get 19 sheets, so that also needs to be encorporated into the formula. And i do not know how to do that. I would look it up in excel help, but i don't know the key word to look up!

[Ian Mac]

My recomendation is to put the scores from the other books into the one your working with, this'll help greatly in getting towards the end I think you need to be at. Going along with this idea, I'd suggest on the same sheet (DataSheet)

If you do that you can set up a table on a different sheet that has Team names along the top AND down the side. you could then reference the DataSheet to see if the teams have played each other yet.

I think unless you do this, it's gonna be hard to to do. well hard to explain at least.

[KnAsTa]

the problem is that i'm not aloud to do it that way. All the 19 files have to be seperate from the main spreadsheet. So somehow i have to link to them.

[Ian Mac]

My question then is, how do you reference sheets that don't exsist at the begining of the season? The short answer is you can't, well not without some clever jiggery pokery and I don't believe your ready for that.

Also, there is no need to Private Mail me with the same question, others need to see this (otherwise I'll be along to collect my certificate for passing this badly designed project), I'm not a 1 man band, I certainly don't have all the answers.

If you like you can E-mail me what you have so far, I Can't/Won't do the work for you, I will however give some good pointers, and just because I'm feeling generous, let the others on the board get a better understanding of the problems you have.

Finally, I really think you should take the following back to your tutor:

Your spreadsheet is required to summarise the existing weekly data worksheets into a single summary table like the above example.

NOTE: Worksheets NOT Workbooks. I think your interpretation of the question is wrong, if your absolutely sure it's not, then I'd question this strongly as it's a different kettle of fish dealing with external data.

[KnAsTa]

Ok, maybe i have interpreted worksheet wrong. How do i create a workbook with all the week files within it?