I am trying to build a sheet that contains a table of contents at the top, followed by tables as one scrolls down in the same sheet. What I would like to have to the side of each line in the table of contents is the page number of the table that line is referring to. Is there a formula or a function I can use that will dynamically retrieve the page number of a particular table?
View 3 Replies
I'm trying to have a repeating cell automatically update the page number and number of pages, much the same as in a header/footer situation, but I need it to be done in 1 cell.
View 2 Replies View RelatedI have a workbook that is now up to 40+ sheets. I'm adding a contents sheet to aid navigation around the workbook.
Every detail page now has a hyperlink inserted called "Home" that simply refers back to the contents sheet and takes me to the contents sheet when I click the link. So far so good.
On the contents sheet, I'll have 40 hyperlinks, each referring to a sheet in the workbook. I want each one to have the name of the sheet displayed, so if my sheets are called sheet1, sheet2, sheet3, etc. then the text displayed on the hyperlinks on the contents sheet are also sheet1, sheet2, sheet3, etc. That's easy as I can just type the sheet name in the "text to display" field in the Insert Hyperlink dialog.
Here's the tough bit.
The names of my sheets change regularly; I want the text displayed for the hyperlink to change automatically so that if sheet3 is renamed to sheet33, the text displayed for the hyperlink also changes to sheet33. Is there a formula that just returns the name of a referenced sheet?
running excel 2000
I have produced a table of contents using the following suggested code:- [URL] .....
Is there a way of getting the contents page to exclude the first three tabs of the work book.
At the moment my table of contents is including the cover sheet and table of contents.
I would like to be able to display all of the sheets in a workbook as a list on the front sheet.
I have a front "Contents" Sheet (attached at the bottom of my post)
In the "Ticket No." column I wanted to have the sheet name, in the "Date" and "Description" column I wanted to display cell values from each sheet; which would be the date and description of that sheet/"Ticket". The "Resolved?" column I will be able to do after I have this part figured out.
The workbook is also attached :
Attached Image : contents.jpg
Attached Files : Troubleshooting Tickets.xlsx
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
View 3 Replies View RelatedI've a Pivot Table which i would like to get a list of it.
It is from an OLAP cube from Oracle
Based on the image below, every month i refresh this PT, there will be an additional WK. Lets say WK22.
How do i export the whole list from WK08 to WK21 everytime after i refresh my PT?
How do i select the last item in the Page field? If there is WK22, den it will auto select WK22. If not WK21.
I have defined 2 pivot table report using the same master data. I wanted to default the page field in these reports. I notice that on refreshing the data, the pivot table page field updates with "All" instead of default one (if there is no data for the default page field). How do I fix this? I wanted to have the default page even though there is no data for that field in the master file.
View 5 Replies View RelatedI know in Pivot tbale we could add calculated field into the data ,but I fail to put the added in the "Page","Row" or the "Column". Eg. in the sheet of data,I have a column " DATE",but no column for year.Is it possible to add year in the "page" without adding anything into the "data" sheet?
View 5 Replies View RelatedI have a table of contents page. The TOC is updated each time some one opens the sheet (this is a must have based on the requirements). I'm trying to include some statistics next to each item in the TOC. These statistics are on each worksheet and just need to be copied to the TOC.
Now my problem.
The worksheets are NOT keep in alphabetical order, so After the TOC is created a sort is done to put the TOC links in alphabetical order. After the order is set, I need a formula that will read the worksheet name from a cell on the TOC (which is really a hyper link to a worksheet).
I have this already:
ActiveCell.FormulaR1C1 = "=Address!R[-2]C[-2]"
If "=Address!" (Where address is the name of one of the sheets) could be replaced with a cell reference that (I think) would do the trick.
My TOC is a MACRO not a cell formula so if this can not be done with a cell formula but can be done through VBA that would be fine.
I am attempting to build a table of contents and add hyperlink to each entry
as suggested by dynamic sheet index
The TOC is constructed properly, but when I click on the hyperlink I receive an error message:
" Reference is not valid"
My code (courtesy Jabax):
Sub Create_TOC()
Dim wb As Workbook
Dim wsTOC As Worksheet
Dim ws As Worksheet
Dim r As Long
Set wb = ThisWorkbook
Set wsTOC = wb.Worksheets("TOC")
' Turn the next two lines of code on and off as preferred
' Ans = MsgBox("Do you want to update the Index?", 4)
' If Ans = vbNo Then Exit Sub
With Application
. ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With...
Is there any way how you can change value in page field in pivot table using formulas? I know this can be done with couple of lines of VBA, but I need to create VBA-free excel file.
View 4 Replies View RelatedI have created a system where users input data from a report into a spreadsheet, and after input it is sent off to various other workbooks depending on what was entered. I.e. The user is inputting test data on a location in a region. The regions make up the workbooks, and each location is a worksheet (While I can think of better ways to organise the data changing this is not an option here).
When the macro encounters a location in a region that hasn't been entered before it adds a new sheet based off the name of the location in the region's workbook, and creates a new named range for that sheet.
I'm running a pivot table in the central workbook, and I want to add the new named range to the page field when I create a new sheet. This is what I don't know how to do. EDIT: To clarify, the page field is choosing between which worksheet(via named range) is being shown currently.
I want to add a button on my Worksheet that will basically copy the last portion of the sheet (about 40 lines) and insert it at the bottom of the sheet (above the button). Each time the button is clicked, the last 40 lines will be copied and pasted. The script also sets the page break to the appropriate line. To achieve this, I've set up a hidden sheet that contains the line numbers and last page number. The script accrues these numbers every time the script runs.
I realise there may be a better way to do this, but at this point I'm trying to stick to what I know. I'm trying to use the function Row().Select, and I keep getting an Error 13. Example of my
Sub AddPageTest()
Sheets("SetPoint").Select
Dim CopyRows As String
CopyRows = Range("C13").Select
Dim InsertRow As String
InsertRow = Range("C14").Select
Dim SelRange As String
SelRange = Range("C12").Select
Dim LastPageNum As Integer
LastPageNum = Range("C5").Select
Dim NewLastPageNum As Integer
NewLastPageNum = LastPageNum + 1
Dim LastPageBreak As String
LastPageBreak = Range("C15").Select
Dim NextPageFirstRow As Integer
NextPageFirstRow = Range("C4").Select + 1...................
I have converted a table from PDF to Excel format....
The table consists of longf columns with rows of data in each column.
Once converted to Excel format, the columns become single cells with say 100 rows in each cell.
My question is this:Can I reformat these larger cells such that each row within the long single cell gets its own single cell.
This would enable me to copy paste the data into a spreadsheet and avoid the data entry....
I have to review work papers with 50+ sheets. The users often hide the sheets they do not use. As part of my review I have to review all sheets, even the hidden ones.
What I would like to achieve:
To create a list of all hidden sheets with hyperlinks so I can go directly to the hidden sheet. This would be the same as a table of contents but for hidden sheets.
i have pivot table that has a field called "supp" is it possible to write a macro that will open up the "supp" drop down box select the first result .print the results of the pivot table. then goto the next selection in the same drop down box and print them results . repeat this until allresults have been printed.
when the pivot table is run weekly the results in the field "supp" will change
in my Pivot Tables page field i could have 20 results. 10 could be customer identification codes
5 material codes, 5 a different material code, i called "inter", and the rest supplier codes, example i05,i05/1,i05/2, fo1,f01/1,f01/2 are both material codes,
what i want to do is have a macro /macros to select and print groups
as follows
1. (all)
2. inter
3 any containing the words i05 and f01 which includes i05/1 etc
4. then the rest
if any does not exsist ignor. if possilbe a macro for each or a drop down box to select
I want to print out, for a range of 'Page Fields', from a Pivot table automatically.
The Macro must be able to:
Automatically go through and select the 'Page Fields' in the pivot table
Select the print area range automatically, which will change each time the pivot is refreshed/reselected.
Set the print area to fit the page for each category selected in the pivot table
Finally print.
Can't find code for this anywhere, but i must admit got fed up with looking
I'm doing some extremely repetitive work with a web tool at work. I need to import about 1800 tables for my first assignment and 5000 for my second. I did a search on the forum and found out that Excel and vba is able to import tables from a website's chart which is amazing. I recorded a macro and got
Sheets("Sheet2").Select
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL I can;t give out this information" _
, Destination:=Range("A1"))
.Name = "index"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False.....................
For each session I have to go to my site, log in with a username and password which is the same everytime then go to a specific page select the right drop down and import chart. The Chart under the drop down is the chart I need to import. The macro I recorded has no problem doing this but however it keeps grabbing the same chart, how do i fix that, so it knows to select the right drop down then import the right chart?
How do I clear the pivot table formatting? I want just the content of the pivot table, but can't figure out how to get rid of the formatting?
View 2 Replies View RelatedAs I mentioned in my other thread, I have a spreadsheet with 9 worksheets and 200+ charts in individual tabs. As you can imagine, searching back and forth across 200+ tabs is a bear. While I have used Tab Colors to help, it's still way too annoying.
Sadly, each chart must be in its own tab in order to ensure that the links to PowerPoint and Word continue to work. Unless I'm missing something, individual tabs are the only thing that have worked for me.
I recently found a macro that creates a TOC for worksheets, but it didn't include chart tabs. Is there such a thing, or is there a way to creat such a TOC manually?
I am trying to create a drop down list for a table of contents that can link off to other pages within the same work book since it is very large and difficult to navigate through. I can create dropdowns were the lists it draws from is hyperlinks and that doesn't do anything special. It is just like drawing a regular list. I want the drop down when I click on something, to link me over to the other sheet.
View 7 Replies View RelatedI have a spreadsheet with macros and conditional formatting assigned to it. What I want is to be able to copy only the displayed contents, without the macros and formatting instruction, to a separate workbook for reporting purposes.
View 2 Replies View RelatedCan I do something so my pivot table shows Manager name in all appropriate cells (eg. agomes is A3:A4 and bschaefe in cells B5:B13)? ........
View 9 Replies View RelatedI have linked a graph to update from a pivot table. I would like to print a copy of a graph and pivot table for each item in the page field.
Can a macro loop through each page field and print?Is there a way to send them to the printer all at once? (I noticed the printer hangs when manually printing page by page)
I've tried "view all pages of pivottable" but then I lose the view of the chart. I tried recording the events but the number of items in the page field changes with each data update and I'm not sure how to write code to accept this.
I want to print one sheet for each of the values in a page field drop-down. I can do this by supplying the values individually (code below) but would rather determine the list of values dynamically. Can this be done?
Private Sub pbPrintAll_Click()
Dim cix As Integer
Dim Ctrct As String
cix = 3
While (Sheets("Database"). Range("B" + Trim(Str(cix))).Value <> "")
' Get value from database sheet
Ctrct = Sheets("Database").Range("B" + Trim(Str(cix))).Value
' Set CurrentPage value
Sheets("Customer P&L Pivot1"). PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = Ctrct
' Print formatted sheet
Sheets("Customer P&L").PrintOut
cix = cix + 1
Wend
' Reset Current Page value
Sheets("Customer P&L Pivot1").PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = "(All)"
MsgBox "Prints sent to printer."
End Sub
I have a "Generate Table of Contents" button that launches a macro. This macro builds the Table of Contents, creates (3) columns (Patient Name, Date of Pickup, and Frequency), finds all of the sheets in the work book (already renamed to a patient's name), sorts them alphabetically, and lists them under the Patient Name column.
This code works great - The next challenge:
On each sheet (which corresponds to a patient in the database), there is a cell that is filled in that states which day the patient picks up their medication. There is another cell that designates how often they come to pick it up.
I am at a loss as to how to direct the macro to: For each sheet, go to the specific cell, and then report it in the table of contents.
It somehow needs to bind the information together... meaning the patient name, date of pickup, and frequency must be displayed correctly each and every time on the same row.
Here is what I am trying to achieve:
I have a workbook with two tabs, on the first one there is one column and the top of the column(A1) is a dropdown containing the options 'Boy' or 'Girl'.
One the second worksheet I have two named tables (Boy and Girl) - these tables each contain a single column array with 5 names in each (so we have a table of 5 boys names and a table of 5 girls names)
What I want to happen is when a user selects an option from the dropdown in A1 the cell below(A2) is populated randomly with a name from the corresponding list.
I have achieved this initially using the following formula:
=IF(A1="Boy", INDEX(Boy,RandInt(1,10)), INDEX(Girl,RandInt(1,10)))
This works fine, but I will need to expand this for several dropdown options, so my new workbook will have a dropdown of car makes, and each make will have a list of the models associatied with the car make (e.g. Ford: Fiesta, Escort, Mondeo...)
But I do not want to have a ridiculously long nested IF statement for every car Make, so what I want to do is something along the lines of the following:
=INDEX([CONTENTS OF A1],RandInt(1,10))
So I use the text selected from the dropdown in A1 as the TableName in my formula - however, I cannot retrieve that to use in the formula without it coming back as a text with the "" included and my formula errors!
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
