# Worksheet Referencing And Position Recognition

Jun 4, 2009

I have a bit of a complicated one here so I have attached my book, its probably easier to undertstand the query by looking at the book. Hopefully somebody can help me out here.

I have 2 worksheets:

A results worksheet - this contains all category of results, each category has been given a specific sort id.

A foreign worksheet - this contains all categories found on the results worksheet with sort id = 7.

Now the complicated bit. The whole exercise is too pick up the correct exchange rate for the foreign category (sort id 7). each foreign type has a specific number at the end of its description in brackets e.g (3).

At the bottom of the "results worksheet" is a key which says what type of exchange rate to use for this type e.g. (3) Price of CAD 22.9 converted using exchange rate of CAD 1.9645 = £1.

What I need to do is on the foreign worksheet, it to recognise the number in the item description, keep it in mind then switch over to the "results worksheet" find the keys at the bottom of the page (the key is not necessarily in the same place all the time!!), match the number in the items description with the corresponding key and then find the exchange rate.

As i said very difficult to explain, since I reletively zero experience with this kind of work in excel. Luckily I been muddling my way through a work project gradually thanks to a forum member here. so i hope we can keep this up!

edit:
two restrictions in the way this solution can be done:
1- Results worksheet can not be modified, but it can be referenced to using INDIRECT, and you can make any number of modifications outside of the results worksheet.
2. Any solution must be automatic.
/edit

I have attached my workbook and highlighted the bits I need to fill and the picks I need to pick up.

Would be ever so grateful if some1 could show me the light here.

## Copy Worksheet In Workbook With All Formulas On New Worksheet Referencing Previous Worksheet

Apr 21, 2012

I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.

How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?

## Referencing A Cell Based On Position Of Another Cell

Jan 23, 2009

Right now cell B7 is referencing cell D3. However I want this to be dynamic in the sense that suppose I change the Phase 2 (which starts from ww3 (cell D3)) to ww5 (cell F3) then I would like that B7 should also get updated to F3 instead of D3.

Basically the Development phase of "Support" Project starts only when Phase 2 of "Project 1" starts.

ww = Work Week.

In short cell B7 should always be in sync with the ww of Phase 2 of Project 1.

## Conditionally Hiding / Unhiding Rows In One Worksheet By Referencing Cell In Different Worksheet

May 3, 2013

coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.

As a simplified example:

Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!

So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.

I was thinking of using something like this:

Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If

I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.

## Fix Position Of Picture On Worksheet

Mar 22, 2008

In one column I want to reference the maximum value entered in another column. In a third column, I want to refernce a different row that is in the same column as the maximum value.

## Placing A Worksheet In A Specific Position

Oct 6, 2009

Part of my code adds a Worksheet to the selected Workbook and then re-orders them alphabetically, but one of the Worksheets (Called home) should always remain at position 1. Is there a way to dictate exactly which postion it should be in so that I can place it there after the sort is complete?

## Picture Name & Position Of Images In Worksheet

Mar 1, 2008

Using excel and Crystal Ball I generated a workbook that calculates via VBA Value at Risk and diverse financial charts and ratios.

I have a second workbook, the template, where this values are pasted generating a report.

Up to this point the macro works correctly, the problem is that some of the Crystal Ball charts are pasted as images and I dont know how to "handle" this images.

I reached the point where I can paste the image in the template.

I want the macro to:

1) Detect the images in the template worksheet
2) Determine a position and size for each image

## Current Cell Position For A Non Active Worksheet

Dec 15, 2009

I need the cell address where the cursor is (in a non active worksheet).

## Restrain/Lock Image Into Position On Worksheet

Aug 18, 2006

Is it possible by using Visual Basic coding to position an image on a worksheet so that no matter what the screensize is it will always appear in the center of the screen, a corner or a side?

## Determine Position Of Shape/Button On Worksheet

Jun 2, 2008

I have written a small piece of code that handles clicks on buttons on an excel spread sheet. When a button is pressed, i need to know the row number where the button resides in.

application.caller.topleftcell.row

to get the row number and

application.caller.topleftcell.column

to get the column number.

However, when i use the row variant, it ALWAYS returns row number 1. When i use the column variant, it returns the column number it is actually in.

i have added the full code that is creating the buttons below, and also the part that displayes the rownumber that is incorrect.

Private Sub Workbook_Open()
Dim name
Dim time As Integer
time = InputBox("Typ the hour you are checking" & vbCrLf & "eg: 7, 10, 13, 14, 15, 16, 17, 18, 19", "Which Check")

## Button Position/Coordinates On Worksheet. Discrepancy Between Versions

Apr 30, 2008

Add Multiple Buttons That Select Row Of Housing Cell. One of my Macro's places a button on a sheet using Top/Left;

Range("J" & count).Left, Range("J" & count).Top, 50, 12)

When I try to find the row of the clicked button using the following code;

The code behaves differently in Excel 2002 and 2007. In 2002 it returns the row the button sits on, but 2007 returns the cell above the button. This could be because the workbook is an XLS running in Excel 2007 in compatibility mode? Is there a way to "nudge" the button down a little after it has been placed using .top/.left ? Or is the only way around this something like;

count = ActiveCell.Row
If Application.Version >= 12 Then
count = count + 1
End If

Is this a bug, or something that I am doing wrong ? The workbook needs to be a .xls to ensure that it works in older versions of Excel.

## Macro To Create Worksheet, Change It's Position, And Paste Date Into It

Apr 26, 2009

I need a macro that will create a new blank worksheet, move it to the last spot and paste the entire contents of the sheet named "master" into the newly created sheet.
I have tried to do this with the recorder but have been unable to get it to work.

## Referencing Every Nth Row From Another Worksheet

Dec 19, 2008

I need to create a worksheet whose sole purpose is to return the values from every 17th row from another worksheet in the same workbook.

I have spent a few hours trying to research the correct answer and OFFSET comes close but either I'm doing it wrong or it's not what I need.

I want to be able to drag the (every 17th row from sheet 1) formula down about 100 rows.

Assume A1 as starting place. I would prefer a formula answer as opposed to a macro or VBA.

## Referencing The Name Of A Worksheet

Jun 24, 2009

way to reference the name of a worksheet in a cell, or even better, use a cell reference to dictate the name of a worksheet.

In other words, I would like to create a new sheet, and set it's name ='Sheet1'!C1 so that if I changed C1, the worksheet would automatically be renamed to whatever is typed in C1.

If that is not possible, I would like to have a cell be tied to the name of a given worksheet, so that if I renamed the worksheet, the cell would change accordingly...similar to the &[Tab] function used in headers/footers.

## Referencing Another Worksheet

Dec 23, 2009

I have an XLS spreadsheet which updates cells through an ODBC connection to a database. The information is pulled across ok and 90% of the Vlookup and IF statements work. The problem arises on a summary page.

When i start off the summary page has cell references in numeric order to another spreadsheet and pulls back the information to 4 cells Across and 1000 Down. ='Campaign Responses - Cash Break'!A2

This is a spreadsheet that changes once the queries have run and a number between 1-1000 records are returned. However once the data has changed and the page refreshed. Not all the results have been pulled across to the summary pages. A few are there but the numeric sequence jumps from......

## Referencing Text In A Worksheet

Oct 22, 2007

I have been having problems referencing text in a worksheet.

I tried cleaning it, didn'twork.

When I do T() some cells come back empty. I don't know why?

How do I get the text?

## Referencing Worksheet In Formula?

Dec 8, 2011

I have the formula:
=VLOOKUP(C10,'[Ticket Out Comparison Report.12.4.11.xls]TO'!\$C:\$O,10,0)

I want to do something like putting 12.4.11 in cell B1 and change the formula to something like:
=VLOOKUP(C10,'[Ticket Out Comparison Report.B1.xls]TO'!\$C:\$O,10,0)

## Referencing A Cell In A Worksheet.

Jul 25, 2006

I used to think I was moderately intelligent. Certainly, I thought I knew my way around a computer. Then I got the bright idea to try and learn VBA. Since then every attempt I've made in trying to produce the most basic of code results in mad fits of rage and me wanting to slit my wrists. I've read half a textbook and I keep trying to write simpler and simpler code to get some sort of result, just to give me the drive to carry on but no matter what I try it always goes completely wrong.
So before I stick me head through the computer screen, here is today's attempt. I want to select a worksheet. I then want to copy the contents of cell A2 down to A1200.

Private Sub CommandButton1_Click()
Sheets("Calls Outcome").Visible = True
Worksheets("Calls Outcome").Activate
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A1200")
End Sub

I’ve stepped through the code and I get an error on “Selection.AutoFill Destination:=Range("A2:A1200")”. After spending several eight-hour days trying to get something out of VBA I have zero energy and zero patience.

## Referencing Named Range Without Using Worksheet Name

Aug 13, 2014

I want to export data from a set of named ranges in workbook x to another set of ranges in workbook y. The names of the ranges to be exported from and to are spesified in a table "Table_Export". Problem is I do not want to spesify in what sheets these respective ranges are located, seeing that I do not know the actual sheet names. But since the ranges are global it should somehow be possible to reference them in vba without referencing the respective worksheet?

The relevant code can be found below where either of the two 'commented lines within the if statement are what I want to accomplish.

Dim x As Workbook
Dim y As Workbook
Set x = ThisWorkbook
Set y = Workbooks.Open(Range("Export_to").Value)
Dim export_control As ListObject

[Code] ....

## Referencing Range In Specific Worksheet?

Mar 19, 2014

I've tested and I'm getting errors trying to reference a range in a specific sheet.

I want to sum a range for further calculation and can get this to work perfectly if I don't specify a sheet - but it sums the range in the wrong sheet as the code is within a With statement range:

[Code].....

I've checked that wsSumm contains the 'Summary' worksheet value and that's all fine, I've also tested the following:

[Code] ....

I've also tried replacing wsSumm with Worksheets("Summary") and Worksheets(1), but get exactly the same results, so no it's not a fault with the worksheet variable. Also tried using Application.WorksheetFunction.Sum, but again exactly the same results.

So, why I can't sum the range on the specified worksheet? Is is something to do with being inside a With statement for another worksheet - though I've not come across problems with this before...

## Tab Named By Referencing A Cell Within A Worksheet?

May 6, 2008

Is it possible to have a tab named by referencing a cell within a worksheet?

## Formula To Referencing Future Worksheet

Aug 19, 2009

Reference a worksheet that has not yet been created, avoiding the #REF! result?

The formula I have is correct, however the macro generates new worksheets throughout the month in which the totals sheet is referencing. The problem being that the totals sheet is referencing worksheets not yet created.

## Prevent Referencing Hidden Worksheet

Jun 3, 2008

I have a spreadsheet which I'm using as a log-in front for a series of other workbooks. The usernames and passwords are stored hidden cells within a "very hidden" worksheet. The workbook is protected and VBA code itself is password protected.

In short no-one can directly view the passwords (unless the password to unprotect it all is known.

What I have found is that someone can use a formula to reference the cells containing the user details. "=a1" for example.

Is the a method hiding the contents of a cell from excel itself? I want the vba script to be able to see the value, but any "=a1" formulas to return a blank.

## Generic Formula Referencing Prior Tab/worksheet

Jan 20, 2010

The workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc.

For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2

That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet.

Is there a way to reference the prior worksheet without using it's specific name?
=C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.

## Excel 2010 :: Referencing To Worksheet Crashes

Oct 31, 2011

I use Excel 2010, 64-bit-version with Windows 7. I have one workbook, where whenever I create a formula that references to one specific sheet, the Excel crashes and cannot be quitted even with Task Manager. I have to restart the whole system.

I do not have any worksheet events in either sheet. This is the same workbook that crashes if all cells are selected from the top-left corner of the worksheet.

I tried by deleting unnecessary COM add-ins but that didn't work.

## Referencing Defined Worksheet Variable In Formula In VBA?

Oct 19, 2012

I have created a userform that once the user clicks the okay button, the information is transferred to a worksheet template named 'PO Template', copied, and renamed. That portion works great. I then have code for a summary worksheet named 'PO Log' that finds the next empty row on the log and enters a formula referencing back to the newly created 'PO Form' (note - 'PO Form' is a variable as the actual worksheet name changes with the user input). The formula on the 'PO Log' references the 'PO Form' literally, which gives me a REF! error because there is no sheet actually named that. I've tried defining POForm in the code as a string so I can enter it into the formula code but I get an error because it's already defined as a variable. I've entered the sub routine for clicking the okay button below:

Code:
Sub OkayButton_Click()
'Make PO Template Active
Sheets("PO Template").Activate

[Code]....

## #REF! Error Referencing Worksheet After Importing Sheet

Feb 9, 2008

I have a template i'm making that is full of formula references to sheets not yet in existance in the workbook. what i want to happen is this: My code imports data files, renames the tabs to 1, 2, 3, 4... The formulas i already have are set in the CONCATENATED tab.... an example of such a formula is: ='1'!\$P2.

so this should work, and yet it doesn't. the sheet is imported as it should be, the tab is renamed to 1, and yet the CONCATENATED sheet still shows #REF for the cell value. if i click in the cell, click in the text bar and enter out, the formula updates. there apparently is no option that i KNOW OF (although i'm sure there is something out there that does...) that will update this without me entering into the text bar and entering out.

Question: how do i manually update all references once the sheets to those references have been imported?

## Date Recognition

Nov 30, 2009

I have a spreadsheet that uses data which is linked to dates on which events are being held.

So, event 1 happens on 5/12/09, event 2 on 6/12/09. What I would like is the spreadsheet to identify the next event occurring and copy the information into a different cell on the spreadsheet.

## Number Recognition

Apr 25, 2008

I have a column of data (numbers) that are expressed incorrectly i.e 1,000 actually means 1, 0,3 actually means 0.3 and 15,500 actually means 15.5. I can make the first and second examples OK by using the replace command, but how can I check the number format of the cell and then make it change to what I want. All these numbers are in random order and sequence. I'm using the replace command in a macro, is there something I could put in the macro to solve my problem?

## Copy/paste Formula Referencing Different Worksheet On Each Line

May 18, 2009

I have a workbook with ~80 sheets. One sheet is a summary sheet containing data from each of the other sheets. The formula to display the data is pretty easy, but I need a function to copy the formula but increment the worksheet reference in each cell.

For example:
the formula in one cell is ='17'!\$AI\$6
The next cell should be ='18'!\$AI\$6
and so on...

I have about 12 columns like this, so I really don't want to edit each of them individually.