Create A User Friendly Sheet Index For Worksheet Navigation
Nov 21, 2009
Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.
View 6 Replies
ADVERTISEMENT
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
Sep 28, 2006
Im building a Menu list for my customers. I want to do it in XL sheet format. Can i know how to create tabs (like hand should apprear,when cursor come to the tab and should be like button, where we can name it) in the main sheet?. I have lot of innovative Ideas to come with beautiful menu file i need your assistance further to build my menu list.
View 9 Replies
View Related
Mar 8, 2008
I ‘m trying to put a drop down combo box on each sheet (around the top right) of a workbook (20+ sheets). The drop down list will have each sheet in the workbook. Selecting the sheet name in the list will make that sheet the active sheet. I plan to remove the tabs from the window, because only about a third of the tabs display at once, and right clicking to the right of the tabs only displays about 2/3 of the sheets.
When I was searching this topic, I found a post that used AllWorkSheets function to create an array of worksheet names and the procedure adds it to the data validation in cell "A1". I don’t have the same cell available on all the sheets, at least not at the top of the page, so I thought a combo box that can be moved would be a good solution, but I’m open to any suggestions that allows the user to navigate the sheets w/o a macro always running or a form always open.
View 2 Replies
View Related
Jul 14, 2009
I am writing a macro in which the user selects several ranges (entire columns) for the macro to manipulate. I display a userform to the user that contains 16 refedit objects (1 for each range that they picked). Unfortunately, the value that appears in the refedit boxes is not very user-readable. It looks like this:
'[WorkBookName.XLS]Sheet1'!$B$5
I can assume that all of the ranges will reside in the same workbook, so I have been cutting the workbook name portion off as a string and displaying it in 1 place only. I can leave the value of the refedit as: Sheet1'!$B:$B, but even that is not very friendly. Of course if I do change the value of the refedit controls then I will need to "fix" them before attempting to access the range.
Does anyone have a suggestion as to how to display the sheet and column of the range to the user?
View 6 Replies
View Related
Jun 5, 2009
attached is a sample schedule, there is conditional formatting in all of the cells (see graphic) such that if the cells to the left are the same value, then the cell is turned white, and a border is removed.
This gives the appearance of a 'block of time' while which cleans up the appearance of the spreadsheet.
the problem i have is that when we scroll to the RIGHT, we lose track of the 'left most' cell...which is black of course, the similar ones to the right are intentionally whitened out.
a perceived solution would be to change the conditional formatting of the column (actually only the cells in the column we care about) when it is NEXT to the vertical freeze pane so that it appears not in white, but in black font, etc.
the TRICKY part is that we will obviously need to go back and forth, so the columns will need to change formatting often.
or
when the column number (or letter) next to the freeze pane changes, change the formatting to certain cells in that column to an alternate version; and change the formatting of all other certain cells within other columns to original formatting[
View 9 Replies
View Related
Sep 5, 2013
I have a file that contains detailed sales information for each sales person. It typically contains over 20k records and the managers find it difficult to look up their sales people.
Currently it is subtotaled for each sales person but there are about 450 sales people across the country and it's not easy to use.
I have seen other Excel files that use buttons and I am wondering if this might be the solution to this issue. In other words, can I create a button where the managers can just click it, key in their sales person's name and get at the records easier? If so, how does that work? How I could make this file more user friendly.
View 9 Replies
View Related
Aug 16, 2006
Can an index of all worksheets names be created without using VBA?
View 3 Replies
View Related
Apr 9, 2014
Can't seem to attach sample Widgets.xls. I need a macro to take the heading (note heading not sheet name) from each worksheet on a workbook (or from a range of nominated worksheets in case I don't want to index the first or last few sheets) and use it as the Description for a Index entry on a nominated sheet e.g. Sheet named 'Index'. In addition that each Description is also a hyperlink back to the sheet with the heading. In this way users can click between each entry on the index to go to the sheet and then click on the heading on the sheet to go back to the index.
Index Sheet
M21, M22 and M22-A BICYCLES1
Q21, Q22 and Q22-A BICYCLES2
R21, R22 and R22-A BICYCLES3
Taking this one step further I ideally want to save this workbook as a pdf and the hyperlinks to remain.
View 4 Replies
View Related
Jan 7, 2010
Is it possible to create an index of cell data (in my case personal names)? And if so can it be done so that each entry is a button to click to go to the cell?
Would save a lot of manual labour....
View 9 Replies
View Related
Feb 26, 2014
I am trying to place a hyperlink on each worksheet to an Index worksheet. This is what I come up with so far, and it does not work.
Code:
Sub CreateIndexHyperlinks()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Hyperlinks.Add Anchor:=ws.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
Next ws
End Sub
It successfully puts the words "Back to Index" into cell H8 of every worksheet, but this cell does not link back to the Index sheet (which is called "Index"), the way I want it to.
I don't really understand the Address and SubAddress part of the argument for the Hyperlinks.Add method. I am guessing the Address part is "" because I am linking to a place in the same workbook. Is that right?
I tried changing the SubAddress to Worksheets("Index") and Worksheets("Index").Range("A1") but that just resulted in an error message.
View 1 Replies
View Related
Jul 9, 2006
i would like the other tabs to be still visible to the user (so not hidden), but they should not be able to go to those sheets. (clicking on their name should so nothing)
View 9 Replies
View Related
May 13, 2007
I have 2 reports on one worksheet. I have put a button at the top of each report to navigate back and forth. One report is located at a1:S27 - it shows up fine. The issue is the other report located at BA1:BT52. No matter where I assign a name to it the report doesn't come up full page with cell BA1 at the top left hand corner.
View 3 Replies
View Related
Jan 8, 2009
I'm trying to create a formula similar to this:
=Calculations!(Indirect("N"&A2)-Indirect("AB"&A2)+Indirect("AA"&A2)
The idea is that the user will enter a value in cell A2. That number will determine the appropriate rows in the formula above.
View 6 Replies
View Related
Nov 20, 2008
I'm trying to create a dynamic array so that when a user enters the first sheet name via an input box, excel will know to select the other sheets with ending (2), (3), (4) etc.
Currently this code works only if there are 4 sheets present (as I set this way), anything lower/higher is out of range.
How do I make it so that it will select sheets if there are less than 4 sheets present?
I tried nesting, but I pretty sure this array needs redimming or something. Is ubound a better option?
View 5 Replies
View Related
Aug 30, 2006
I'm going to be creating a workbook that contains a worksheet for each year. Within each worksheet, I will have a calendar format that contains a range for each month of the year (multiple columns and multiple rows). Is it possible to name areas based on month and then create a button or drop down list at the top that would allow users to select the month and the view would go to that area within the calendar?
View 2 Replies
View Related
May 1, 2008
Split off from
Dependent/Linked Drop Downs In Cells
Originally Posted by Dave Hawley
Try it now. You hadn't named the cells that the Validation lists are Dependant on. Not bad for a "moron", hey?
Things get pretty stressed around here, especially when your commander depends on you as much as mine, and just between the two of us, it is twice as hard when your commander is younger than you. Let me try it real quick. Auto Merged Post Until 24 Hrs Passes;Ok, that works great, now I have to go back and add all the real units and soldiers. Fun, fun, fun. BTW, you wouldn't happen to know how to add a first worksheet that would have buttons to take you to the different sections? I know how to do the hyperlinks, but I think I read something on here about comboboxes?? If I need to post this somewhere else, then just push me in the right direction. I am use to pushing in the Army, it's our life!!
View 9 Replies
View Related
Oct 31, 2007
situation: 3 rows of data, row 1 has HDR in several cells (always in A1).
task
1. search row one for next example of cell containing HDR, excluding cell A1.
2. IF found, create a new worksheet and move all data below and/or to the right of that cell to the new sheet.
3. Repeat steps two and three until the search does not turn up another HDR except in A1.
View 9 Replies
View Related
Jun 16, 2006
find attached zip file . if you open the file then you can understand the problem.
I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)
Kindly open attahced file i putted the note inside the file.
View 3 Replies
View Related
Oct 25, 2007
I have a spreadsheet with the following headings:
A
Claims Number
B
Name
C
Scheme
D
Admin
E
Date
I need to create a user form that will "pop up" and ask the administrator to enter the above details.
A - should be created automatically (ie last claim number + 1)
B - user enters manually
C - data retrieved from a list
D - data retrieved from a list
E - date is the date the information is entered
View 9 Replies
View Related
Apr 30, 2009
I have a list of data that I'm using the index - match function to output data based on user input.
For example, I have:
1 10
2 15
3 20
So when '1' is selected another cell outputs '10'.
However what I want is for example if 1.25 is input, I want it to interpolate to spit out the correct number between 10 and 15.
View 5 Replies
View Related
Feb 13, 2009
Here is the situation:
On Sheet1:
A
123
456
789
On Sheet2:
D, G
aaa123, 11
bbb456, 22
ccc789, 33
I would like the function to search strings within an array on Sheet2 for a value on Sheet1 (that is, to search for 123 within the strings aaa123, bbb456, ccc789,etc.). Once a match is found, I would like the function to return another value from the same row on Sheet2 that contains the value from Sheet1 (within the string).
Can you help me?
I must use cell references for the values on Sheet1 because I am working with thousands of unique supplier numbers. In addition, all cell formats are general. I prefer to use the INDEX(MATCH combination rather than the VLOOKUP option.
View 8 Replies
View Related
Jul 8, 2014
I would like to create a spreadsheet with 3 columns: employee ID, last name, first name. Then on the 4th column I would like to enter the employee ID and have the last name and first name auto populate into the 5th and 6th columns. How do I do this?
View 14 Replies
View Related
Jan 11, 2007
see attached workbook. I want VBA to insert an index/match forumla on sheet 1 to lookup a value from sheet 2. I don't want it to specify a range though. I want VBA to look to see if there is data above and to the left of the cell and if it is true insert the index/match formula. Then it won't matter what row or column I put the headings in.
View 2 Replies
View Related
Jul 14, 2014
I am trying to create a function using the functions of IF and "And", but for any reason I don't know how to add the Add function
My function written in excel will looks like =IF(AND(A80="00",C80<>"8300"),"yes","no")
LOB Cost Center
00 1000
00 2000
00 8300
01 5000
02 8300
Function Allocation(LOB As Integer, CostCenter as Integer) As String
If LOB = 00 And CostCenter <> 8300 Then
Allocation = "Yes"
ElseIf LOB = 1, 2, 3, 4, 5 Then
Allocation = "No"
End If
End Function
View 3 Replies
View Related
Nov 13, 2008
I have a macro that's running quite well and is cross-posted here:
[url]
What I need to do is allow for user input. I want to create a dialog box that pops up and asks for an input range, when the user runs the macro. Values can only be from 1 - 50. And the user won't be allowed to select broken sequences, like 6, 9, 12-15.
They would have to be continuous, i.e. 6 - 15, etc. I'm still very new to VBA. I can play with the control toolbox, but I have very little understanding of how to interface with the components that I'm adding/creating. Once I have that range, I want to use the inputs as the 'start' and 'stop' points for my loop. So I need to capture the values and pass them as input parameters, I'm guessing.
I can modify the loop to accept the inputs, I'm just not sure how to capture them from a user input dialog box.
View 14 Replies
View Related
Jun 17, 2013
I have a mental block when it comes to writing functions.
In a cell I have the Post Code followed by a "#" sign (no speech marks) and sometimes more text
e.g. FY6 8JF#ABCD (where the Post Code is FY6 8JF)
The "#" sign always indicates where the Post Code ends, i.e. I want to return all the characters to the left of the "#" sign.
In a spreadsheet I would accomplish this with "=LEFT(AA14,FIND("#",AA14,1)-1)"
where the string of interest is in cell "AA14"
How do I write a UDF to do this?
View 5 Replies
View Related
Jan 20, 2012
Using Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.
The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.
Here is an example of the formula:
Code:
=INDEX(Table1891034445678912822253536[#All],
MATCH($I35,Table1891034445678912822253536[L/I],0)+1,
MATCH(L$34,Table1891034445678912822253536[#Headers],0))
Just look at that Table name!!
The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.
Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.
Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?
Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?
View 2 Replies
View Related
Sep 4, 2009
I wish to create a button which will allow the user to goto the page in my spreadsheet.
I no i can use macros or hyperlinks to do but i can only use this button once for it to work. I want to cut and paste the button to save time.
View 10 Replies
View Related
May 18, 2009
I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.
=INDEX(Sheet2!B$20:B$961,MATCH((LARGE(Sheet2!$F$20:$F$961,$R9)),Sheet2!$F$20:$F$961,0))
I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.
View 2 Replies
View Related
