Referring To Sheet Name Through Variable In VBA?

Feb 21, 2012

I have a workbook with a list of staff in it. The format is: the manager's name is in row 1, the 8 or so team members are listed below. And again in row 20, there is a manager's name with 8 or so team members listed below. This is duplicated over many columns and multiple sheets. The sheets are all named after the ops-managers who manage all the managers on their respective sheet. The book is called "stf.xls"

I'm using the following code to look up a staff member and return who their manager and ops-manager is to the relevant cells in a different workbook:

Code:
Sub AdvisorFind()
Dim Wsht As Worksheet
Dim Slookfor As String

[Code]....

My problem is how to return the manager's name for a staff member in the middle of the book. Activesheet. is the last worksheet so no good. Wsht.name.Range(manager) is what I want to do but I don't know the correct syntax.

View 2 Replies


ADVERTISEMENT

Referring To Variable Array In Formula?

Mar 14, 2012

Is it at all possible to refer to a array that may change in a formula?

For example I need to use a Vlookup formula, however the table array will change depending on the value of another cell.

I need the user to be able to select the column heading that the lookup should work off from a drop down list. So if the user selects column heading C, the array should start from column C though it will always end at column Z. If the user selects column heading Y the array would be Y:Z.

View 7 Replies View Related

Populating Multiple Columns In On Sheet By Referring To A Column In Another Sheet

Mar 14, 2013

i'm having in excel- I'm looking to populate rows in 2nd tab based on the data entered in the 1st tab. The rows to be populated in the 2nd tab resides in the 3rd tab. So a match has to be made on the data entered in the 1st tab. When a match is found on the 3rd tab then the corresponding rows from the 3rd tab should be listed on the 2nd tab.

I have an example.xls attached for clarity.

example.xls

View 4 Replies View Related

VBA - Referring To Named Range On Different Sheet?

Jun 20, 2012

I just named a range on a sheet and would like to paste that range on a different worksheet within the same workbook. I can't figure out how to refer to the range. I've tried several different variations so far. Example below:

ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
'Add new sheet and paste data
ActiveSheet.Name = "Raw_data_Sector_Summary"
ActiveWorkbook.Sheets.Add.Name = "RawData"
Range("A1").Formula = "=RawData!(RawSectorData)"

View 5 Replies View Related

Referring To A Sheet In An External Workbook By Position, Not Name

Oct 26, 2008

I'm trying to summarize some data from an external workbook. The problem is that the worksheet names there are months (Jan2009, Feb2009 etc.) and the sheets rotate (change position) with time. In the summarizing workbook I would like to use a function to refer to (for example) cell A1 on the third worksheet, regardless of the current name of that worksheet. So I might have a cell in the summary workbook that looks something like "=CoolFunction(ExternalWB.xls, 3, A1)"

View 3 Replies View Related

VBA INDEX And Match Formula Referring To Different Sheet

Feb 16, 2013

I have table as below of raw data. In which data will increase dynamically both for rows and columns from starting column for eg say Column "D".

I want to get ID data on sheet2 to compare it with some other data for the ID by using formula = INDEX(Dynamic range,MATCH(ID Ref- Sheet2,Sheet1! ref -dynamic,0),column ref) and get the referred ID data on "Sheet2").

On Sheet2
ID
Data1
Data2
Data3
Data4
Data5
Data6
Data7

[code].....

View 1 Replies View Related

Formula Referring To A Sheet Based On Values In A Cell

Jan 31, 2014

How to do this:

I have many sheets called;

Input Jan
Input Feb
Input ....

Meaning 1 for each month.

I have another sheet where I want a formula that can

..............................................JUL
CapEx.................................... EBITDA...............................EBITDA %
"=('Input Jul 2014'!$BI$3)/1000"

The "Jul" which stand for July, it is possible to have a formula where I can have certain text in a formula that is based on the value in B1 (In this case, B1 being JUL in bold writing.)

Like this;

=('Input &B1 2014'!$BI$3)/1000

Or must I manually go in and replace all formulas for each month?

View 4 Replies View Related

One Sheet Saves A Column To The 1st Empty Row On A 2nd Sheet After 1st Looking For A Match On An ID Variable

Apr 2, 2008

I am trying to create a macro so that when i save an entry the form sheet looks in the tavela master sheet to see if there is a match on the record number (in the A column) and saves there as an update if it matches and then if there is no match, saves it in the last empty row. this is a column to row copy so it has to be paste special to transpose. also i have a password on the sheet which i use to unprotect then reprotect. i have successfully done these two things separately but cannot combine them! i know not very much about visual basic code just what i have read here and so i am trying to make this code work.

Code:....

View 9 Replies View Related

Referring Cells

Jun 17, 2009

I am trying to refer to a cell. Not sure if thats a good way to put it.

Example: I am in Cell B1 and I want to have a formula that says if C2 is 5 then A1 is 5, else B1 is 500.

I need A1 to be completely blank with the formula only in B1 and C2 has the number typed in it.

How can I put a number in A1 without having a formula or any text in that cell (A1)?

View 9 Replies View Related

Referring To Functions Within Function IF And THEN VBA

Nov 5, 2012

So far I have 2 Functions with a sub to test to do certain calculations.

VB:
[B]Private Sub test_getBaristaSalary()
Dim InputYear As Integer
InputYear = InputBox(Prompt:="How many years of Experience?")

[Code].....

View 1 Replies View Related

Referring To Empty Cell?

Jun 12, 2014

I need a better way to refer to an empty cell. I'm using “” but if the cell contain a formula but show empty it affect the conditioning format I'm using this =AND(cell <> "", existing_rule)

View 9 Replies View Related

Referring To Worksheet Name In A Formula

Feb 20, 2010

I have the following formula that I want to copy to a worksheet named Tankersley:

=IF('Tankersley Input'!B5=0,"",'Tankersley Input'!B5)

I would like the formula to read the worksheet name rather than entering the word Tankersley directly, so that if I copy it to another worksheet named Tyson, the formula would read:

=IF('Tyson Input'!B5=0,"",'Tyson Input'!B5)

How would this formula need to read?

View 2 Replies View Related

Referring To Open Workbooks

Jan 27, 2009

Sub vocab6()

Dim fname As String
fname = "c:Documents and Settings
obertDesktopvocab.xlsx"

Workbooks.Open Filename:=fname

Workbooks(fname).Worksheets("tangible nouns").Range("A1").Value = 9

End Sub

I just want to open a workbook saved on my desktop (from a different workbook), and insert 9 into the first sheet (which is named tangible nouns).

View 9 Replies View Related

Referring To Other Workbooks Via VBA Code

Oct 5, 2009

I'm trying to use VBA to write from one workbook to another. I'm trying to populate other workbooks from a single 'control' workbook with a template.

I've managed to figure out how to open up Excel workbooks within a directory (this needs to be done multiple times) supplied by the user, then retrieve information from it to be collated, but I can't for the life of me figure out how to write to the file that I've opened.

I've got the code below, and it's very scrappy as I've been trying lots of different things but nothing's worked. I'll try and highlight as best as I can where I'm having difficulties, as I get the "object not defined" or some such error.

View 12 Replies View Related

Referring To Characters/numbers

Nov 13, 2009

Lets say we have several numbers and combination of letters/numbers. What can we use to commonly refer to them correctly,

34566
R3456
88900
D2344

I tried to use

Like *####

But thats not correct

View 3 Replies View Related

Referring To Chart In VBA Code

Jul 31, 2012

My first version is this:

Code:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19

Works without problems, but I really don't want to use "ActiveSheet" there

I changed it to:

Code:
Dim chtTR As ChartObject
Set chtTR = shtData.ChartObjects("Chart 1")
chtTR.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19"

and it gives me

"Object doesn't support this property or method."

View 3 Replies View Related

Referring To Closed Workbook

Mar 31, 2007

I have the following formula to extract a specific cell from a closed workbook. It works fine. I want to be able to make the file name refer to a another cell so I can create a spinner to change it. For example:

='C:Documents and SettingsTom Desktoplabor[01_032407.xls]Stats'!A4

Cell A1 would be 01_032407, and I would replace [01_032407] with [A1]
but it does not work.

View 9 Replies View Related

Referring To Named Ranges

Dec 11, 2007

I'm creating a button so as to input a chart where the user inputs the name of the range that they want the chart to hold. The named ranges are dynamic thus I want the graph to also be dynamic but when added the source is simply the range of the name rather than the name itself.

Is there anyway I can enter in a VB variable into quotations marks so that it simply writes that.

View 9 Replies View Related

Referring To Multiple Sheets

Feb 24, 2010

I have every month a product demandplan (for each of 150 models) in a workbook. This workbook contains a seperated sheet for every sales region and is saved on network.

What I want to do is a new workbook be able to pull in the information from the closed demand plan. That part is easy. The hard part is that I want to select the sales region from droplist and than get demand per model. VLOOKUP won't work since I can not select the region and INDIRECT doesn't work with closed workbook.

View 9 Replies View Related

If Statement Referring To Another Worksheet

Mar 8, 2007

I am trying to insert an If statement using the

With Sheets("Sheet2").Select
Range("c" & rBegRow).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[-8]C[4]<>0,Sheet1!R[-8]C[4]*-1,Sheet1!R[-8]C[5]*-1)"
Range("C" & rBegRow).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End With

The reason is that once the formula is inserted in to the cell in worksheet 2 then I drag it down kind of like a copy paste so it changes automatically the if statement cell reference to worksheet 1 automatically. the trouble I am having is that I do not want to use the R[#]C[#] since when the contents of the information changes the formula does not work properly. If i substitute them for G9 instead of the R[-8]C[4] and H9 R[-8]C[5] but it inputs it in the actual excel worksheet as =IF(Sheet1!'G9<>0,Sheet1!'G9*-1,Sheet1!'H9*-1) therefore creating a value error.

View 9 Replies View Related

If Function Referring To Two Cells

Jun 29, 2007

I have this formula:

=IF(C38="C"*F38>=0,"1","")

which is meant to return the number 1 in a cell if C38 has the letter C in it and F38 is equal or greater than 0. I have met both criteria and it returns an error of #VALUE! What am i doing wrong? NB in F38 there is a Vlookup formula that returns a number (in this case it is 0).

View 3 Replies View Related

Variable For Last Sheet In VBA

Jun 4, 2009

I need to get to the last sheet in the workbook through a VBScript macro.

View 5 Replies View Related

Sheet Name To Be Used As Variable

Jun 14, 2006

I want to use " sheet name" as Vaiable keeping format the same. For Example in the following example :if I type Sheet2 in cell A1 I should be able to change this range " sum(Sheet1!b3:b5" to "Sheet2!b3:b5"

View 2 Replies View Related

Difference In Two Methods Of Referring To Other Workbooks?

Feb 22, 2014

These workbooks are located in different folders.

The first is pasted "as link" from two open instances of Excel and is an array formula.
The second is just pasted from another open workbook.

{=Excel.Sheet.12|'xx.xxxFolderFolderFilename.xlsx'!'!SheetName!R2C9:R1500C22'}=IF(H4="N/A","N/A",VLOOKUP(A2,'I:FolderMore Folders[FileName.xlsx]Sheet'!$A$1:$T$35,13,FALSE))

What is the difference between using these two different methods?

View 1 Replies View Related

Using Countif Referring To Specific Cell

Nov 27, 2013

Can you set up countif to work to refer to a certain cell instead of a specif name or text.. for instance

HTML Code: 

countif(a36:b36, "c1"

View 3 Replies View Related

Listbox Referring To Hidden Range

Apr 11, 2014

I have this code for a texbox to search into a listbox

It's working properly but sometimes it doesn't look at all the names in the list.. probably because some of the rows are hidden?

View 4 Replies View Related

Referring To A Worksheet In A Closed Workbook.

May 5, 2009

I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month. Kind of lost on how to possibly make this work. I basically need something like: =location/[workbook.xls]worksheet!cell - where worksheet = B12 and B12 =month(today()) and is formatted to MMMM

View 4 Replies View Related

Referring To A Cell For The Row Position On A Formula

Jul 8, 2008

I'm trying to find the details required to refer to contents of a cell and then treat the value as the formula. IE for Sum(B5:B8), i'd like to have two cells, one with a 5 in it and one with the 8.

I know its possible if i put "b5" as the cell content using indirect - but I'll be using the same cell value to update formulas in different columns so I cant afford to include the B. I assume theres a very simple method along the lines of Sum((B&(Cell(a4)):B&(Cell(a5)))?

View 9 Replies View Related

Referring To Range() Using Shortcut Notation

Sep 23, 2008

I have noticed in many of the Excel help files that the developers liked to use shortcut notation for Range reference in VBA.

For example, Range("A1:B10") would be [A1:B10].

I was doing a bit of testing with this, and was not able to make the shortcut notation work with a variable. Does the shortcut method have the capabilities to do the equivalent of Range("A1:B" & LR)?

If it does have that capability, is it just due to force of habit that we always use Range() to refer to ranges, or would there be a more in-depth reason.

View 9 Replies View Related

Referring To Code From Multiple Events

Jul 14, 2006

If I have a button on each of numerous sheets, how do I get all of the buttons to refer to the same bit of code, without having to copy it to the click event of each?

View 4 Replies View Related







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