Make a sheet where i can swap out tables using for instance a list

[The excel apprentice]

Voorbeeld excel vraag.jpg

First of all i don't know if what i want is possible, and if it is it is i think that would probably be by using some kind of function/formula, but if i am in the wrong thread i will remove it (if i can?) and place it in the correct thread (i would need some guidance where that would be tho).

Okay, as you can see i have made an example sheet (picture). Now basically what i would like to do is: swap out table A for table B IF it selects ''cruiseship'' in the list to which table B refers.
If i where to select Tugboat, i would like to swap it out with table C, and so furth.

I would:
- like to know if this is even remotely possible, and if so, how?
- prefer the data to be drawn out of a seperate data worksheet
- prefer to do it like in the example, with a list
- like to know if my formulas, and conditional formating won't get messed up (is it possible that it uses the conditional formatting from the seperate datasheet i refered to above?)

Basically, lots of questions and little knowledge,

If anyone could help me out or point out that this is simply impossible, that would be greatly appreciated!!

[dosydos]

any chance you can upload that sample sheet you made?

[ChemistB]

You can pull exact copies of your tables over using your dropdown list but you will lose formatting, data validation and conditional formatting (unless all the tables have the same type, then maybe we can work with that).
Are all your tables the same width? If not, are column sizes at least approximately the same width?

Can you upload a few examples (Go Advanced>Manage Attachments)

[José Augusto]

Hi
I supposed that your tables as 1 row for title, 1 row for labels, 7 rows to data values and 2 other rows for space, all in Sheet2
In Sheet1 use the following

Please Login or Register  to view this content.

where C1 as data validation with the name of the tables

See the file DataOffSet.xlsx

[The excel apprentice]

I have attached an ''upgraded example'' i hope this helps

- Tables and column sizes are the same (as in A is the same as A in any other table, but (A and B might vary in width, but that is the same in all tables)
As you can see in my example A has a bigger Width than B, but that is the same for all tables.

I expect that conditional formatting will be a problem since the area indicator bars won't be on the same row for all tables?
- since you dint mention formulas (chemistB), i asume that won't be a problem?

[The excel apprentice]

Oh you can't actually see that the width of A is the same in table B and C, since i forgot to edit table B and C to have the same width. They do have that in the original document where i intent to use this.

[ChemistB]

Are the three tables in separate workbooks? In separate sheets in the same workbook? or all 3 in a single separate sheet?

Also, are you using actual Tables or just ranges that look like tables? If you click within an actual table, you will see a new "Design" tab on your ribbon.

Sounds like this is best done with VBA, as formulas and formatting will not be able to be pulled with other formulas. Just need some of the above info.

[The excel apprentice]

I appreciate all your effort José, thanks for that.
Now i see this is by typing the name, could this also be done with a dropdownlist?
And is there any reason you typed T1 t/mT48 in for instance the tugboat table?

[The excel apprentice]

At this point i only made one of the real tables, including it's conditional formatting and formulas (formulas for title of vessel / automatic row numbers)
I think it would be the easiest to make separate data sheets for each table in the same workbook.

[The excel apprentice]

Oh and there are also quite some dropdown lists in the actual sheet, not sure if this will be a problem

[The excel apprentice]

You are asking a question beyond my expertise, i believe by your terms these are not actual tables, i use the lines around the cells to make them.

[ChemistB]

Okay, no formula will pull over other formulas or conditional formatting or data validation (dropdown menus) or cell coloring so it looks like VBA would be your only solution. Are you okay with that? Are you going to be the only one using this workbook or will many people?

[The excel apprentice]

Me and my boss are the only ones that will be using it at this stage, but we are searching for a new person to come and work here which would also need to be able to work in it.
I have only once in my life tried to do something with VBA which was quite difficult since i basically have only wrote one sentence of code in my life (using an toturial). The way i believe VBA works is once you have inserted the code all you will have to do is activating it with a short key, am i right?

[The excel apprentice]

I am okay with it as long as it won't make things more difficult, since it would also be an option to simply make multiple worksheets, and drag them in the right place (and dragging the other one out). I thought this could be a fancy thing, especially since my boss claimed this to be possible.

[José Augusto]

I appreciate all your effort José, thanks for that.
Now i see this is by typing the name, could this also be done with a dropdownlist?
And is there any reason you typed T1 t/mT48 in for instance the tugboat table?

Hi @apprentice
1)
In Sheet1!C1 I put a data validation list with the names Sheet2!$J$2:$J$5 (four tables).
So you can use the dropdownlist (I don't know if office 2007 allows Data Validation (DV) to the list on different sheets. Try to use DV on Sheet1)
2)
I don't typed 1 t/mT48. Please try to download my file again.
3) Conditional formatting is not transmitted through formulas.
Regards

[The excel apprentice]

voorbeeld vraag.jpg

This is what i meant (picture), i downloaded your file again, but the numbers are there (allthough i think it is to show it draws data out of the other sheet, right?).
Allthough it doesn't matter, since it is important for me to maintain my conditional formatting

Thanks for the effort!