Using TRANSPOSE() array function and returning blank values

**t_e_k** · 12-02-2009, 07:03 AM

Hello all, hope you are well!

My problem:

I have a mastersheet which uses TRANSPOSE() as an array function to return values from another workbook. The transpose has an INDIRECT in it so it knows which file t look at and which sheet within that file it should look at.

The issue is that, unlike paste special > transpose, TRANSPOSE returns blank cells as 0. The data it is looking at contains both blank cells and 0s, so I want to be able to return both. I tried an IF(ISBLANK()) but couldn't get this to work in the array.

Two spreadsheets are attached. I am not tied to TRANSPOSE so anything you think would work would be great.

Thanks,

Tom

**DonkeyOte** · 12-02-2009, 07:09 AM

If you need to return the numbers as numbers etc then realistically you will need to double evaluate given the mixed data types being returned...

ie if you could return the numbers as strings (coerce latterly if req.) you could avoid double evaluation by means of REPT, eg:

C2: =REPT(INDEX(INDIRECT("'["&$A2&"]"&$B2&"'!B2:B12"),C$1),1)
copied across

(note: I would use INDEX in pref. to TRANSPOSE irrespective)

**t_e_k** · 12-02-2009, 07:17 AM

Thanks DonkeyOte for another quick answer. Interesting use of REPT(), would never have thought of it!

And yes, I prefer to avoid arrays if I can so INDEX is good. Also you I don't think you can use array functions in tables, and I really like tables if I can use them!

I should be able to coerce latterly as you say. Just going to have a look at how my subsequent pivot tables deal with the values.

Thanks again,

Tom

**DonkeyOte** · 12-02-2009, 07:25 AM

The PT will view the number strings as strings unfortunately - ie it will not coerce.

If the data types of each column being returned are known in advance you could perhaps utilise that in your retrieval formulae... it's not clear.... if not then double evaluation/duplication of table are your most viable routes.

(and if on the off chance all the data is coming from one sheet then you could possibly run an external PT)

**t_e_k** · 12-02-2009, 07:54 AM

OK I see. Duplicating the table of results is not a problem, that's fine. Can you use one of the paste special functions to get it to recognise numbers as numbers, or would you use N() and IF() or something similar? Actually I might even be able to work that one out myself, maybe!

On a seperate note, I am getting some #VALUE! errors using REPT(). I didn't think to mention that some of the text strings are quite long. I can't find specific reference to it, but I think REPT doesn't like strings over 256 chars?

Thanks,

Tom

**t_e_k** · 12-02-2009, 08:02 AM

I have just found out how to multiply the entire set of data by 1 using paste special > multiply, and that seems to work for converting the text to numbers.