Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Can i use a formula to get the worksheet name into cell Results

Is there a formula that will output the current sheet name in text format
(into a cell)?

I want the same type of &[Tab] functionality used in the custom
headers/footers, but in a cell in the worksheet.

Excel allows the user to SUM a range on a worksheet:

	VB:
	
=SUM(Sheet1!A1:A5) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Excel also allows the user to SUM a 3D range by drilling through worksheets:

	VB:
	
=SUM(Sheet1:Sheet3!A1:A5) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Defined names can be used:

	VB:
	
=SUM(fish) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So if I define a worksheet specific name ("fish") on each worksheet, is there a way I can get Excel to sum that range on multiple worksheets? Eg

	VB:
	
=SUM(Sheet1:Sheet3!fish) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Furthermore, can I somehow enter an array formula to get the "drill through" data for each cell in a specified range

	VB:
	
 an array into cells Sheet4!A1:A5 
=SUM(Sheet1:Sheet3!fish) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I do not want to reference each worksheet individually as I want to be able to add and delete worksheets without causing problems. And I want to refer to defined named ranges so that rows can be added on a worksheet without causing too many problems.

Thank you for your help.

I need help using excel. I have an inventory divided into different
workbooks according to product type, each workbook contains multiple
worksheets for the different providers. What I need is to be able to
access the prices of the different products I have, from a different
workbook, I would like a formula to acces the price according to the
type of product I have and be able to use CTRL+D to propagate the
formula to similar items.
For example:
In cell A2 I have CAPA0003 which is the product code and is stored in
worksheet APA in workbook ceramics.xls and to get the price I have the
formula
=VLOOKUP(A2,[Ceramicas.xls]!APA,16,FALSE)
all worksheets have the same format and the prices are store in column
16
The formula works perfectly but with this formula I have to type the
worksheet name of each product every time. Since the worksheet name is
embedded into the product code is there a way to get the worksheet
name from the product code automatically so that I can copy the
formula to all items in the same worksheet to get their prices?

I hope the question is clear enough.
Thank you

I had been using Harlan Grove's PULL function in order to link to data in another workbook that wasn't necessarily currently
open in Excel.

The PULL function allows you to specify the details of your linked range as a cell value (unlike a direct link), similar to the
INDIRECT function. However, INDIRECT doesn't work with closed workbooks. I like Harlan's PULL function because the code is
open and can be easily copied and pasted into additional VBA workbooks as required so that no add-ins are needed. (I have not
had much experience with using INDIRECT.EXT via the MoreFunc add-in, although it is possible to "attach" MoreFunc to a workbook
so that functions can be used without the add-in being installed.)

The PULL function works well - however, it can be slow to use when returning large ranges of cells. For example, we had a
spreadsheet that was taking 5 minutes to update when using PULL to refer to a range of 3000 cells. So I looked into creating
an alternative function that would be more suited to my circumstance. I feel I was successful and wanted to share my findings
here, since the publishing of the PULL function was very helpful to me. (Harlan - I hope that you don't mind that I have used
some of your ideas in the new function.)

The PULL function uses the ExecuteExcel4Macro command to get values from a cell from a closed workbook. The LINKEDRANGE
function that I present here differs from PULL in that it actually opens the linked workbook (in a separate Excel instance,
since spreadsheets cannot be normally be opened in a UDF), gets the values it needs and closes the workbook.

LINKEDRANGE may be faster than PULL when returning ranges of values from linked workbooks. LINKEDRANGE may be slower than PULL
when returning single values or small ranges.

Furthermore, LINKEDRANGE can be used to link to named ranges that refer to a range of more than one cell. (PULL works with
named ranges that refer to a single-cell only.)

The VBA code and sample spreadsheets are located he
http://www3.sympatico.ca/sstackho/LinkedRange.zip

The .bas file can be used for easy importing into spreadsheets.

Although I have tested the code on a couple of machines, it certainly will not be as bulletproof as the PULL function. Harlan
has added several layers of armor to the PULL function over the years so that it works on more Excel versions and more
operating systems. Since I don't fully understand all of the error-checking logic in the PULL function, I wasn't comfortable
adding it to the LINKEDRANGE function. Harlan or anybody: please feel free to make this function better by adding any
additional logic as you see fit.

I will paste the code below, although it might not look very good with line-wrapping, etc. The code is also available at the
link above.

'-------------------------------------------------
'-------------------------------------------------

Option Explicit

Function LINKEDRANGE(Link As String) As Variant

' Developed by Shawn Stackhouse
' Inspired (and partially developed) by Harlan Grove and his PULL function
' that was in turn inspired by Bob Phillips and Laurent Longre
'-----------------------------------------------------------------
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------

' Version History
'
' v1 - 2006-08-24
' v1.1 - 2006-08-25
' - changed structure to have the function accept a single input and split out the LINKREFERENCE logic to a separate
function
' - fixed problem with conflict between workbook-level and worksheet-level named ranges

' Purpose:
' This user-defined function can be used to get values from another spreadsheet, even if it is not open.
'
' This provides similar functionality to using regular Excel links, but allows the locations and names of
' linked workbooks to be specified via cells.
'
' LINKEDRANGE provides similar functionality to Harlan Grove's PULL function.
'
' LINKEDRANGE differs from Harlan Grove's PULL function in that LINKEDRANGE actually opens the linked workbook (in a
' separate Excel instance, since spreadsheets cannot be normally be opened in a UDF) to get the linked values.
' Furthermore, LINKEDRANGE can be used to link to named ranges that refer to more than one cell.
'
' LINKEDRANGE may be faster than PULL when returning ranges of values from linked workbooks. LINKEDRANGE may
' be slower than PULL when returning single values or small ranges.

' **** NOTE ****
' This function requires a full recalculation (Ctrl+Alt+F9) in order to update values

' Function Output:
' - a range of variable size
' - to return a range of cells, use LINKEDRANGE as a formula array (Ctrl+Shift+Enter)

Dim xlapp As Object, xlwb As Workbook, xlws As Worksheet
Dim r As Range, iChrPos As Long
Dim Directory As String, WorkbookName As String, WorksheetName As String, WorksheetRange As String
Dim NamedRangeRefersTo As String

On Error GoTo CleanUp

' Check to see if the referenced range is currently open in this Excel instance,
' by using an Evaluate function. If the function returns an error, then the
' range is not open (or the range is invalid).

' Do an EVALUATE on Link to see if the referenced range is currently open in this Excel instance.
' The Evaluate function will return an error if the range is not open
LINKEDRANGE = Evaluate(Link)

' If the range is not open (or invalid), an error will be returned from the above statement and
' the following section will be processed

If CStr(LINKEDRANGE) = CStr(CVErr(xlErrRef)) Then

' Let's decipher the Directory, WorkbookName, WorksheetName and WorksheetRange from the Link string.
' The Link string can be in a variety of formats.

' If the first character is not a single quote, then a Directory has not been defined.
If Left(Link, 1) "'" Then
Exit Function
End If

' Remove the leading single quote
Link = Mid(Link, 2, Len(Link) - 1)

' the Directory name will end at the last occurrence of ""
' find last occurrence of ""
iChrPos = InStrRev(Link, "")
Directory = Left(Link, iChrPos)
Link = Mid(Link, iChrPos + 1, Len(Link) - iChrPos)

' The next character will be a "[" unless the worksheet name has not been defined (and a workbook-level named range is
being used)
If Left(Link, 1) = "[" Then

' a worksheet is defined, the Workbook name will be until "]"
iChrPos = InStr(Link, "]")
WorkbookName = Mid(Link, 2, iChrPos - 2)
Link = Mid(Link, iChrPos + 1, Len(Link) - iChrPos)

' the worksheet name will be until a single quote
iChrPos = InStr(Link, "'")
WorksheetName = Mid(Link, 1, iChrPos - 1)
Link = Mid(Link, iChrPos + 2, Len(Link) - iChrPos)

Else

' a worksheet is not defined
WorksheetName = ""

' the workbook name will be until a single quote
iChrPos = InStr(Link, "'")
WorkbookName = Mid(Link, 1, iChrPos - 1)
Link = Mid(Link, iChrPos + 2, Len(Link) - iChrPos)

End If

' the WorksheetRange will be what is left over in the Link string
WorksheetRange = Link

' Create a new Excel instance
Set xlapp = CreateObject("Excel.Application")

' Open the linked workbook as read-only and do not update any links in the linked workbook.
' If the workbook doesn't exist, an error will be triggered.
Set xlwb = xlapp.Workbooks.Open(Directory & WorkbookName, UpdateLinks:=False, ReadOnly:=True)

' If a workbook-level name has been used (i.e. no WorksheetName was specified), then we need to refer
' to the RefersTo property of the named range to ascertain the proper worksheet.
If WorksheetName = "" Then

' temporarily add a blank worksheet to avoid problems with worksheet-level named ranges
Set xlws = xlwb.Worksheets.Add

NamedRangeRefersTo = xlwb.Names(WorksheetRange).RefersTo
' find the '!' in the range
iChrPos = InStr(1, NamedRangeRefersTo, "!")
WorksheetName = Mid(NamedRangeRefersTo, 2, iChrPos - 2)

'check for single quotes around WorksheetName
If Left(WorksheetName, 1) = "'" Then
WorksheetName = Mid(WorksheetName, 2, Len(WorksheetName) - 2)
End If
End If

' Refer to the WorksheetName worksheet.
' If the worksheet doesn't exist, an error will be triggered.
Set xlws = xlwb.Worksheets(WorksheetName)

' Refer to the WorksheetRange range on the worksheet.
' If the range doesn't exist or is invalid, an error will be triggered.
Set r = xlws.Range(WorksheetRange)

LINKEDRANGE = r

End If

CleanUp:
Set xlws = Nothing
If Not xlwb Is Nothing Then xlwb.Close 0
Set xlwb = Nothing
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End Function

'-------------------------------------------------
'-------------------------------------------------

' In order to assist with building the 'Link' parameter above (or 'xref' for PULL),
' I created another little UDF below that returns the Link parameter based on
' directory, workbook, worksheet and range inputs.

'-------------------------------------------------
'-------------------------------------------------

Function LINKREFERENCE(Directory As String, WorkbookName As String, WorksheetName As String, WorksheetRange As String) As
String

' This function can used as a helper for the LINKEDRANGE function. This function takes in information about the linked range
' and returns a link reference in the form needed by LINKEDRANGE.

' Function Inputs:
'
' Directory - the full path that contains the workbook from which values will be pulled
' - trailing "" is optional
' - e.g. C:LinkedData
' - relative directories can be used
' - e.g. "C:DummyDirectory..LinkedData" (evaluates to C:LinkedData)
'
' WorkbookName - the name of the workbook from which values will be pulled
' - e.g. LinkedWorkbook.xls
'
' WorksheetName - optional - leave blank if referring to a workbook-level named range
' - the name of the worksheet from which values will be pulled
' - e.g. LinkedSheet
'
' WorksheetRange - the cell range or named range from which values will be pulled
' - e.g. A1:E5
' - e.g. $A$1:$E$5
' - e.g. LinkedNamedRange

' Function Output:
' LINKEDRANGE - a string that contains the link reference in the form used by LINKEDRANGE

Dim sLinkReference As String

On Error GoTo CleanUp

' If the Directory, WorkbookName or WorksheetRange fields are not defined, then exit the function immediately.
If IsEmpty(Directory) Or IsEmpty(WorkbookName) Or IsEmpty(WorksheetRange) Then
Exit Function
End If

' Trim the inputs of any excess spaces
Directory = Trim(Directory)
WorkbookName = Trim(WorkbookName)
WorksheetName = Trim(WorksheetName)
WorksheetRange = Trim(WorksheetRange)

' check the Directory string and append a '' to its end if it doesn't already have one
If Right(Directory, 1) "" Then
Directory = Directory & ""
End If

' prefix with a single quote
sLinkReference = "'" & Directory ' e.g. 'C:LinkedData

' A worksheet does not need to be specified if using a workbook-level name.
' The format of the 'xref' statement to be evaluated differs if the workbook is not defined.
' Add a "[" if the worksheet name is defined.
If WorksheetName "" Then
sLinkReference = sLinkReference & "[" ' e.g. 'C:LinkedData[
End If

' Add the workbook name
sLinkReference = sLinkReference & WorkbookName ' e.g. 'C:LinkedData[LinkedWorkbook.xls

' Add a "]" if the worksheet name is defined
If WorksheetName "" Then
sLinkReference = sLinkReference & "]" ' e.g. 'C:LinkedData[LinkedWorkbook.xls]
End If

' Add the worksheet name (may be blank)
sLinkReference = sLinkReference & WorksheetName ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet

' Add a single quote and exclamation point
sLinkReference = sLinkReference & "'!" ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet'!

' Add the linked range (cell range or named range)
sLinkReference = sLinkReference & WorksheetRange ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet'!A1:E5

LINKREFERENCE = sLinkReference

CleanUp:

End Function

'-------------------------------------------------
'-------------------------------------------------

' Finally, here's a small UDF to return the current workbook directory, which can be helpful
' for creating relative links. This is preferable to using the CELL("filename", A1) function
' since CELL("filename") is volatile (even though Microsoft claims it is not!)

'-------------------------------------------------
'-------------------------------------------------

Function ThisWorkbookDirectory() As String
' This function returns the directory of this workbook.

Dim sFullName As String
Dim iChrPos As Integer, iStrPos As Integer

sFullName = ThisWorkbook.FullName

' find last occurrence of ""
iChrPos = InStrRev(sFullName, "")
ThisWorkbookDirectory = Left(sFullName, iChrPos)

End Function

-------------------------------------------------

I hope that someone finds this helpful!

Thanks,
Shawn Stackhouse

--
----------------------------------------------
Posted with NewsLeecher v3.0 Final
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
----------------------------------------------

First time doing this.. but.. This is worth 30usd to me? (I'm guessing paypal is easiest?)

I haven't found online examples quite like what I'm trying to do, but I think it's do-able and quite easy?

I have a worksheet where each row is an individual. To the right of this I want some users to populate the employee numbers of who they feel could replace them if that individual left the company.

Because we want these replacements as employee numbers (so that we can populate additional sheets/do vlookups), and the number of possible candidates is quite large (1-2 thousand), I want to provide a search form they would use to find each replacement. This will allow more accurate data entry while still keeping the document in excel format, which they are familiar with.

So.. What the data looks like (as per first attachment example):

Sheet 1:
User,~extra data~,Replacement 1,Replacement 2, Replacement 3 etc....

Sheet 2:
Employee ID, First and last name, Last Name, First name, ~extra data~

Note that the last/first names on sheet two will be calculated (I'll use a formula to extra the First/Last names from the concatenated name in the 2nd cell). I'm doing this as I believe to allow searching on first/last name separately they should be in different columns.

Functionality desired:
Triggered by either clicking the "replacement" cells, or selecting the cell and starting the macro (ctrl function perhaps?) I'd like a search form to come up.

Search form would show 2 boxes:
-First Name
-Last Name
and a search button.

Entering either or both lists any matches (using wildcard search) and their matching employee number. If the user doubleclicks any of the results, that user's employee number is pasted into the originally highlighted "Replacement" cell.

Workflow Summary:
-User clicks one of the "replacement" cells on the first worksheet and starts the macro. If clicking the cell it'self would trigger the macro/form (or perhaps hitting a combination of keys) that would be ideal.

-Search form appears.. User enters first and or last name and clicks search.. matching results (based on first/last name from 2nd worksheet appear) **Note, would be desirable to show results as their first/lastname plus their employee number.

-User clicks the correct user (if there are multiple results).

-That employee's employee number gets populated to the cell they selected before starting the macro.

I've attached a simple workbook showing what the data looks like:

Closest I found to this was here:
http://www.ozgrid.com/forum/showthre...t=27799&page=2

But that one returns the sheet the matching user(s) is on, then brings you to that sheet/row if you click it.

Hi,

I am trying to build a dynamic table of contents of worksheet names but I have not been able to get the HYPERLINK function to work with my formula that generates the worksheet names.

The worksheet name formula works perfectly. It's in two parts:
1. A range name called "Worksheet" has the formula:
=GET.WORKBOOK(1)

2. A cell formula that I can copy down:
=MID(INDEX(Worksheet,ROWS($A$1:$A1)), FIND("]",INDEX(Worksheet,ROWS($A$1:$A1)))+1,32)

Problem is when I stick that cell formula into the HYPERLINK function and append &"!A1" I get the error "Can not open specified file."

What am I doing wrong?

Lawrence

How can I link a formula in a worksheet to an unopened worksheet based on two of the cells data. I have a folder (2010) that gets added to each day with sheets named based on Date and Time...ie 25-07-2010-0330, for July 25th,2010 3:30, this is how they're saved automatically every time I run my macro in a worksheet named "WorkCompCore".
In WorkCompCore, can I have formulas in the cells already populated by the macro, link to the appropriate sheet in the unopened folder and get the data it needs to run the formula correctly from this unopened sheet?
Right now it gets the data for previously filled cells from the most current sheet in use. This is wrong. I need to show the past data plus the current data both. (Link to the Folder 2010)
The sheet saved in the unopened folder is identical to one that WorkCompCore is running the macro with. The current data gets saved everytime the macro runs, into this unopened folder as mentioned earlier as an identical copy of this sheet. The sheet that WorkCompCore uses is actually like a template so to speak. Its used over and over to make the copies with current data in the unopened folder.
Anyone have ideas?

I have 2 related questions:

1) Using a formula is there a way I can pull the sheet name into a cell? So for example, if my sheet name is "SHAWN", can I write a formula that would insert "SHAWN" into cell A1. i.e.; in cell A1 of worksheet SHAWN, I enter "=sheetname()" and it returns SHAWN

OR, if I am trying to pull the sheet name off of another sheet. For example, I am on worksheet named SHAWN, and I have another worksheet named "BILL". On the sheet named SHAWN, can I reference the sheet named "BILL" to get and pull out the worksheet name. i.e; in cell A1 of sheetname SHAWN I enter "=sheetname(BILL!A1)", and it would return "BILL"

2) Description - Lookup a value out of an array on another sheet, and reference the sheetname by way of a formula:

I am on worksheet named SHAWN I have BILL entered into cell A1.
In cell A2 of worksheet SHAWN, I have the date "April 1st, 2005", which corresponds to a array on my worksheet named BILL (for illustration, lets say my array is located on worksheet BILL in cells A1:B10). Can I write a lookup formula that looks up my date in my array on worksheet BILL, and in the formula reference cell A1 to direct the vlookup to my worksheet named BILL
ie; =vlookup(A2,unknownformula(A1)!A1:B10,2,false)

thanks in advance. Im sorry if im not clear and easy to follow in these questions.

Oy Ve. I had no idea how to name this question so I did the best I could. Let me explain:

I have a range of 12 cells from I2 through I13 which presently do not have any data. However, upon entering a username into a UserForm I have excel simultaneously populating those cells (I2 to I13) with the username as well as creating a separate worksheet named for the given username. I need cells J2 through J13 to reference the worksheet named for the adjacent cell. An example: I enter username Mike into the userform. A new worksheet named "Mike" is created and "Mike" is output to cell I2. I need cell J2 to do the calculation of =Mike!E2-Mike!C28 without explicitly referencing the name of the worksheet (as it can change at any time) or the sheet number (e.g. sheet1, sheet2, etc).

A few things to note. J2 will always reference the worksheet named for I2, J3 for I3, J4 for I4, etc. The cells referenced in the formula "E2-C28" will always be the cells referenced on the given sheet. I'm indifferent whether VBA or a cell formula/s are used.

Thanks in advance

*EDIT* I tried the following code

	VB:
	
Cells(emptyRow1, 10).Value = (Worksheets("TeamNameBox.Value").Range("E2")) - (Worksheets("TeamNameBox.Value").Range("C28")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get a "run time error '9'. Subscript out of range"

I'm really just unsure of how to reference a worksheet that is yet to be created

Okay here's the deal. I am in a financial worksheet course and have to answer the following question:

Let's say we have typed a list of names first and last names in column A and need to sort by last name. How can we separate the names in the columns so you have first name in column A and last names in column B, without using a formula.

Now go the other way put two columns first name and last name together in one column. There are two ways to do this.

How would you apply formatting only to cells containing text?

I figured out how to seprate the list of names in column A, but I am having trouble getting it to go back into one column instead of two. My instructor says there are two ways to do this, I can't even figure out the one way. Can someone help me with this question?

I'd like to be able to have a cell on a worksheet display the names of certain sheet tabs. I would like this cell to automatically update if a user changes the name on the tab.

So on SummarySheet the display would be:
A1: Tab1
A2: Tab2

And if the user changed the name of Tab1 to FirstTab, SummarySheet would then display:
A1: FirstTab
A2: Tab2

Essentially I want my user to be able to visually confirm these pieces of information because I hope to use them in an INDIRECT in formulas on the rest of the sheet. The tab names will likely change every time the template is used (because we like to include some type of date in the sheet name). If I can have a formula pick up the sheet name then I don't risk the user typing it into the cell incorrectly, or failing to update the cell.

Right now the hope is that the underlying tabs will always be the correct ones, and the initial user is just going to look to these cells to confirm they are correct. I'll worry about instructing them on how to fix the problem once I get past this step. They will act as good information for secondary users regardless.

The ADDRESS and INDIRECT functions are heading in the right direction, but they convert text to references, not references to text.

Thoughts?
Kelly.

Before posting my nightmare I would like to direct my thanks to all of
you who posted answers before. Browsing trough the response I complete
maybe 80% of my task in a very neat way. The remaining 20% of the task
it seems that will require more than 80% of the time!

There are 3 worksheets named S1, S2, and S3.
I use a macro to first fill the no value NA() a range sayA1:C700 in S3!
And second to open a text file and format data in it (get rid of empty
rows via sorting since data in first column is ascending, etc). At the
end of this macro there is another worksheet named P1.txt which has 12
columns and a variable number of rows (never exciding 700).

I need to copy only 3 columns from P1.txt! into S3! in A,B and C
column. Since there are several formats I get my useful 3 column of
data not necessarily in the same place all the time. In S1! there are 3
cells (A1, A2, A3) which hold the info regarding the order where my
columns are in the P1.txt file..

One solution would be: select a range using OFFSET with P1!A1 as
reference, 0 for row, S1!$A$1-1 for column shift. Use COUNT to get to
the bottom. There is select_active_column code which I know is working
but I can not get to the first cell of the column of interest. Not to
mention I cannot convince COUNT to count a generic column!

I tried to move to the cell on top using:

///////////////////////////
Sub Macro3()
' Sheets("S3").Select
Range("a1").As Range
Range("a1").Offset(0, S1!A1).Select
End Sub
////////////////I got: argument not optional

Here is the second question:
Once I have the data in column A-C in S3! I like to run in adjacent D-G
formula which will extend down to the last row of data (again: the
length of the column varies). I like to have this formulas in a macro
rather than first cell.
They are just plain trigonometry across the row like (in S3!):
D1=B1+(S1!$B$1*sin(A1+90)*PI()/180) and so on...( S1!$B$1 is another
parameter hold on S1!)
Can be that done using dynamic range?
Then is the neat part from S2! which is populated with
=IF(S1!$C$1=True,S3!,NA()), S1!$C$1 is a check box (thank you people!)
the user can plot or shut down that curve on a chart.

To resume:
1.I need help with a code to select a cell (or even better the whole
non empty column) from a sheet based on the value of a cell from
another sheet, which indicate the order of column of interest.
2. I need a code to fill a formula in the next column(s) based on
values across the row from the first 3 columns. All the columns has the
same number of rows, but the number of rows changes each time the data
is updated.

I apologize for rather lengthy post, and I will appreciate any help,
Emil

I am working with a Pivot Table that contains a calculated field which is simply revenue multiplied by a percent. The formula is hard coded within the Pivot Table, but users would like to be able to manipulate the percent used within the formula without having to manually change the formula itself (i.e. they would like to be able to enter a percent into a cell that exists above the pivot table and the formula within the pivot table will then recalculate using the user defined percent).

I have been told that I need to use a macro to acheive this as a pivot table calculated field can not refer to a cell.

I have messed around and created the following VBA code thus far, but when I enter a number into my cell I get an error saying that "The Value You Entered Is Not Valid; A user has restricted the values that can be entered into this cell." The below code is supposed to change the formula "EOTC Credit" that is found in the Pivot Table within the worksheet "Active Pivot" so that the percent entered into cell F8 on the "Active Pivot" worksheet is entered into the "EOTC Credit" formula. Cell F8 is a defined range titled var_EOTCCredit. The original "EOCT Credit" formula is (='SumOfFinal Imputed List Revenue' *0.01), where "SumOfFinal Imputed List Revenue is a field within the data that the pivot is built off of and "0.01" is the percent that was manually placed into the formula to start with. It is this percent that I would like to replace based on what the user defines.

Not sure if the code below can be fixed or just scrapped.....please help!!!!
_________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Address = Target.Address Then
ActiveSheet.PivotTables(1).CalculatedFields("EOTC Credit").StandardFormula = "='SumOfFinal Imputed List Revenue' *" & ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Value & ")"
End If
End Sub
_________________________________________________________________________

Any help is VERY MUCH APPRECIATED!!!!

Thank you,
G

I have a worksheet template (ddsd.xlt) that contains links to a worksheet (Admin) within the workbook I am insterting it into:

When I click Insert on a tab and choose the template, it asks me 'The workbook you opened contains automatic links to information in another workbook...etc' if I choose Yes, it then says 'File not Found' (it's trying to find the linked cell workbook) the cells formula in the template is Code:
 this is a worksheet in the workbook I am inserting it into.
Even if I click Yes, Cancel or choose No at the message screen the sheet is added to the workbook but I get a Code:
 in the cell with the above code, even though that worksheet exists in the workbook I just inserted it into.
If I go to the Formula bar and click Edit Formula and click enter it fixes the Code:
 issue and points to the worksheet cell.

Any ideas on how I can avoid this happening, so that it inserts it and 'updates' the formula.
Also I am using a macro to insert the template (same issues):
Code:
Sub Insert_NewClient_Template()
    Dim sh As Worksheet
    Dim shName As String

    'name of the sheet template
    shName = "ddsd.xlt"

    'Insert sheet template
    With ThisWorkbook
        Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
                            after:=.Sheets(.Sheets.Count))
    End With

    'Give the sheet a name
    On Error Resume Next
    sh.Name = InputBox("Enter sheet name " & s & " of " & SheetCount, _
                    " NEW SHEET NAME")
    If Err.Number > 0 Then
        MsgBox "Change the name of Sheet : " & sh.Name & " manually"
        Err.Clear
    End If
    On Error GoTo 0
End Sub
Is there a way to suppress the Message Box ('The workbook you opened contains automatic links to information in another workbook...' )?

thanks

I've been working on this issue today and have decided to ask the same question differently; sorry if this is duplicitous.

Please review the sheet below:

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCD1QtyCash*ReconciliationAmount*2*Net*Sales*Plus*Tax$1,846.30**30*Paid*Ins*(+)$0.00**40*Paid*Outs*(-)$0.00**5*Total*Responsible*For*=$1,846.30**6*Non-Depositable*Total$648.79**7*******Credit$648.79**8*Deposit*Total*(+)$4,376.07**9*******Deposit*102055$1,632.25**10*******Deposit*102121$1,546.32**11*******Deposit*102321$1,197.50**12*Total*Accounted*For*=$1,846.29**13*Over/Short($0.01)*14*******Sum*of*Shift*Over/(Short)($0.01)*mon*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I am trying to get the deposit information from this sheet and import it into another worksheet. The actual row where the data is at changes from day to day. I currently use a vlookup to get the "Deposit Total (+)" deposit amount into the spreadsheet but have trouble getting the individual deposits brought over; they all start with the word "deposit" and all have a unique number that is system generated.

What I'm thinking is that once I get the "Deposit Total (+)" located on the sheet, I can get the next 3 entries using cell referencing. Unfortunately, I don't know exactly how to accomplish this.

Does any of this make sense? Any ideas - suggestions?

Thanks.

Louis

Hello,

I have two corresponding workbooks each with 52 worksheets (one for
each week of the year). The first workbook is a control sheet for
inputing data. The second draws information off of the control sheet
using a system of arrays and range names.

For example I use variations of the following formula:

=INDEX('[ControlSheet-WklyPL-AllStores.xls]24-Oct-05'!ControlAll,73,4,$G
$1)

The Workbook name ("ControlSheet-WklyPL-AllStores.xls"),
the range name ("ControlAll") which is defined within the
ControlSheet-WklyPL-AllStores.xls, the row numbers, and the columns
(73,4)
all remain constant.

The only thing that changes is the Sheet Name ("24-0ct-05" in this
example) and the Area_num ($G$1).

My first problem was to find a way to enable the Sheet Name to change
based off referencing a cell. I was able to resolve the problem with
the INDIRECT function, but the result is that the range name
("ControlAll") no longer works.

To simplify:
I defined a name for "ControlSheet-WklyPL-AllStores.xls" as
"ControlBook"
I defined a name for a cell referencing the Sheet Name ("24-Oct-05") as
"Date"

I tried as many versions as I could think of like this (with
paranthesis, without, etc.) but nothing referencing the range name from
the other workbook seems to work:

=INDEX(INDIRECT("'"&ControlBook&Date&"'!"&"Control All"),73,4,$G$1)
result: #REF

I was able to get a termporary fix using an extended version of the
following formula (I shortened the definition of it for demonstration):

=INDEX((INDIRECT("'"&ControlBook&Date&"'!"&"A1:F99 "),INDIRECT("'"&Contro
lBook&Date&"'!"&"G1:L99")),73,4,$G$1)

Whereas in this example "A1:F99" and "G1:L99" make up the array
(A1:F99,G1:L99) that is defined above as "ControlAll" within the
ControlBook workbook.

I was hoping someone might know how I can reincorporate the ControlAll
range name back into the formula rather than having to split it up into
the smaller pieces. As I add to the ControlBook and extend the array,
the goal is to merely change the definition of the ControlAll range
name, whereas the temporary fix version would mandate that I change
every formula if I increase the size of the array.

Thanks to anyone who can help,
Steve

I am using Microsoft Excell 2003 SP1.

I have a UDF in a normal Module that reads all the worksheet names in
the active workbook and puts them into an array. A Summary worksheet
contains a formula copied to several cells in a column that uses this
UDF to list the sheet names. The formulas end in (wihout the quotes)
"&T(RAND())" to make it volatile so that it updates whenever worksheet
names are changed.

I also have a Workbook_SheetChange macro that writes data into an
adjacent cell when a target cell value is changed. I use
Application.EnableEvents=False at the beginning and
Application.EnableEvents = True at the end of the macro. The target
cell has a validation drop down list.

When the line of the macro is reached where it is supposed to write to
the adjacent cell, before the data is actually written to the cell, the
UDF is called and executes.

This is not a problem if valid data is entered into the target cell via
keyboard entry. The UDF executes and execution is returned to the macro
which then finishes normally. But if the data is selected from the
validation dropdown list, execution never returns from the UDF back to
the macro. The data never gets written to the adjacent cell and the
Application.EnableEvents remains False.

Is there anything I can do to keep the UDF from taking over the
execution of the code? The Application.EnableEvents = False does not do
it. Any other VBA statements that would do the job?

Thanks

--
Lreeder
------------------------------------------------------------------------
Lreeder's Profile: http://www.excelforum.com/member.php...o&userid=26699
View this thread: http://www.excelforum.com/showthread...hreadid=399672

I have exported from an Order Entry program to comma delimited text file
customer purchases. The file has more entries than Excel can handle on one
worksheet. So, I imported the file data into Access (107,000+entries). I
then used Excel's "Query Database" feature to import customer level
purchases into 4 worksheets. The Order Entry program didn't export customer
names, only customer numbers. So, I exported the customer info to another
text file and imported it into the Excel Workbook, only 451 entries . The
problem is when I did a vlookup function to retrieve the customer name into
the worksheet with the customer number, it works when I go into a customer
number cell with "F2" and exit. Otherwise, I get the #N/A error because I
have it set to false. My formula is
=VLOOKUP(A2,Customers!$A$2:$C$415,3,FALSE) in the invoice worksheets. I am
using Office 2000.
What do I need to do to make things work? I know this hard to follow and
trying to explain it is not easy but any help is greatly appreciated. I know
more about Excel than Access and that isn't saying much!
Thank you very much.
Lee

I am using Excel 2002, and I need to modify a chart so that it updates
automatically. The way the spreadsheet is laid out is that it uses a
row of months at the top, a bunch of detail rows below that, and two
different total rows at the bottom. I'm only concerned with the months
row and the two total rows. In the chart (an embedded line chart on a
separate sheet from the data), the months are the x-values and the
total rows are the two data series. The amount of information being
represented is getting past two years' worth, and as time goes on, more
and more data will be squished into the same chart. The chart needs to
be modified so that it always only represents the last 24 months, and
does it automatically.

I've found a great idea for this at
http://www.j-walk.com/ss/excel/usertips/tip053.htm , which shows how to
use range names and the offset formula, and then refer to the range name
in the series formulas in the chart. The example on the above web page
has the chart representing an ever-growing range, but I wrote offset
formulas to modify the example so the chart only represents the last 5
items, or 10, or whatever, and it works beautifully.

The example, however, has columns of data, whereas the spreadsheet I'm
working with has rows of data. For some reason, the same concept won't
translate into rows. The problem comes down to this: in the series
formulas of the two data series, I can refer to the range name in the
X-values argument (click on data series in the chart and edit in the
formula bar), but Excel won't let me do it in the Values argument. In
other words, I can refer to a constantly changing range in X-values
(the months), but I can only use an absolute reference for the data
values, which kind of shoots holes in the graph being automatically
updated. When I put the range name in the data values argument, Excel
gives me an error that says "Your formula contains an invalid external
reference to a worksheet. Verify that the path, workbook and range name
or cell reference are correct, and try again."

At the j-walk website, there is also a class module that might provide
a VBA solution to this problem
(http://j-walk.com/ss/excel/tips/tip83.htm), but I'm not sure I'm in
the mood to do that, although I could if I had to.

--
jeffsumm
------------------------------------------------------------------------
jeffsumm's Profile: http://www.excelforum.com/member.php...o&userid=29853
View this thread: http://www.excelforum.com/showthread...hreadid=495532

Ok, I have data in several other exel files that I want to link to in several
formulas throughout my spreadsheet.

For example the data in the other worksheets I linking to a

'H:Financials2007January[Plant1 Statistics.xls]Worksheet1'!B$26
'H:Financials2007January[Plant2 Statistics.xls]Worksheet1'!B$26
'H:Financials2007February[Plant1 Statistics.xls]Worksheet1'!B$26
'H:Financials2007February[Plant2 Statistics.xls]Worksheet1'!B$26
'H:Financials2007January[Plant1 salespeople.xls]Worksheet1'!B$26
'H:Financials2007January[Plant1 salespeople.xls]Worksheet2'!B$26

I would like to calculate formulas using this other data, by being able to
use variables for different components of the path name for these files, so
that I can easily change what files the data is being pulled from to populate
a cell.

For example, I'd like to be able to let A1="January", B1="Statistics" and
C1="Worksheet", D1="H:Finanacials2007". E1="Plant1

and then have my formula fill these "variables" into my cell's formula to
use the right path to pull the data in from the correct other worksheet.

So my cell formula(with the correct syntax) might read something like:
='D1A1[E1 B1]C1'!B26

to get the value in: H:Financials2007January[Plant1
salespeople.xls]Worksheet1'!B$26

This example is a little extreme, however I am trying to link to about 50
different worksheets that have consistent paths, formatted worksheet names
and formulas, and I am trying to find out the syntax to use variables within
these paths, rather than having to "hard-code" the different path names
through out my spreadsheet that is accumulating the data.

Any help you could give me would be GREATLY appreaciated!!!

Thanks,
Joan


No luck finding an answer? You could always try Google.