Need EXCEL buttons designed to take cursor to various spot on sheet. 3

[Sherman6789]

I have an Excel spread sheet that is divided into six parts. Each part will be accessed by different people from their own computer using a common drive in the office. They all will be using the same file at different times.

When a person from the "C" group, for example, opens the spreadsheet; he or she is interested in data beginning on line 365. Persons from group "E" are interested in data beginning on line 610. I would like for them to be able to have the cursor jump to specific area at the click of a button. Also the line numbers may change frequently because data is constantly changing and lines are added and deleted daily. They complain that scrolling down the sheet is becoming a pain.

Any help you can give will be appreciated.

Thank you.

 

[CBasicAsslember]

Use a hyperlink with named ranges for each group.

 

[DaleWatson123321]

Other options...

1) AutoFilter - useful if the users will be required to modify the data.

2) Advanced Filter - If the users will only be viewing the data, this can be used to extract data for each group to a separate sheet (at the click of a button - with VBA code attached of course). To extract the proper data for each group, the data will of course need to have an identifier - which will be used as your "criteria" for the Advanced Filter extraction.

For the code, use the macro recorder for Data Filter Advanced Filter.

A real benefit from using the Advanced Filter is that you can easily "isolate" the data for each group - AND you can "customize" the extraction sheet... for example with a different header based on the group selected, and the sheet can be pre-set for printing which is usually appreciated by the end-user(s).

The exciting thing about the Advanced Filter, is that there is a "HUGE Depth" to its functionality. So the investment of time in exploring this will be WELL worth it.

Just a final (important) tip... Place your criteria on a separate sheet (and hide the sheet if required). This practice keeps scene cleaner - i.e. no interference with your data sheet. And more importantly, Excel can sometimes encounter a conflict IF the criteria were placed on the same sheet as your data.

Hope this helps.

Regards, Dale Watson

 

[SkipVought]

Hi,

Yet another option is MS Query which is one of Excel's very powerful data analysis and reporting tools, via Data/Get External Data/New Database Query...

You would insert the QueryTable on a new sheet. It could be a parameter query, which means that one of more criteria parameters can be priveded by the user in order to return the subset of data that they want to view. The DATABASE is your workbook and the TABLE is the sheet containing the source data that you refer to in your OP. The CRITERIA for the query will be similar to the Filter approch.

Skip,

_[red][/red]

 

[Sherman6789]

Is the following true? If so, how do I set it up.

Group A covers lines A30 - A255
Group B covers lines A260 - A489
Group C covers lines A505 - A821
Group D covers lines A835 - A1011

All are on the same spread sheet.
At the top of the page are four buttons labeled:

<GROUP A> <GROUP B> <GROUP C> <GROUP D>

Anyone using the spread sheet may scroll up or down to find their area of interest or "for example if he clicks the "Group C" button he will instantly be taken to line A505. If that person had clicked the "Group D" button; the cursor would have moved to A835. If additional lines were added, the Excel program would remember the location of the first line of each group and adjust the "jumps" acordingly.

I hope that this makes sense.

Thanks for your help.

 

[SkipVought]

Ther must be some value or values (in column(s)) that can identify each group.

For instance in column B you could have Group A in 30 to 255.

What is in between each of your groups (256 - 259, 290 - 504...)?

Skip,

_[red][/red]

 

[Sherman6789]

You are correct Skip. Between each group are columns of data and rows of information containing data and approved amounts. The columns expand over to column Q. We expect it to expand into the double alphas soon. Some of the rows used by some groups are as many as 300 rows. That is why we want to avoid the scrolling methods, if possible.

The beginning line of each group has a row printed with a brown background with white type. Maybe the first row column "A" can be identified by the group name. Also, if additional lines are added between the groups will the identification be updated automatically or will I have to modify the the begin and end lines?

I am reading about hyperlinks with named ranges that "CBasicAsslember" mentioned above. As you can see, I am new at this.

Thanks for everyone's help.

 

[SkipVought]

You are not dealing with data that is in TABLE format. This makes your job much more difficult than if your data were tabular.

Why is that in between data there?

How is that in between data used?

It seem like a really bad funky design to me.

Skip,

_[red][/red]

 

[CBasicAsslember]

The beginning line of each group has a row printed with a brown background with white type.

Use those for your named ranges. Put in one named range for each group. Click on a cell at the top where you want your hyperlinks.
Right click and select "Hyperlink".
Click on "Place in this Document".
Click on "Defined Names" and select the name associated with where you want to link.

If you add or subtract rows the hyperlinks will adjust to the new location.

 

[Sherman6789]

Thanks for your comments Skip Vought.

Sometimes we have no choice but to do the best we can after being given instructions. I personally think that the entire information should be put in a database. However, the users are all use to Excel Spreadsheets and feel comfortable using them. During the coming holidays, I plan to design a database that might replace this but in the meantime, I have to give them what they want and try to make their use as easy as possible. I don't have the liberty to tell the directors, vice-presidents and department heads that their design of the spreadsheet is a "really bad funky design".

The data is in table format with automatic calculations, sub totals, grand totals, etc. The only concerns that I had at this time was to try to find an easy way to jump to certain areas quickly with buttons or something similar. The information is useful to the users and to the head office. Each area uses the information as they need to. At the same time, the head office is able to obtain the information it needs from all of the groups at one time. The variouse groups also need to see each other's information for compatibility. The spreadsheet also total the information for all conceerned. Some of the fields, rows and columns & are password protected for use by the accountant. Other field areas have various passwords for their group use. All of the data is available for everyone to see and for use in making reports, orders, etc.

I will use the suggestion from "CBasicAsslember" for this project. The suggestion from Dale Watson will come in handy for another project that I am currently working on. There is a 60% chance that I may be able to convenience the bosses to let me convert this into a database in the near future.

Thanks again everyone for your assistance.