Seek for Suggestions on how to Display the Data for Easy Reference

[liau8funny]

Hi

I have an excel sheet as attached with many tabs link to 1st tab namely <Database>.
Let say if I create 80 tabs in this workbook, the file size will be very big and it will be very tedious to manipulate too.
Any suggestion to manage my data for easy reference?

Thanks.

Br
Fenny

[canapone]

Hi,

did you consider to try a pivot table.

[Tsjallie]

There are several options:

1. Integrating the display sheets
   As far as I can see the only difference between the display sheets is the Unit code on row 1. You could introduce a dropdown box with a base code for the unit of which the other unit codes (columns D, L and T) are derived from. Shouldn't be too difficult. This way you can have one display sheet and a database. Even if there would be minor differences between display sheets, that could be solved.
   As a bonus this way of working would also guarantee the consistency of the display sheet as there will be only one sheet (or a limited set) left to maintain.
2. Moving to Excel 2010
   This would dramatically reduce the file size. For example: the attached xls is 279.040 bytes. Saving it as xlsx (2010) reduces it to 110.200
3. Splitting up the workbook
   You could - for instance - put the database in an other workbook

[liau8funny]

Hi,

did you consider to try a pivot table.

Hi Canaponel, can you do some demonstrations using my data for me?
Appreciate it.

[liau8funny]

There are several options:

1. Integrating the display sheets
   As far as I can see the only difference between the display sheets is the Unit code on row 1. You could introduce a dropdown box with a base code for the unit of which the other unit codes (columns D, L and T) are derived from. Shouldn't be too difficult. This way you can have one display sheet and a database. Even if there would be minor differences between display sheets, that could be solved.
   As a bonus this way of working would also guarantee the consistency of the display sheet as there will be only one sheet (or a limited set) left to maintain.
2. Moving to Excel 2010
   This would dramatically reduce the file size. For example: the attached xls is 279.040 bytes. Saving it as xlsx (2010) reduces it to 110.200
3. Splitting up the workbook
   You could - for instance - put the database in an other workbook

Hi Tsjellie, thanks for your valuable reply.

1. I will try this method but may need a lot of effort to address the differences between display sheets as we have different remarks for every customer, eg: Cell L29:B38 in sheet <B15>

2. our company only using excel 2007

3. Splitting out the workbook may cause the difficulties when I share the files with my colleagues with different version of excel.

[Tsjallie]

Forgot to mention option #0: do nothing and just accept the problem is there.
Sounds a bit silly, but it's a serious option. Of course it depends on how important the application is for you and/or your company and whether the problem is growing.
What would happen if the application wouldn't work anymore? If the answer is "nothing special" now or in future, then I wouldn't put too much effort in it.

Where exactly lies the problem: does it take a long time to load, does it take a long time to calculate or is it getting difficult to keep track of things because there is too much data?

I will try this method but may need a lot of effort to address the differences between display sheets as we have different remarks for every customer, eg: Cell L29:B38 in sheet <B15>

If these remarks are common then may be the can be integrated in the sheet's layout. Alternatively you could collect these remarks in a separate sheet and have a hyperlink in the database which gets displayed in the sheet depending on the unit chosen.

BTW, the pivot table Canapone mentioned is a similar solution, but would additionally need your database to be properly structured.

[liau8funny]

Hi Tsjallie,

1. Thanks for your enlighten, I will try this.
"If these remarks are common then may be the can be integrated in the sheet's layout. Alternatively you could collect these remarks in a separate sheet and have a hyperlink in the database which gets displayed in the sheet depending on the unit chosen."

2. Can you show me some demonstrations for this? FYI, my pivot knowledge is still very limited.
"BTW, the pivot table Canapone mentioned is a similar solution, but would additionally need your database to be properly structured."

[Tsjallie]

Tried to produce a sample pivot table. No luck. Costs an awfull lot of effort to restructure the database and still. See attachment.
I'm pretty sure this is not what you looking for. Must say I'm not really familiar with pivot tables and - given the struggle I've been in - that won't change very soon. Drives me crazy

Going through the database however I noticed quite a few exceptions and I wonder how you're keeping track of them. At some point in time this must lead to mistakes and - considering the amounts involved - these could well turn out to be quite painful. Forgive me for taking the liberty to bring this up.
If this workbook is really important for you or your company (see my comments on option #0) I would recommend you to go thru the design and do the necessary restructuring. Integrating all the sheets into one would be good opportunity for that.