Excel - create anchors? 1

[blackduck]

I am creating a worksheet with 52 small tables in it (ie 2 columns "issue" and "comment", 10 rows, and one table for each week of the year) and would like to goto a particular week without having to scroll through.

I made a drop down list populated with each week. Is it possible to goto the corresponding weeks table once the week is selected from the list?

My next thought was a command button with "go" which takes the value from the list and finds it on the sheet?

Any ideas greatly appreciated.
Thanks

 

[GlennUK]

If you name each table, then doing Edit/Goto ( or F5, or using the drop-down to the left of the formula bar ) will give you a list to choose from.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Hi,

"...one table for each week of the year..."

This raises [red]RED FLAGS[/red] with me.

Will the data from week n NEVER EVER be compared, aggregated, analyzed with the data from week m?

If your answer is unequivocally and without a doubt, "YES tables will NEVER EVER be compared, aggregated or analyzed!", then I grudgingly agree.

However, it is nearly always a bad design to chop similar data up into separate tables, that themselves have data designations, as Week1, Week2 etc.

Furthermore, if you do decide to NORMALIZE your tables, use a REAL DATE rather than week numbers.

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[blackduck]

Thanks GlennUK, though for this project I need to try and keep it really simple for user data input and just keep them on the worksheet with buttons etc instead of into excel.

SkipVought, I hear you re database design and would love to do the whole thing in access, but am restricted to excel which I'm finding difficult, because I see everything as a database and want excel to cope with that.

I agree the 52 tables are bad design. So I have flipped them to have one table with 11 columns and 52 rows instead. It looks really yuk for users data input being text comments and therefore wide columns, but at least the table is now normalized.

Thanks heaps,
also any other replies for still a better way would be appreciated.
Thanks again.

 

[SkipVought]

Good for you! It will pay off, believe me!

BTW, I never suggested Access. Excel will work just fine.

Tables are not desigend for aesthetic value. That's what reports are for.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[blackduck]

Skip, thanks for the support. You said Excel will work just fine (are you sure). Is there any tricks and functions you seem to always use to manipulate it like a database?

 

[SkipVought]

MS Query via Data/Get External Data/New Database Query -- Excel files -- YOUR WORKBOOK...

Each sheet can be treated like a table.

QBE Grid in MS Query is very much like Access QBE.

Data/PivotTable & PivotChart are two I use often.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[blackduck]

Thanks Skip,
Haven't played with MS Query yet because its not installed where Im at and dont have time to chase up at moment (cant wait to get home and try it).

Thanks for putting me onto pivot tables, I can see myself using them extensively now that I get it.

cheers to you
K

 

[SkipVought]

MS Query is IN EXCEL.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}