dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
Hi guys,
I have this issue with a formula. Here is an outline of the task:
What I'm trying to do:
I am copy/pasting data from a source file into my data table then pulling certain columns from my data table into a report table (on the same worksheet). The trouble column in my data table has a field name: destroy old stock and cells contain either one of three values --- "0", "1", or "" (blank). Sounds simple right...
In my report table (in the corresponding column 'destroy old stock') there is a formula that converts zeros and ones into no's and yes':
I inherited the formula from my predecessor is as follows:
=IF(INDEX($J$1:$XFD$96495,ROW(A3),MATCH(I$1,$J$1:$XFD$1,0))="0","No",IF(INDEX($J$1:$XFD$96495,ROW(A3),MATCH(I$1,$J$1:$XFD$1,0))="1","Yes",""))
As I understand it, this is saying: in the range for data table, in this row...exactly match the report table header in cell I1 (destroy old stock) with data table headers in range J1:XFD1...then if the cell in the data table says zero, return "no" but if the cell in the data table says ONE, return "yes", or if blank, return blank.
Relevant fact: The source file comes from an *xls worksheet i.e., 97-2003 version of excel.
See googledrive link for picture
https://drive.google.com/file/d/1JFN3GvKgufNvmZadE-3YN3yiyeTuixZB/view?usp=sharing
Issue:
See googledrive link for picture
https://drive.google.com/file/d/1nJpp8rzvLv6MaXW6oxUoZEd01OdF51Nr/view?usp=sharing
As you can see, the cells that I've double clicked into have lost this formatting and no longer return a value.
It appears that the 97-2003 excel file in the column 'destroy old stock' has some text formatting unique to the 97-2003 format that 2010 excel doesn't recognize as text formatting but for some reason, the formatting is required to make the formula work. Does this mean that the formula only works for the earlier versions of excel?
Is there a better formula for 2010 excel that would allow me to do the same thing?
I have this issue with a formula. Here is an outline of the task:
What I'm trying to do:
I am copy/pasting data from a source file into my data table then pulling certain columns from my data table into a report table (on the same worksheet). The trouble column in my data table has a field name: destroy old stock and cells contain either one of three values --- "0", "1", or "" (blank). Sounds simple right...
In my report table (in the corresponding column 'destroy old stock') there is a formula that converts zeros and ones into no's and yes':
- If the cell has a zero in my data table, I want the formula to return "no" in the report table;
- If the cell has a ONE, I want the formula to return "yes";
- If the cell is blank (""), I want the formula to return a blank.
I inherited the formula from my predecessor is as follows:
=IF(INDEX($J$1:$XFD$96495,ROW(A3),MATCH(I$1,$J$1:$XFD$1,0))="0","No",IF(INDEX($J$1:$XFD$96495,ROW(A3),MATCH(I$1,$J$1:$XFD$1,0))="1","Yes",""))
As I understand it, this is saying: in the range for data table, in this row...exactly match the report table header in cell I1 (destroy old stock) with data table headers in range J1:XFD1...then if the cell in the data table says zero, return "no" but if the cell in the data table says ONE, return "yes", or if blank, return blank.
Relevant fact: The source file comes from an *xls worksheet i.e., 97-2003 version of excel.
- The column data for 'destroy old stock' in the source file is different from all the other columns in the source file in that: it contains an error message for in each cell (each cell contains a small green arrowhead in the top left of the cell). The error message reads: "number stored as text". This is odd because when you click one of these cells and check the format, the formatting is set as 'general', not text. Also, double-clicking on one of these cells removes the 97-2003 formatting, causing the formula to return blanks for 1, 0 and blank.
See googledrive link for picture
https://drive.google.com/file/d/1JFN3GvKgufNvmZadE-3YN3yiyeTuixZB/view?usp=sharing
Issue:
- if I copy the 'destroy old stock' column data from the source file and 'paste with formatting' into the data table, then the formula works...however...
- if I copy the 'destroy old stock' column data from the source file and 'paste values' into the corresponding column in the data table, then the formula in the report table returns blanks.
See googledrive link for picture
https://drive.google.com/file/d/1nJpp8rzvLv6MaXW6oxUoZEd01OdF51Nr/view?usp=sharing
As you can see, the cells that I've double clicked into have lost this formatting and no longer return a value.
It appears that the 97-2003 excel file in the column 'destroy old stock' has some text formatting unique to the 97-2003 format that 2010 excel doesn't recognize as text formatting but for some reason, the formatting is required to make the formula work. Does this mean that the formula only works for the earlier versions of excel?
Is there a better formula for 2010 excel that would allow me to do the same thing?