I'm copying column information from one sheet to another. Some cells have no information in them. On the receiving sheet those cells return a 0.00. How do I get those cells to return a blank cell like the first sheet has?
Thanks very much in advance!🙂
Not the behaviour I'm getting here with Numbers '09 v2.0.3 (on Tiger)
Original table (on Sheet 1) at the left. Image of column B of second table (on Sheet 2) to the right.
I selected B2:B19 on the first table, Copied, then switched to Sheet 2, clicked on B2 of the table there and Pasted.
Paste Values gave the same result.
Paste and Match Style (into a column formatted as Number, with 2 decimal place precision) appended ".00" to the cells where the value was 4, but the empty cells remained empty.
Data in the source cells was directly entered.
Regards,
Barry
Hello Barry
What is described is what we get when we transfer datas from a table to an other one with formula.
=Feuille 1 :: Tableau 1 :: C21
inserts 0.0 if the original cell is blank.
We may use a workaround :
=IF(LEN(Feuille 1 :: Tableau 1 :: C21)<1,"",Feuille 1 :: Tableau 1 :: C21)
but the cell is not really blank.
This is why I asked Apple to give a function which would be named NULL() or BLANK()
which we would be able to use to replace the empty string :
=IF(LEN(Feuille 1 :: Tableau 1 :: C21)<1,BLANK(),Feuille 1 :: Tableau 1 :: C21)
Yvan KOENIG (VALLAURIS, France) mercredi 30 janvier 2011 20:57:30
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2
My iDisk is : <http://public.me.com/koenigyvan>
Please : Search for questions similar to your own before submitting them to the community
I didn't clarify, I'm copying using the =
So on sheet two I click a cell, then "=", then click on a cell on sheet number one. Then I copy that cell on sheet two down the column to copy whatever it is on sheet number one because the values in cells on sheet number one change frequently.
An additional follow up question is if I'm copying using this method, how do I copy ONLY if a cell is NOT blank, but then copy a series of columns. For example:
In this case if there aren't any of a certain type (represented by no number of them) then I don't want it copied to the new sheet, but if there are I do along with the type and the sub-type.
Is there a way to do this? Essentially copying info on a sheet with rows across but ONLY if a condition is met?
Got it! It's the reorganize function then using the columns and clicking certain values to show or not.
Reorganizing is a way to achieve your goal.
When for some reason we can't reorganize the source table, we may use this scheme
In the source table, use column E as auxiliary one.
In E2, insert the formula :
=IF(LEN(C2)=0,99999,ROW()-1)
Apply Fill Down
In the target table.
In cell B2, insert the formula :
=IF(SMALL(SORrce :: $E,ROW()-1)<99999,OFFSET(SORrce :: B$1,SMALL(SORrce :: $E,ROW()-1),0),"")
Fill to the right (columns C and D)
Fill to bottom.
In fact this scheme was described here several times.
Yvan KOENIG (VALLAURIS, France) mercredi 30 janvier 2011 22:54:10
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2
My iDisk is : <http://public.me.com/koenigyvan>
Please :
Search for questions similar to your own
before submitting them to the community
Seriously??? in big red letters?
I DID - I was looking for filtering because that would make more logical sense!
When I'm not serious, I insert a smiley.
I know that the described scheme was posted many times because I did that several times.
Search for the keyword 99999 for see.
Yvan KOENIG (VALLAURIS, France) mercredi 30 janvier 2011 23:07:56
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2
My iDisk is : <http://public.me.com/koenigyvan>
Please : Search for questions similar to your own before submitting them to the community
Update - Reorganizing DID NOT work. It works to change a VIEW is all, but the actual rows still exist. This creates a problem because sheet two for example then has rows 1, 3, 4, 6 etc. But if you use formula's or functions the UNSEEN rows still exist and mess up the formulas.
What I'm looking for is how do I get a condition to actually delete a row, and move the next row up. So in this case row 3 would move up and become row 2 etc. This sounds more like a condition or filter and not just reorganizing a view.
What you're looking for here is a 'breakout' table—a table that copies data from rows that meet a certain criterion, in this case that the 'number of them' is greater than zero.
There are at lest two similar means of creating such a table. Both involve creating an index column, then using a fomula that will retrieve data from cells in the indexed rows.
Yvan's first formula above creates a list of the row numbers of the rows whose data is to be copied, and marks the unwanted rows with a number larger than the maximum number of rows in a Table in Numbers.
His second formula uses SMALL to retrieve those row numbers as the keys for OFFSET use to determine the row from which to collect the data.
(While a search on 99999 would bring up other examples of this, 99999 doesn't seem to be a search key that would occur to the average user looking for a solution to this issue.)
A second means of creating the necessary index is shown below.
Here the index column (labeled Hide me) assigns a serial number to the rows to be retrieved, using the formula below:
Data::E2 (and filled down to the end of column E): =IF(C>0,MAX($E$1:E1)+1,"")
In the Summary table, a LOOKUP formula uses it's row number minus 1 as the search value to retrieve the data from each indexed row. An IF statement is used to suppress LOOKUP when the maximum value in the index column is reached.
Summary::B2 (filled down to the end of column B and right to column D): =IF(ROW()-1<=MAX(Data :: $E),LOOKUP(ROW()-1,Data :: $E,Data :: B),"")
Regards,
Barry
Barry wrote:
(While a search on 99999 would bring up other examples of this, 99999 doesn't seem to be a search key that would occur to the average user looking for a solution to this issue.)
I thought that I was clear :
I asked to search upon 99999 to check that I posted several times the scheme which I described, not as a way to find thread answering the OP's question.
In this case, as I often wrote, a valuable keystring may be build from words grabbed from the question's title.
Before creating a new thread entitled :
Copying columns from one sheet to another, blanks return 0
it would be good to search with keystrings like :
copy AND column AND from AND sheet AND another
copy AND from AND sheet AND another
using simple words associated by the boolean operator AND is the best way to find the wanted infos.
Yvan KOENIG (VALLAURIS, France) jeudi 1 janvier 2011 09:51:15
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2
My iDisk is : <http://public.me.com/koenigyvan>
Please : Search for questions similar to your own before submitting them to the community
Thanks - I honestly thought the search for 99999 was a joke - I didn't think he was serious because it would make no sense to a user of this forum. BUT - now I see there are some ways to do this. I'll work through the info here - thank you so much for your help!
In my scheme, if the cells doesn't contain numbers the app issue a blue triangle warning.
This is why I put nothing in the header one.
As a table is limited to 65535 rows, putting the value 99999 in the cells which must be dropped, I'm sure that :
(1) there will not be a conflict between the row nums flagging valid rows and the value flagging invalid ones
(2) when the SMALL() it's easy to filter it..
When I built this scheme for the 1st time, I used 65536 to flag invalid rows but, as I am lazy, I decided to replace this value by one easier to remember and easier to type.
66666, 77777, 88888 or 99999 are values matching these requirements.
Re-reading my message, I discover that my script translating French formulas into English ones replaced the table's name "Source" by an awful "SORrce". It treated the two letters "ou" as the French function name "OU" which is "OR" in English.
I wish to add that from my point of view, Barry's scheme is more elegant. It's also safer assuming that one day, Apple engineers will break the 65535 rows limit 😉
Yvan KOENIG (VALLAURIS, France) jeudi 1 janvier 2011 18:56:16
iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2
My iDisk is : <http://public.me.com/koenigyvan>
Please : Search for questions similar to your own before submitting them to the community
Copying columns from one sheet to another, blanks return 0
