Dynamic View Of Workbook

Aug 16, 2006

I have 1 workbook which summerizes 3 other workbooks. My question is, is there a way that if I hide a column(s) in 1 workbook (by either using Hide or Outline), the corresponding column(s) will also automatically be hidden in the Summery workbook?

Or, is there a way that I can make the Summery Workbook only display / Link to cells that is been displayed in the source workbooks? I hope to do this dynamically, so if something is hidden by Outline or Hide in one of the workbook, the affected cells in the Summary workbook will also go in hiding.

I am hoping to do this by some magical Excel Function, if not then by VBA (I'm not that good a VBA programmer), if not even that, then maybe some free Excel Tool someone knows?

View 6 Replies


ADVERTISEMENT

Dynamic Autofiltering: Top Level View Of Incidents

Oct 4, 2005

I have a spreadsheet which I distribute weekly to certain people.
It is basically a top level view of incidents, which shows the following.

Sheet Number 1 is :
Person - Count
Joe Bloggs - 42
Fred Bloggs - 12

And so on.

This shows me a summary of how many incidents per person have been logged.

Sheet Number 2 is the actual raw data for that count, so it shows every line.
So in the above example, there would be 42 lines for Joe and 12 for Fred.

What I'd like is to put a hyperlink on sheet 1, whereby if you click onto Fred's name, it will take you to sheet 2 and autofilter to only show Fred's records (i.e 12 of them)

View 9 Replies View Related

Set The View Default On All Sheets In A Workbook?

Dec 22, 2009

I have an excel document with about 7 worksheets in it. The users of these worksheets can save the document but they never save it with the page view starting from the top. How can i ensure that whenever this document is open the default view for all the contained worksheets is at the top (ie:cell a1).

View 2 Replies View Related

Personal View Of Shared Workbook?

Nov 8, 2011

Is there a way to setup personal views of a shared workbook ? For eg : I might hide columns f to j and the other person might have to hide columns h to m. Also, one person might be using ver 2003 and other person using 2010.

View 2 Replies View Related

Have 2 Workbooks, Want To Hide Data Workbook From View

Feb 6, 2008

I have two workbooks that are used in conjunction with each other.

One is a template that creates a quote, the other is a workbook that has data which is used to populate the newly created quote. The data workbook is opened and closed by the opening and closing of the template. But beside the need for the user to see the data when they are selecting which items to add to the quote, there is no other reason for them to see it on the "View", "Switch Windows" for you 07 users, can't recall 03, or on the "Windows Status bar" at the bottom/side or where ever it's placed.

Is there anyway to hide this from the user, and or prevent the user from saving any changes on the data workbook.

Came across this code on MSDN but not sure how to incorporate it or if it's even applicable. [url]

Visual Basic (Declaration)

< BrowsableAttribute(False)> _
Public Property IsAddin As Boolean

Visual Basic (Usage)

Dim instance As Workbook
Dim value As Boolean

value = instance.IsAddin

instance.IsAddin = value

View 9 Replies View Related

Reset View To Normal On Workbook Close

Mar 6, 2007

I have a workbook that, when opened, sets the view to full screen. When it is closed, I want the view reset to normal.

The problem is that the prompt to close full screen appears rather than the view automatically resetting to normal.

What additional codes is needed to fully automate this?

I have:

ActiveWorkbook.Save
Application.Workbooks("PCAR Log.xls").Close (0)
Application.DisplayFullScreen = False

View 4 Replies View Related

Creating Master Sorting And Improving Workbook View

Jul 31, 2012

I have an excel file as attached. I have two departments Digital and Industrial. Under this I have done a drop down option for different quarter for different projects.

When I open my excel file I just want to see the Quarter column under Digital but when I double click on Digital all other columns appear.

Second this is it possible to create a master filter or sorting for Quarter above the table so that if I select Q1 in it it will displays all the project and corresponding values in both Digital and Industrial.

Attachment 47491A.xlsx

View 4 Replies View Related

Excel 2010 :: View Same Workbook On Dual Monitors

Apr 9, 2014

I believe that someone showed me once that you could view the same workbook on two monitors with the vien arrange all mode, you could make changes on both screens and the changes would be saved to the same file similar to arranging two views. I cannot rememebr how to do this or even if it is possible.

View 5 Replies View Related

Excel Workbook Protect - View And Hide Option

Apr 18, 2013

I have hide workbook in view and hide option, when i unhide the same it should ask password. Is it possible.

View 1 Replies View Related

Dynamic Workbook Within A Formula.: Look Up A Value Within A Seperate Workbook

Mar 27, 2009

I am creating a formula - that will look up a value within a seperate workbook. I want the workbook reference to change based on a cell which contains the date.

So workbook name is "Report 120309.xls"
Cell: A1 contains the date "120309"

Current formula ='[Report 120309.xls]Sheet 1'!$F$50. Can I replace the data within the formula with the date in the cell.

View 2 Replies View Related

Excel 2007 :: How To Adjust Protected View Settings But Protected View Tab Is Missing From Trust Centre Settings

Dec 12, 2013

Excel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.

View 3 Replies View Related

Dynamic Functionality In Workbook

Mar 8, 2014

I have sheet1 along with three columns as below

Result column will show the result after sum ** column A & B

[Code] ......

This is simple that i will add this formula =A2+B2 in Result column cell C2.

But i want to perform this addition action on Sheet2 and return the result in column C2 ** Sheet1.

What formula should be populate in Result column ** Sheet1 that could catch addition formula from Sheet2 dynamically and display the result ** column A & B in Sheet1 .

And what type ** formula should be use in Sheet2 to add two columns dynamically.

The same concept that we are using in any functions like as below:

Sub Function Plus (value1 As Double, value2 As Double)
Plus = (value1 + value)
End Sub

View 3 Replies View Related

VBA / Dynamic Workbook Name Reference

Feb 6, 2014

I am using:

Code:
Dim Aname As String
Aname = ActiveWorkbook.ActiveSheet.Range("D2").Value & "Pricing"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Aname & ".xls"

to create a new workbook. I need to copy 2 columns from the original workbook/worksheet to the one being created though. I can get the the original easy enough, but then getting back to the newly created book is proving difficult.

(the 2 columns being copied are filtered to what is needed and I have used the below to copy it, yes I know that is 3 rows but I couldnt get it to copy just E and G at the same time to the lastrow without including F)

Code:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("E2:G" & LastRow)
.Select.copy
End With

View 7 Replies View Related

Dynamic Range In Another Workbook

Aug 23, 2006

I am facing a problem in using a dynamic range(name) defined in a different workbook, when that workbook in not open. I have defined a dynamic range, 'complete' , in Main Assumptions.xls as shown below

= offset( sheet1!$A$16, 0,0, counta(sheet1!$A:$A)-6, 5)

I am trying to use 'complete' in another workbook, RCL.xls, as shown below.

=VLOOKUP(C11, 'C:Chein[Main Assumptions.xls]Sheet1'!complete,5)

This works well as long as Main Assumputions.xls and RCL.xls are open. However, When Main Assumptions.xls is closed and I try to open RCL.xls, I get a warning saying, Excel can't find 'complete' in Main Assumptions.xls. There are two possible reasons: 1. the name you specified may not be defined. 2. The name you specified is defined as as something other than a rectangular cell reference. Does that mean that I can't use dynamic ranges defined, in other workbooks?

View 3 Replies View Related

Merge Dynamic Data To Master Workbook

Aug 11, 2009

is to have a folder where I drop multiple workbooks from different stores. There can be 1 or more workbooks at any given time in that folder. Each workbook is a different size as far as the information inside. Next I need to transfer certain columns to the Master Workbook and also have the store location added to the Master workbook (File Name). I have highlighted the store information that needs to be merged to the master, Store 1, Store 2. For reference I have attached a copy of the Main Folder which is on my desktop C:Documents and SettingsDesktopInventory.

View 5 Replies View Related

Using INDEX With Dynamic Workbook Name - How To Format Name Portion

Jun 29, 2013

I have a range of workbooks that I need to access when closed. I therefore want to use INDEX rather than INDIRECT.

This non-dynamic INDEX reference works perfectly:

=INDEX('[AAA 100.xlsx]Sheet 1'!$A$1:$F$20;1;1)

In the future, however, there will be a lot of reference files and both portions of the name will change - I might have ABC 123, DEF 345, etc. These name portions will be stored in cells. Cell A19 might contain "AAA" and Cell A20 might contain "100".

I am trying in my ignorance to get something like this to work:

=INDEX("'["&A19&" "&A20&".xlsx]Sheet 1'!$A$1:$F$20";1;1)

But I just get "#VALUE!".

Stepping through the evaluation, it seems the final value to be evaluated is this:

INDEX("'[AAA 100.xlsx]Sheet 1'!$A$1:$F$20";1;1)

The outermost double quotes therefore seem to be causing the problem, but if I remove them I get "#REF!".

View 4 Replies View Related

Add Dynamic Hyperlink To Separate Worksheet In Same Workbook

Oct 24, 2013

I have read a number of posts and other sites and can create the hyperlink in the desired cell but get an error message to the effect, "Cannot open the workbook."

The current macro follows. I believe its description is made in the comment statements below the date-author comment.

Sub DecomposeCTQ()
'Decompose CTQ Macro
'14Oct13 Charles T. Carroll'
'This macro copies the template to a new worksheet and takes data from the'
' active cell CTQ and makes it the parent CTQ on the new worksheet. Then'
' it renames the new worksheet with the active cell CTQ ID.'
If ActiveCell.Column 2 Then
MsgBox "You must be in the CTQ ID Column to run this program"
GoTo Leave

[code]......

View 4 Replies View Related

Extract Dynamic Data From Closed Workbook

May 11, 2007

i have this code that could extract out Data from many closed workbook. I am extracting data from 2-3 thousand files and the Data in those files are rather dynamically positioned.

For e.g. My vba codings will extract data from cell B256 of the closed workbook.

Sometimes, the data might not start from that cell which my vba would still extract that non-relevant data.

Is there anyway i can modify the codings to search for the required data and extract instead of extracting it dumbly?

Sub getINFO()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim rCell As Range

View 9 Replies View Related

External Workbook Link With Dynamic Cell Reference

Feb 24, 2014

I currently have a file that is linked to an external workbook that is referencing a certain cell from a certain tab. The below is what Column A & B look like right now. What I'm wanting to know is is there a way to have the formula in Column B automatically reference the name in Column A (which is the tab name in the external workbook) instead of having to manually change the formula when the value in Column A changes?

In other words, I want everything in the external reference formula to remain the same except have the person's name change dynamically...

Mary
='X:Anderson CorporationEmployees[Work Hours]Mary'!$F$42

Joe
='X:Anderson CorporationEmployees[Work Hours]Joe'!$F$42

Frank
='X:Anderson CorporationEmployees[Work Hours]Frank'!$F$42

Michelle
='X:Anderson CorporationEmployees[Work Hours]Michelle'!$F$42

Sam
='X:Anderson CorporationEmployees[Work Hours]Sam'!$F$42

View 1 Replies View Related

Update Worksheets From Another Workbook Housing Dynamic Data

Apr 20, 2009

I have a weekly report which lists items and their available date. This list is always changing - new items added, old items removed. These items are required for 4 different projects and the item listed says which project the item date is for. There is also the same item across different projects which also have different dates which also appear and disappear each week depending on requirements.

I want to be able to capture the date for each of the items per project per week. Ultimately, I want to be able to track the movement of the date per item per project. I have attached a sample of how I want it to do.

View 5 Replies View Related

Delete Rows That Contain Data From User Defined Dynamic List Within Workbook

Oct 7, 2012

I have a financial dataset which I need to "clean" before manipulating/analysing.

Each row of the data represents a completed transaction and the first step is to delete rows that are done with particular (internal) clients; the client data (the client's name) is in Column D.

Currently, I delete the unwanted data by autofilter and delete (code below), however this means the clients to be deleted are only defined within the code. I would prefer to have a worksheet within the workbook where the user defines the clients by adding or subtracting their name from a list.

I have created a dynamic range for the client list by inserting a Named variable along the lines of "=OFFSET(!$B$3,1,0,COUNTA($B$3:$B$200)-1," which works fine.

However how do I work this list into my code so that it works when new clients are added or deleted?

Current code for deleting unwanted client data:

Sub filterdelete()

Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count

With Range(Range("D1"), Range("D" & LastRow))

[Code] .....

View 2 Replies View Related

Macro To Copy Dynamic Sheet In New Workbook - Save As Temp / Mail And Delete

Sep 15, 2014

Code to copy the sheet that get selected by if-then-elseif statement and paste in a new workbook, save the workbook as temp, email it and delete it,

sub sending_mail

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim DataWB As Worksheet

[Code] ......

View 2 Replies View Related

How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

View 9 Replies View Related

Dynamic Starting Point For Dynamic Named Ranges

Jun 21, 2009

I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.

View 2 Replies View Related

Dynamic Data Validation :: Dynamic Lists

Jun 24, 2009

I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-

1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection

Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?

View 5 Replies View Related

How To Create Dynamic List Within Dynamic Table

Oct 31, 2013

I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.

I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".

I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.

Is there a way to make dynamic drop downs within a dynamic table?

View 1 Replies View Related

View But Not Print Out

Aug 15, 2009

if there's a way to either define what columns or rows are printable or to somehow leave columns (or rows) as visible within the print area but omitted from printing as if they were hidden.

In my CAD package I can simply elect to have certain collections of information "non-printable", is there any way I can do this with columns without having to rely on people managing to laboriously (sic) hide and unhide columns either side of printing a document.

View 8 Replies View Related

View A Worksheet With VBA

Feb 8, 2010

I've design a small little excell program that brings up a form and hides the excel workbook upon opening. By using the form the user is able to input data to a worksheet in running in the background. I've added a button on the form where I want the user to be able to open the worksheet which is password protected and view or print the data. I've already got the code to hide the workbook, protect and unprotect the sheets, I just can seem to get the code to view the one worksheet.

View 3 Replies View Related

View Locked Add-Ins

Sep 18, 2002

In keeping with their monopoly on code, Microsoft has, of course, locked all of the included XL add-ins (the code part).

I was interested in looking at how some of these add-ins functioned, seems like it could be fairly educational.

how to unlock the modules that come with XL's add-ins?

View 9 Replies View Related

Changing The View

Feb 17, 2007

i was wondering how i can get excel to hide everything except for the worksheet, as soon as it starts and then put it all back when you quit.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved