A
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.
THE PROBLEM:
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.
EXPLANATIONS:
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.
ARRAY FUNCTION EXAMPLE:
In cell C4 of MAIN, we have the following typical array functio
referring to the DB file:
={SUM(IF(DB_Codes=$B4;DB_Filters))}
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:
9312..................={SUM(IF(DB_Codes=$B4;DB_Filters))}
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).
GETTING SPECIFIC ABOUT THE PROBLEM:
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
9312).
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
text').
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
EXPLANATIONS above).
THE REQUEST:
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,
Alex
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.
THE PROBLEM:
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.
EXPLANATIONS:
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.
ARRAY FUNCTION EXAMPLE:
In cell C4 of MAIN, we have the following typical array functio
referring to the DB file:
={SUM(IF(DB_Codes=$B4;DB_Filters))}
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:
9312..................={SUM(IF(DB_Codes=$B4;DB_Filters))}
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).
GETTING SPECIFIC ABOUT THE PROBLEM:
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
9312).
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
text').
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
EXPLANATIONS above).
THE REQUEST:
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,
Alex