Make table size depend on data range

[andershess]

Hi all!

I have a little problem in this document: Forsøg.xls

In sheet 1 i have some data in the following cells I4 to K35.
At the moment in only use I4 to K22.

In sheet 2 i have a "table", where i just copied these cells from Sheet 1 unto Sheet 2, and added some extra rows myself.

How do i make a table in sheet number 2, that automatically take my data from sheet 1 I4 to K35, and makes the table?
I want it to automatically make the size depend on the data from sheet 1. If there is only data in I4 to K35, it should only be this data coming in the table (and the size depending on that). If it is I4 to K12, it should only be this data coming in.

Is even possible?

The last thing is, in Sheet 1 from B4 to B35 i have a data range called guides.
This one doesnt always have names in all of them.

In sheet 2, in the table - i have a column where i have a drop-down list which depends on the data range called guides.
But right now in the drop down list, there is a lot of empty to select, those where there is no name.

Is there any way to make the drop-down list only include the cells including text?


Hope some, who is more expert than me, knows this.
Thanks in advance!


Anders

[aydeegee]

Hi Anders

Here's my solution. If you change the number of Guides, just click on the button. If you want to copy to sheet 2, click on the other button.

Alternatively, use Ctrl + shift + Q (or W) as required

Regards
Alastair

[andershess]

It still doesn't happen automatically, thats my problem.
I have to "copy" in into the other sheet. I wished it could happen without me doing anything in Sheet 2.

[aydeegee]

Your wish is my command!

Do not do anything on sheet 2

To copy Salgsmuligheder from Sheet1 to sheet 2 Click the button "Copy Salgs"

To adjust the length of the Guides click on the button "Update Guides"

Am I missing something here?

Regards
Alastair
(a bit puzzled)

[andershess]

Okay i can actually see that now, sorry for that.

How do you manage the update the guides? what is the command for it to change depending on the number of guides i write?

Further, I know i will be a ***** now. But is there NO way of this happening automatically, whatout having to push any updates and so on?

[aydeegee]

Hi Anders

Yes. You can have it so that any amendnment to the guides will set off the re-sizing of the data validation. I have done this a few times, so I will work on this this evening.

Regards
Alastair

[aydeegee]

Hi Anders

As promised, a version that will automatically vary the range of the Guides data verification.

To see the code right click the Sheet 1 tab and select "view code"

Let me know if it works for you

Regards
Alastair

(and I still think that it's overkill ! )

[aydeegee]

Hi Anders

Sorry - I missed answering your question (which I believe is probably more important than providing you with an answer)

How do you manage the update the guides?

It's not very pretty - and I am sure that there is a slicker way of doing it but

Please Login or Register  to view this content.

Starts at B4 and wanders down until it reaches a blank and calls that row LR

Please Login or Register  to view this content.

call the range B4 to that predefined row GM

Please Login or Register  to view this content.

Get rid of the old named area called GuideNames

Please Login or Register  to view this content.

Set the new area to have the name GuideNames

The data verification then looks at the name GuideNames

Hope this helps

Alastair