Array functions and cell formatting problem


Alex Hatzisavas

Dear all!

I kindly request your help with the following problem i've run into:

I'll try to be as explanatory as possible, so please bear with me :).

I'm working on Excel XP.

I'm using a Main Excel file (MAIN) that uses several array functions t
perform some computations.
These array functions refer to Ranges in a Database (DB) Excel file.

There is also an Input Excel file (INPUT), that feeds MAIN with data.

After MAIN is fed with data from INPUT, it tries to calculate its arra
functions, which refer to ranges in DB.

So we have the following 3 Excel files:
INPUT (Data) >>> MAIN (array function computations, with range
referring to the DB file) >>> DB.

I'm having problems with the formats of the cells in the INPUT.
The array functions in MAIN seem to be having problems depending on th
format of the cells that are 'fed' to them from INPUT.

The INPUT Data is two columns, one with Codes, and one with Number
(production requirements for the respective Codes).

The Codes (INPUT Data) can be any of the following:
- numbers only (as in 10275),
- characters and numbers (as in M9202),
- numbers starting with zeros (as in 0050).

Thus, i believe the best choice for the format of the Codes (INPU
Data) is the 'Text' format (in 'Number' format, 0050 would b
transformed to 50).

The INPUT Data (two columns, Codes and Numbers) are being fed to MAIN
where the array functions work.

In cell C4 of MAIN, we have the following typical array functio
referring to the DB file:

Please note that:
- cell B4 contains the Code that was 'fed' to MAIN from INPUT
- Ranges DB_Codes, DB_Filters refer to the DB file.
Each Range refers to a single column of the DB file.

Thus, in Sheet 2 of MAIN we have (omit the dots):
In cell B4:..........In cell C4:

Cell B4 contains the formula: = (Sheet 1 of MAIN) B4 (this cel
contains the value 9312, and gives me the green arrow error message fo
'Number stored as text').

(INPUT Codes are pasted in Sheet 1 of MAIN.
Sheet 2 of MAIN 'reads' these values from Sheet 1 and performs th
array function calculations).

What the array functions should do:
Let's assume the INPUT Code that was fed to cell B4 of MAIN is: 9312.
Then, the array function of the above example will look at Rang
DB_Codes of the DB file (DB_Codes is a single column of the DB fil
containing various Codes).

Whenever the array function encounters 9312 in the DB_Codes column, i
will SUM the respective elements of the DB_Filters column (basicall
0's and 1's).

Some array functions of MAIN (the ones that should look up Code
consisting of numbers only) don't work correctly.
The array functions that have to work with codes containing bot
numbers and letters (as in M9202) work fine.
The problematic array functions seem to get confused by the cell forma
of the Code (that was 'fed' to them from INPUT) that they are trying t
look up in the DB.

In all files (INPUT, MAIN, DB) i get that little green arrow on the to
left of those cells that contain Codes that are numbers only (such a

The error message i see when i click on the exclamation mark is:
Number stored as text.
(Sure Mr. Excel, but i want these numbers stored as text, so you don
enter a Code such as 0050 as plain 50 :).

No matter what combinations of cell formatting i try (Text, General
Number etc. in all 3 files: INPUT, MAIN, DB) some array function
simply won't work.
More specifically, they won't recognize the records of the DB fil
where a certain Code is contained.

Referring to the ARRAY FUNCTION EXAMPLE above, imagine that Colum
DB_Codes of the DB file contains 500 records with the Code 9312.
Thus, for those 500 records of the DB file, the array function shoul
have summed all respective elements of the DB_Filters Column.
However, it does not, and what i get is a plain 0.

To overcome the problem for a single formula, i tried to key in 9312 i
cell B4 of Sheet 2 of MAIN (thus substituting the formula: = (Sheet 1 of
MAIN) B4 ).
I got the green arrow again, with the same error ('Number stored as

In the error menu, I chose to 'Convert text to number', which caused
the array function to calculate correctly !!!.....

The problem is,
1. I have around 3500 Codes in the INPUT file, and cannot afford to
'Convert text to number' for all of them
2. I'd rather have these Codes in 'Text' format, as they can be
combinations of 00's and other digits (please refer to paragraph

Can you possibly think of any way around this problem?
It's really slowing me and my work down.

In case you need any clarifications, please contact me here or at:
(e-mail address removed)
I could send you a small sample file with the problem.

I'd be very very grateful to get some help on this one :)))

Thank you for your time and patience,




Frank Kabel

I would try to format ALL cells as 'Text' and also the search value
should be stored as 'Text'.

Frank Kabel
Frankfurt, Germany

"Alex Hatzisavas" <[email protected]>
schrieb im Newsbeitrag



Alex Hatzisavas

Thanks Frank, i tried, well, all cell formatting combinations, includin
turning all cells to 'Text' format.

Unfortunately, even if a cell is in 'Text' format, if you've got
numeric value in it (e.g. 9312) you get the green arrow error warnin
from Excel XP ('Number stored as text').

The array functions couldn't seem to work their way around this, s
after having spent some time trying to overcome the problem, i decide
to add three apostrophes (''') to all data being read, i.e. the DB an
INPUT data, thus FORCING all cells to finally accept their 'Text
nature :)

Thankfully, this worked.

Thanks for your time! Keep up the good work.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question