Currently Being ModeratedJan 24, 2013 2:13 PM (in response to Larry duff)
Here is a formula to get you started. What it does is tell you which rows have the specified fruit (Apple, Orange, Pear, etc).
Table 1 in the formula below is your "one big table". The formula assumes Table 1 has a single header row and no footer rows. It allows you to add new rows to Table 1 without having to modify the formula.
In your "Apple" table,
Cell F1 = 1
Cell F2 =MATCH("Apple",INDIRECT("Table 1::A"&1+F1&":A"&ROWS(Table 1)),0)+F1
fill down with the formula in F2
There will be a lot of error triangles but it doesn't really matter; you'll be hiding this column when everything is completely set up.
Now that you have all the row numbers for "Apple", you can use the OFFSET formula to get the corresponding data from your "one big table". You will also be using the IFERROR function or using an IF statement of the form IF(ISERROR(F),"", your offset formula) so your table won't be full of error triangles.
Currently Being ModeratedJan 24, 2013 2:27 PM (in response to Badunit)
Too late to edit my post so I'll just add to it:
You could also use the INDIRECT function to get the corresponding data. For instance, to get the date:
Currently Being ModeratedJan 24, 2013 2:33 PM (in response to Larry duff)
Here's an approach that I've used...
In the Purchases table, Aux column, the expression is:
=COUNTIF($A$1:A2, A) & "-"&A
This expression builds a string that identifies the item and the ocurrance of that item.
The Date column of the Summary tables, cell a3, contains:
=IF(ROW()-3<COUNTIF(Purchases :: $A,$A$1), LOOKUP(ROW()-2&"-"&$A$1, Purchases :: $F, Purchases :: B), "")
Fill Across, then fill down.
Currently Being ModeratedJan 28, 2013 11:29 AM (in response to Jerrold Green1)
Thanks for both methods, I used the second one, as it was easier for me to understand what actually I am doing.
I will try to go throught the first one step by step to see how it works, too.
Currently Being ModeratedJan 28, 2013 12:37 PM (in response to Larry duff)
Yeah, the formula is kind of confusing to look at. All it is doing is trying to find the word "Apple" in your table and tell you what row it was in. The complicated part is that it needs to use a different range each time it looks, otherwise it would always return the first match in your table.
Currently Being ModeratedJan 30, 2013 1:48 PM (in response to Jerrold Green1)
Ahhh, have just realized there is one more thing that I missed originally. If the "big table" contains a blank cell, then the corresponding cell in "Apples" should also be empty (or marked with something different than a zero). If the "big table" cell is not empty, then the formula Jerrold gave is completely sufficient.
I have struggled a bit with ISBLANK, but cannot seem to be able to properly refer to the right cells. Guess I need spend more time on that area.
Currently Being ModeratedJan 30, 2013 4:05 PM (in response to Larry duff)
It depends on what kind of problem you had, how you might want to deal with it. If you can predict what might happen to cause an error, sometimes it's sufficient to trap the error with IFERROR().
Other times it's better to eliminate the source of error before it develops. This often does require a test to see if a cell is empty or not. ISBLANK is not a function that I use often. I prefer to check the length of the string in the cell, or see if the cell equates to a null string.
Posta screen shot the example that's giving you trouble.