Display Outlines At Different Level

Nov 11, 2006

I created 2 groups of lines (or outlines) with sub-outlines (so I have 2 levels) in the same worksheet.

I would like, via a macro, to display the 1st at level 1 and the 2nd at level 2.

Of course, I tried the "showlevels" command but it puts both groups at the indicated level.

Is there any command to work only on 1 of the 2 groups ?

View 4 Replies


ADVERTISEMENT

Display Macro Security Level Code

Sep 6, 2006

Although not a complete novice, I am fairly new to EXCEL/VBA.
Is there a script that can be embedded into an EXCEL workbook that checks the Macro security of the PC, and if set to HIGH, shows a message that states that "Macro security is too high to open this workbook".
With security set to high, Excel will open my w/ book, but not run any of my checks that are set in the module. So a "catch 22" really, I need the macros to run to that it is being opened on an authorised computer etc, but if security is set to HIGH, then they don't run, similarly, if I put a macro in to check the macro setting, it won't run unless the security is set to medium.

View 9 Replies View Related

Multi-Level Reporting

May 15, 2008

I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.

Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.

View 9 Replies View Related

Multi-Level Folders In VBA?

Apr 19, 2012

I have the following function. the problem with this excel is i give it "C:" as directory, it search only one level of sub folders i.e it will search the filename in

C: est
C: est3

It does not go deeper. i.e it wont look in folder that is for example

C: estanotherlevel
C: est3anotherlevelmylevel3

How to modify my function so that it goes to the deepest folder. is there a way to do it?

Function GetFile(directory As String, filename As String)
Dim FSO As Object
Dim fldr As Object
Dim subfldr As Object
Dim file As Object
Dim fullname As String

[code].....

View 2 Replies View Related

Getting Sheet Level Names - VBA

Jul 24, 2012

How can I obtain the sheet-level name through VBA? I'm not referring to the sheet (tab) name but the name as defined though Insert/Define or Insert/Create, etc .

I know the way to get the cell level name goes something like this:

MsgBox Sheet1.Range("A1").Name.Name

But i don't know the correct syntax for sheet level.

View 4 Replies View Related

Check Memory Level

Feb 10, 2009

I have this memory game I want to set out on the computer. In table A, Row 2 (B2:E2) I (Moti) start off being the first player, where I place a series of different items. (In Example is given letters but can be kitchen article food and so…. Separated by coma) these Items are shown to player Jack, for a few seconds, so he can memorize them and Answer in (B3:E3) ....

View 9 Replies View Related

Sum Depending On Indent Level

Sep 22, 2009

I was wondering if it was possible to do a SUMIF function depending on the indent level, e.g. sum all of the values which have an indent of 2 in the row headings.

View 2 Replies View Related

Bucket Level Chart

Aug 30, 2007

With reference to thr great charts that Andy Pope has made available on his website; specifically the thermometer chart and the dollar spit chart. Is there any way that one can use these methods on something shaped like a bucket or do the shapes have to be "straight line shapes"?

What I was trying to do was to adapt the thermometer method to work on a bucket but I realised that the chart is built on a column chart. Thus a bucket would not work cos the top is wider than the bottom.

View 7 Replies View Related

Multi-level Lookup

Oct 24, 2007

I'm looking for a formula to pull the correct piece of data from one worksheet and display it in another. I have tried VLOOKUP, but it is only returning values from the first instance of my lookup value. Below I have included a sample of what the data from the first sheet looks like. My lookup value is entered on Sheet2 in cell A1. The formula I have used is as follows...

=VLOOKUP(A1,Sheet1!A2:D13,4,FALSE)

The result this would return for a value of 111111111 is 15, but I'm looking for one more level of depth in this lookup, by being able to pull not necessarily the cost of the first code, but of a specific code......

View 3 Replies View Related

Relative Path With Different Directories - Especially Up One Level

Jan 4, 2011

Is there any way to reference a directory that is up one level from the ActiveWorkbook.Path?

I am trying to accomplish three goals:

1. Maintain a Master Database in a root directory and pull data from a subdirectory - EASY:

VB : FilePath = ActiveWorkbook.Path & "sub-DirectoryName"

2. Maintain a Minor Database located in a sub-Directory, Named by Month, and pull data from the Master Database located in the Root Directory (up one level). Which I cannot find the right syntax for - ".." does not work.

3. Maintain a Minor Database in a sub-Directory, Named by Month, and pull data from a different Minor Database in a Different sub-Directory (e.g. up one level to root directory, then down one level to "January"). Which I also cannot find the right syntax for.

The Databases are going to be moved around A LOT, so I have to use relative paths.

View 4 Replies View Related

3 Level Dependent Data Validation

Jan 16, 2014

I have a workbook with three sheets.

Sheet 1 is a large data array (approx 8 columns x max 400 rows), than contains the inventory and cost information of a list of products in a running cafe.

Sheet 2 is is a recipe analysis tool that I want to be able to use data validations to cost out recipes, drawing information from sheet 1 in three dependent drop down boxes 1- category, 2- supplier, 3- item name, which then returns that items cost per standard of measure.

Sheet 3 is where I have started to try to generate a unique list for category and supplier to assist sheet 2 in referencing from sheet 1.

There is data overlap across the inventory items, with many items having the same category and or supplier.

Examples that I have found online use the index and match function, in conjunction with naming to return the array required for the drop down, however most of these examples I have found contain simple unique lists.

I cant seem to nut dynamic naming, which is integral as the list is expanding and contracting all the time.

So far I have been successful by manually naming a range (not a satisfactory solution as per above) and then using the indirect function to reference that named array from sheet 1.

View 4 Replies View Related

Multi Level Match Function

Nov 19, 2009

Been trying to figure out whats wrong with my formula. The 4th column doesn't seem to produce the intended result. I have attached here for reference.

View 4 Replies View Related

Water Tank Level Formula

Jan 1, 2010

I am making a chart for displaying the amount in inches that we have in the water tanks.

The idea is to have a setup like this...

View 14 Replies View Related

Multi Level Bill Of Material

Feb 9, 2010

create a multi level BOM in excel:

i have a formula
A=a+b+c+B
B=a+d+e

if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.

also i may take 50% of A +50% of B the resulting formula must appear.

i attached an exemple file.

View 14 Replies View Related

Use The Dropdown List To Move To The Next Level?

Jan 23, 2013

I have a sheet on the left is a list of document names, next to the list is a hyperlink to the actual document. On the right is a drop down list.

The list contains:

Started
In Progress
Complete

I want each person to be able to use the dropdown list to move to the next level but not be able to go backwards.

Example of how it would work:

A person would click the link to the document and set the status to Started in the drop down.They would read half the document and set the list to In progress. However they would not be able to go back to Started or a blank cell once they have set it to In Progress. The same applies to the next level, if they set it to Complete, they cannot change it to anything. Perhaps before "locking" it as Complete there could be a message box warning that the action is not reversible and giving the option to leave it as In Progress.

View 6 Replies View Related

Disable Save As At Program Level?

Jan 29, 2014

I know the disabling of 'Save As' has been covered alot. I have successfully disabled both Save and Save As at the file level by the following:

Making file Read Only (which disables Save option)

Inserting the following VBA to make the Save As disabled.

[Code].....

The remaining problem I have is disabling the Save As prompting at the program exit level.

For example, if I have a read only file with Save As disabled and I choose the workbook (file) level close window (X) button, I am prompted to save the file but am not allowed to. This is the behavior I am looking for.

HOWEVER, if I choose to exit the file by selecting the program (Excel) level close (X) button, I am prompted to save a copy of the file and it allows me to do it.

I do not want my ~180 users to have any option to save the file once it is uploaded to a centralized server location. It is a large file and I do not need multiple versions floating around.

Is it also possible to disable the 'Save As' on close at the Program level??

View 1 Replies View Related

Excel 2007 :: 4th Level Sorting

Nov 26, 2011

when you sort by VBA, you can only do 3 level of sorting. is there a workaround solution for 4 or more level to sort?

I am using excel 2007 but the data excel is xls.

View 4 Replies View Related

Subtotal Spreadsheet That Already Has Level Of Subtotals On It?

Mar 22, 2013

Is it possible to subtotal a spreadsheet that already has a level of subtotals on it?

View 2 Replies View Related

Calculation By Criteria And Target Level

Nov 15, 2013

I have to write a calculation that sums a range of values. Times that sum by a given amount but only if the total of the original sum is greater than a specific criteria -

A
B
C
D
E
F
G
H
I
J
K
L
M

PRODUCT

[Code] ........

In the above example it calculates the total units sold of Product A, by Month, by 5.00.

In Column M, It totals the number of sales units and then also totals the number sales units by that given value 5.00. So 426.5 x 5 = 2132.50

I need a sum that only totals the value over a given criteria.

EG. Total 426.5
Criteria > 300 = 126.5
Sum would equal 126.5 * 5

View 1 Replies View Related

Formula For Difference To Next Rebate Level

Feb 17, 2014

I'm building a sales report that has various formulas to work out rebate percentages based on the number of units.

I've written the if statements for these but how to get formula to work out the number of units required to achieve the next highest rebate level.

rebate levels

>=150 units= 1% rebate
>=300 units =2% rebate
>=600 units =3% rebate

column I has the unit totals and in column M I need to have a formula to work out how many units the customer would need to purchase to achieve the next rebate level. Headers are row 1 and data begins in row 2.

View 3 Replies View Related

Calculating Percentages Of Multi-level BOM

Mar 13, 2014

So our company gets Material Statements which basically state what chemicals and how much of said chemicals go into producing X item.

I need to figure out the percentage of MASS for each chemical.

So data might look like this:

Total Weight of product: 500 grams or mg, etc.

Sub-Item - Contact - 450 g
Material - Copper - 425 g
Substance - Nickel - .05 g
Substance - Copper - 422 g
Substance - Lead - .02 g
Material - PVC Insul - 5 g
Substance - Polysomethings - .5 g
Substance - Red Dye - .4 g
Substance - Plastic - 4.1 g

So basically the sub-item line is 450/500
first material line is 425/450
substance is .05/425

the substance lines use vlookup to find the material line to divide by. formula i'm using is below:

=IF(B2="Sub-Item",I2/$M$2,IF(B2="Material",I2/VLOOKUP("Sub-Item",$B$2:I2,8,FALSE),IF(B2="Substance",I2/VLOOKUP("Material",$B$2:I2,8,FALSE),"n/A")))

B2 is either sub-item/material/substance
Col I is the mass of that line
M2 is the total weight of the item

Formula works until you get to the Polysomething lines in the above example data. instead of taking the material weight from PVC Insul, it takes it from Copper.

View 1 Replies View Related

2-level Dynamic Grouping Of Rows

Jul 8, 2008

I have a spreadsheet that requires grouping at two levels. See pasted image below.

I need a macro that on the first pass dynamically creates groups of different sizes of data based on values of one column. For example, a group is created for the RR's, a group is created for the SS's, and a group is created for TT's.

On the second pass I would like to group the groups based on the values of another column for the 2nd level of grouping. So the A's would be one group and the B's would be the other.

TotalAAToTalRRARRARRARRAToTalSSASSASSASSATotalTTATTATTATTATotalBBTotalUBUBUBTotalVBVBVBVBVBTotalWBWBWBWB

I can have 2 tables set up for the values that I need grouped at each level.

Table 1Table 2Group 1Group 2ARRBSSCTTUVWXY

I had recorded a macro where I manually grouped the data. However, this did not help, because I am creating a template that needs to dynamcally handle the data that comes in.

View 9 Replies View Related

Define Worksheet Level Names

Oct 26, 2009

I was trying to re-create an example from Chapter 4 the 2nd Edition of Professional Excel Development in which there is a data validation list,
that is conditionally populated according to the selection of another data validation list.

When you choose b/t Fruits or Vegetables (Source:=Categories) in column D, the list under Item (column E) will read as a group of Fruits or Vegetables (Source:=If(IsBlank(D4),"",Indirect(D4)).

When I look at the Define Name dialog box, there is a column indicating the sheet name, which I cannot re-create/build myself. My first question is how do I do this, and secondly, the 2nd formula above will not work when I plug it into the data validation, but it may be on account of these worksheet level names. If you guys can see another reason why, I'm all ears (or eyes as it were).

UPDATE: Of course, I just tried it again this morning and it worked, but i would still lke to know how to use Worksheet/book level names.

View 9 Replies View Related

Multi-level Dependent Lists

Feb 25, 2010

I need to set up an excel sheet with dependent data validation that is multiple levels deep.

ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.

Is this possible?

Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.

View 9 Replies View Related

Save A Module Level Variable

Jul 20, 2006

I would like to save a module level variable but do not know how to do it. I have tried using the public key word. If I put it in the sub, I get an error when it tries to compile the sub. If I put it outside of a sub, it just doesn't work. Surely this is something easy.

View 8 Replies View Related

Determine Tank Level As Filling

Oct 1, 2006

I have got a case for which I am trying to find a (simple) spreadsheet formula. If possible without macros, since I am not very good at that yet.

A truck is filling a tank, which has a level reading. The formula should calculate how much the level increase is, during the period of loading. The level may fluctuate a bit and therefore it is tricky to automatically define the starting point and end point of the data. I was thinking about the tracking of a certain change in reading, but don't really know how to progress.

View 9 Replies View Related

Import From File One Level Up With Variable Name

Mar 11, 2007

I wish to import data from another workbook which will always be one directory level up from the target workbook. The problem is that the source workbook's name will change but it can always have the first four characters the same, ie, List_Dublin, List_Kildare, etc.

View 4 Replies View Related

Hyperlink Folder Level Limitations

Jun 15, 2007

I am writing a macro that scrapes information from other workbooks. I wanted to add a hyperlink on each line to the workbook that row's information came from. I tried both the VBA method of adding a hyperlink as well as the formula method. When I click on the link that was created, windows explorer opens up to a folder rather than opening the workbook. I have a feeling I am hitting some folder level limitation. The link is to a network share and here is an example of what it looks like:

\MYCOMPANY.CORPFILESCBOSEALDRIVEDeptROPSPT Billing2007 AdjustmentsMCBDApproved#01305.20075.25.07 - C - NAME.xls

View 2 Replies View Related

Formula - Week Product Level Count

Jan 5, 2014

How to solve my problem in attached file : Week Product Level Count.xlsx

View 1 Replies View Related

Link Sheets Based On Grade Level.

Aug 31, 2009

I would like to link students for the 1st Nine Week list to the correct grade level as shown. I have attached a file.

View 2 Replies View Related







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