Table Of Contents That Will Include Chart Tabs
Apr 27, 2009
As 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?
View 5 Replies
ADVERTISEMENT
Nov 30, 2009
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:
1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case
2. the ToC has to change depending on the sheets selected.
I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.
What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
View 13 Replies
View Related
May 29, 2014
I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.
I found the following formula which, when used as a named range, automatically extends a list for data validation:
[Code] ....
I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?
View 2 Replies
View Related
Apr 3, 2009
I have 4 tabs, The first tab has title TOTAL. Tab 2, 3 and 4 are filled. Kolom A are filled with values (TEXT). I want a command that in tab 1 all the contents are shown from tab 2 till tab 4.
View 7 Replies
View Related
Apr 11, 2012
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.
View 4 Replies
View Related
Nov 23, 2008
Is it possible for the tab name to auto populate from a specific cells contents?
View 9 Replies
View Related
Nov 10, 2009
I have 5 sheets. In the first sheet I have set up 4 cells where I want the contents of them to automatically rename the other 4 corresponing sheets.
Eg.
In Sheet 1, Cell B6, I want the contents (which will be text) to be the name of sheet 2 automatically.
In Sheet 1, Cell B7, I want the contents (which will be text) to be the name of sheet 3 automatically.
View 8 Replies
View Related
Feb 6, 2008
I have an existing pivot table that is feeding from data in another sheet.
The data gets updated every month and sometimes their are more columns of data than the previous month. Everytime there are more columns they are not added into the pivot table when i refresh the table even though they are included in the range.
Is there are way the pivot table will add these new columns when i refresh the table without me having to manually drag them in?
View 9 Replies
View Related
Jun 10, 2008
I have a workbook that automatically creates a new sheet based on the current day. There is a pivot table in the workbook that gets values from multiple consolidation ranges.
Right now I have to manually add the ranges from the new sheet every time. I need that pivot table to update with the same ranges from the new sheet every time a new sheet is created.
There is also another sheet (named "Data Results") that needs to update too, but that's not as urgent.
I've searched everywhere on the forums and can't find anything like that. When the file opens, I've written script to have a box pop up asking whether you want to "View Data" or "Begin New Audit." If you click "View Data" nothing happens and you can just view the file. If you click "Begin New Audit" it creates a new sheet based on the current date.
I wanted to attach the file, but it's an Excel 2007 macro-enabled file (*.xlsm).
View 4 Replies
View Related
Nov 30, 2011
I am working with Excel 2007 and have an occurring problem when I resize a table. Some of my forumlas copy to the new rows, while others do not. How can I ensure all my formulas copy to the newly created rows. Also sometime when they do copy to the new row an #REF! in the formula where the range should be , yet if I copy and paste the formula from the above row it works.
Here is an example of one of my Formulas Note Sheet1!$J$3:$J$500 is also a table Called Data
=SUMPRODUCT((Sheet1!$J$3:$J$500)*(Sheet1!$A$3:$A$500=$A14&DP$4)) and this is how it is inserted into a new row
=SUMPRODUCT((Sheet1!#REF!)*(Sheet1!#REF!=$A15&DP$4))
View 1 Replies
View Related
Jun 7, 2012
I have a table in which the rows represent cities and the columns represent certain characteristics/ attributes a city can have. In the cells every city & attribute pair receives a "grade"
City EatingBeachSki
Barcelona 210
Chamonix 002
I would like to transform this so that every row represents a city & attribute pair and includes its corresponding grade.(I want to do this in order to be able to add more information about city- attribute pairs rather than about cities only)
City AttributeGrade
BarcelonaEating 2
BarcelonaBeach 1
BarcelonaSki 0
ChamonixEating 0
ChamonixBeach 0
ChamonixSki 2
I am aware that I can use a pivot table to sort out all the pairs with a specific grade. and then I could paste this into a new table. But my data base (number of cities x number of attributes) is huge and I was wondering if there is no automatic process to make this transformation.
View 4 Replies
View Related
Mar 15, 2012
I have looked and experimented on how to include multiple ranges on a pivot table in excel 2011, i have found how to to it with the wizard but i can not find the wizard.
How to separate the ranges in the selection box? They are going to be going across sheet is that makes any difference.
View 4 Replies
View Related
Apr 2, 2014
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
View 10 Replies
View Related
Sep 9, 2009
I need to create a chart that will show month over month sales for different channels. But each month total is in a separated tab in the same file.
Example:
August totals
Channel Sales
Channel 1 $1000
Channel 2 $2000
Channel 3 $500
I need a chart that will show sales for Channel 1 ,2 and 3 from jan to aug.
View 8 Replies
View Related
May 6, 2014
I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)
How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.
View 1 Replies
View Related
Apr 29, 2014
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
View 2 Replies
View Related
Jan 10, 2014
I am trying to make the contents of a cell into a chart title.I know I can do it like this...
ActiveChart.ChartTitle.Select
Selection.Caption = "=tab_name!R2C1"
However, I am trying to use the same macro on several different tabs in the same EXCEL worksheet. Obviously, I can't have the tab name in the macro. But, I don't get a title at all when I try to remove it like this...
ActiveChart.ChartTitle.Select
Selection.Caption = "=R2C1"
or
ActiveChart.ChartTitle.Select
Selection.Caption = "=A2"
Is this possible? I am (kinda new at) using EXCEL2010.
View 3 Replies
View Related
Jan 18, 2014
In the annual table tab, I want to be able to select a sales person or other field, and create a table that will pull data from all the tabs. For example, if I select the salesperson Kelly, I want it to pull all of the data from January, February, and March, and compile it into a table.
I would want the table to be dynamic also - I want to be able to quickly sum/average all of that salesperson's sales for all months.
View 4 Replies
View Related
Aug 22, 2007
Good Afternoon. I am attempting to create a pivot table using 4 columns from 5 different tabs. I have created pivot tables before using one tab and they are pretty easy. However, I have been working on this for over an hour and I am getting nowhere. Can someone tell me how to put all five tabs into one pivot table? The columns are all in the same order on each tab. They are as follows: System, Management Area, Schedule Date, WO Type. I want the pivot table to have System and Management area on the left side, date on the top row and counting the number of WO Types.
View 6 Replies
View Related
Dec 6, 2012
I am trying to populate a large data table with data sourced from multiple tabs.
Each of the tabs is, for the most part uniform.
They have column labels of "Invoice Number", "Schedule Dates," and "Amount". Their cell references are A6, B6, and C6, respectively.
Is there any way to congregate this data into one massive data table? It doesn't matter the order of the data table. I will be using sort/sumifs/pivot tables to analyze the data.
I cannot copy and paste as there is too much data that changes on a daily basis.
View 1 Replies
View Related
Jul 12, 2012
I am working with a large table of quite simple data in the following format.
Ref1 SurnameInitialsDOL1 Reference2
Zz719147GLAWSJ07/04/19780271947X
YA414253CDORCE07/04/19870282179F
I need to split up the "master" table so that for all rows where DOL1 falls with a year range of e.g 6th April - 5th April (in any year) then this row is moved or copied into a new tab based on the year range.
i.e. The first row should appear in a new tab for the range 6th April 1978 - 5th April 1979.
The marco should be ale to create and name new tabs. The tab name will be 78_79.
The second row should appear in the 87/88 tab.
I have several thousand rows of data with dates ranging from 1978 - 2012 so there should be tabs populated for every year from 78-2012.
View 6 Replies
View Related
Nov 17, 2007
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out.
I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook.
I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
View 6 Replies
View Related
Jul 9, 2014
I would like to combine 2 tabs into one spreadsheet. I was not able to do so, because it exceeds the max rows allowed in excel. I have installed powerpivots but not sure how to combine data using powerpivots.
View 3 Replies
View Related
May 16, 2008
I 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.
View 6 Replies
View Related
May 17, 2007
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...
View 5 Replies
View Related
May 27, 2014
I have a macro that enables me to split my master table into separate tabs based on the first column. However, the column widths and the header format is not retained. Is there something I can add to the macro below to keep the formatting?
Code:
Sub DispatchTimeSeriesToSheets() Dim ws As Worksheet
Set ws = Sheets("MasterList")
Dim LastRow As Long
LastRow = Range("A" & ws.Rows.Count).End(xlUp).Row
[Code] ......
View 4 Replies
View Related
Feb 17, 2009
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
View Related
Nov 22, 2011
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....
View 1 Replies
View Related
Jan 3, 2012
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.
View 4 Replies
View Related
Apr 22, 2014
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 Related
