Transpose a column that contains blanks, but not copy the blank cells

[mtouhig]

I have downloaded a template that I am adding to that produces a league table. When I transpose a list (column) of match results to a row elsewhere on the worksheet, the blank cells in that list ie.the matches yet to be played and therefore empty, return a zero in the row. I can hide these zeros or format the font colour and cell background to be the same so that they can't be see - but nonetheless they are still there, seen or otherwise. This has a detrimental effect on another worksheet that examines the results to calculate a league table because if there is ANYTHING at all in the cell, it counts as a match played (see League Table tab)

As I am not the original author of the workbook, I am not exactly sure on how the whole thing works. if you look at the attached file, I have added only the 2 fixtures sheet and the scores sheet.

Ultimately, I wish to simply enter the score for each match once only in the list on the scores tab, and this to be echoed to the results grid. I have tried TRIM, ISBLANK etc but to no avail. any help gratefully appreciated thanks.

[martindwilson]

well there seems to be a problem with that xls

[mtouhig]

Okay thanks. I'll try and upload it again

[mtouhig]

I hope this can be downloaded

[martindwilson]

nope it crashes my excel

[mtouhig]

Hmmm - sorry about that. I'll try once more but before that, I'll strip out the irrelevant bits. Meanwhile I'm wondering if any else has been able to download it - whether they can offer a possible solution or not. Admiin? I've been researching the problem I have, it seems it is impossible for a cell to be blank - even if there is just a formula or the result ("")of formula in it.

[mtouhig]

Try this please - unless it's causing you problems

[martindwilson]

nope still the same

[mtouhig]

Okay - thanks anyway Martin . I can't think of a work around either

[mtouhig]

#martindwilson I think if I uploaded a couple of screen shots to here, I might be better able to illustrate the problem I have if you think that might help.? Give me half hour

[martindwilson]

well i opened it with open office and re-saved as excel but i cant see how this is supposed to work at all
there are loads of named ranges one that stands out is "teams" and is defined as =Results!$A$2:$A$25
so how the formula
=VLOOKUP(A3,teams,COLUMN(),FALSE) could ever work is beyond me

[mtouhig]

It does work if I enter each result individually into the Results! tab - the table produced is correct. But when I try to copy and paste or transpose or link cells from Scores! tab to Results! tab the affect is shown in the enclosed screenshots. I've also uploaded the file again from my pc rather than the Android tablet I was using earlier. Like I said in the original post - it is not my creation, it is a template I downloaded and I'm trying to add to it. Maybe there are hidden worksheets in there somewhere and protected. I understand if you want to give up though lol.Thanks very much for your time this evening.

[martindwilson]

ok i took a chance and ignored the error warning and opened it and can see everything
what particular formula is seeing all these 0 values
i cant see whats wrong with the league table

[mtouhig]

I have entered the results separately into the results worksheet. That works OK. What I want to do is somehow copy the results en masse from the scores worksheet (from the list of individual matches). The problem is the matches not yet played, they result in a zero when copied - however so. There must be workings out elsewhere that I can't see - eg. how the teams are ranked. Thanks again

Edit. It's the transpose function (using an array - { and }. )

[martindwilson]

well you can replace the transpose with for results in scores b2 with

Please Login or Register  to view this content.

that will give "" instead of 0
ranking is done on sheet "league table unsorted" which is hidden,right click any tab select unhide