automatically copy items from a changing list into a sortable table

Hi,

I have a list (formatted as a table) on one sheet that I want users to be able to change. On another sheet there is a table that needs to have every item from the list in the first column. So far I have achieved this by using "=Table2[[#This Row],[Crop List]]" in every cell of the column where I want the list copied. ("Table2" is the list, "Crop List" is the header on the list.) This basically works, but it has some drawbacks:

1) When I try to sort the table, the items referencing the list are stuck in their original positions and no longer match the other columns.

2) I have to have a large number of cells displaying "#VALUE!" at the bottom of my table because they refer to empty cells in the list which might later be filled.

Any suggestions?

In case my explanation wasn't clear, I've attached a copy of the workbook. Everything I'm talking about is on the first two sheets, "Lists" and "Pricing."

Thanks!

Hi illyBLC

I think that your problem is that you are using the sheets for the wrong things. I'm not sure why you want the "list" sheet, but your pricing sheet should not be dependent on another sheet - the pricing sheet shoulod be the basis of your other sheets.

I would make the pricing sheet into 1 table, so that if you sort on 1 column, the other columns move with it.

To get rid of the "#VALUE!" (which you will not have if you follow my advice above ! ), wrap your formula in an "IFERROR" statement as in

Please Login or Register  to view this content.

which, in English is "value from Table2,Crop list unless that gives an error, in which case "" (nothing)"

Hope this helps

Regards
Alastair

Hi,

In case you were wondering, I want the "List" sheet because those lists are referenced in a number of other places, not only the Pricing sheet. My problem with the Pricing sheet was only one example of a problem that I have been dealing with throughout the workbook.

That being said, this IFERROR thing sounds promising.

Thanks,
Bill

Hi Bill

I understand needing to be able to reference elsewhere. If you have the pricing as your base, you will not get the problem 1) not matching other columns as there is no relevant column on the "list".

Perhaps you could reference to "pricing" and do away with the Crop list part of list? Even if you can't at least you will not have the mis-matching problem.

Let me know if I can assist further.

Regards
Alastair

Hi Alastair,

I had thought about doing away with the Crop List, or in effect using the first column of the pricing table as the crop list. The drawback of that plan would be that I still have the reference problem elsewhere in the workbook, for example on the "Summary" sheet. I also have a similar problem where the Task List is referenced on the Crop Report sheet, and so on. I am starting to think that there is no perfect solution to this problem.

I've changed the Crop column in the pricing table to simple references like "=Lists!A2," which helps with the sorting problem. Now, though, if an item is added to the Crop List the user has to copy that formula down to a new cell in the Pricing table. If I leave some empty cells at the bottom of the Pricing Table in case of an addition to the Crop List, then I have 0's there, which I don't like. I think that's the best I can do, though.

Meanwhile, IFERROR is helping things.

Cheers,
Bill

Hi Bill

You are probably right in that there is no perfect solution!
However, it is very easy to suppress 0s - set the number formatting (on the Home tab) to Custom:

Please Login or Register  to view this content.

which means the formatting for [positive];[negative];[zero];[Alpha]. You will note that there is nothing between the 2nd and 3rd semicolons ( so zeros are suppressed. Job done.

Regards
Alastair

That's a neat trick.