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)<VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,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 04:54, KnAsTa wrote:
Ok, maybe i have interpreted worksheet wrong. How do i create a workbook with all the week files within it?

First things first. set 1 template that you're happy with that you can copy/paste the data into, copy this sheet 18 more times, rename the sheets week## or whatever.

I tell you I hope I get a job with the Certificate I've got coming to me.

 

[KnAsTa]

Ok, now i have 20 worksheets in the one workbook, they include the 1 main worksheet of the soccer wins losses etc. The other 19 sheets are the weeks 1 to 19.

I have a formula:
=IF(ISNUMBER(MATCH(A5,week1:week19!A1:A10,0)),IF(VLOOKUP(A5,week1:week19!A1:D10,3,0)>VLOOKUP(A5,week1:week19!A1:D10,4,0),1,0),)

But this is just searching all 19 sheets at once, not giving values from week 1 to week 19.

For each week we need to see if the team wins. This is what we hope the above formula to do, but obviously it doesnt.

We need to know how to make it go through each individual week giving us a result for each week if the team wins or not, and returning the total number of wins.

For information on the formula variables, see my other post on this same question.

 

[Jay Petrulis]

Hi,

I just looked over the specs for the assignments and I believe you are making this way too complicated.

Your import of the text delimited file assumes that you will require a macro to download the data, right? If that is the case,
1. write the data to the appropriate sheet
2. transfer the weekly data to a single data table (DataSheet as Ian suggested)
3. Use SUMPRODUCT on the table sheet referencing the data table.
4. You may want to add an additional week reference, where at the change of a cell or button, you can quickly recreate the league table (sorted) for *any* week (this allows you to look at past results).
5. The sort should be last thing you need to add to your automatic procedure.

I'll play around with this a bit and see if I can give some guidance.

Bye,
Jay

 

[KnAsTa]

Whats a datatable or DataSheet? is this just having the 19 week spreadsheets in the workbook?

I am only a fairly new beginner in excel, and i can't understand much.
This message was edited by KnAsTa on 2002-04-15 23:22

 

[KnAsTa]

The problem i am having at the moment aswell, is how to represent the data given to us. Like are we meant to keep the 19 weeks datafiles seperate from the main league table, or do we put them into 1 workbook, etc.
The reasoning for this, is our lecturer is going to run his own 19week data through our spreadsheet, and by this i expect that he wants the files to be seperate?

 

[Dave Patton]

Jay and Ian's suggestions are consistent with the approach/formula that I suggested last weekend.

2. transfer the weekly data to a single data table (DataSheet as Ian suggested)

One sheet on your main spreadsheet. You can also include a copy of the source data if you want but that is not necessary.


3. Use SUMPRODUCT on the table sheet referencing the data table.

See previous postings or with named ranges

=SUMPRODUCT((rB_=A4)*(rF_)*(rA_=$B$3)+(rC_=A4)*(rG_)*(rA_=$B$3))

4. You may want to add an additional week reference, where at the change of a cell or button, you can quickly recreate the league table (sorted) for *any* week (this allows you to look at past results).

You can also use the week reference such as R1 as a criteria for the above formula.

N.B.
- ensure that you understand fully the requirements of your project
- what must you accomplish
- what information must be assembled to achieve the solution

All of the requirements except one can be done with a few formulas like Sum, Sumif and Sumproduct on a Summary Sheet and a Total Sheet.