following the active cell address without recalc

[haystack]

How can I get a cell in sheet1 to display the active cell address in sheet1
at all times without having to recalc when the active cell is changed? And
if that is possible, how then can I display in another cell in sheet1 the
address of a cell in sheet2 that corresponds to the active cell in sheet1
without having to recalc when the active cell in sheet1 is changed? Thanks

 

[Rick Rothstein]

The answer to the first question is to right click the tab at the bottom of
Sheet1 and select View Code from the popup menu that appears, then to
copy/paste the following code into the code window that appeared....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Address(0, 0)
End Sub

where I assumed A1 was the cell you wanted to show the active cell's address
in. As for your second question... it doesn't make sense to me. Did you
perhaps mean to say you wanted a cell on Sheet1 to reflect the address of
the active cell on Sheet2 (rather than Sheet1 as you stated)? If so, right
click Sheet2's tab, again pick View Code from the popup menu and then
copy/paste this code into the new code window that appeared...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Sheet1").Range("A2").Value = ActiveCell.Address(0, 0)
End Sub

where I assumed A2 was the cell you wanted to show Sheet2's active cell
address.

 

[Shane Devenshire]

Hi,

I didn't know how literal you wanted us to take "without recalc" you aren't
recalcing, Excel is, and it isn't recalculating the workbook but just the two
cells where the formulas are.

Put the following formulas in A1:A2 of Sheet1, for example:

=CELL("address")

=MID(CELL("filename",'Moving The Data
Field'!G19),FIND("]",CELL("filename",'Moving The Data
Field'!G19))+1,50)&CELL("address")

Then add the following code to your workbook:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Sheet1").Range("A1:A2").Calculate
End Sub

If this helps, please click yes

Cheers,
Shane

 

[haystack]

Thank you, Rick! This is getting me closer. I have no working experience
with code in Excel, but I can apply what you tell me if it is
clear...........What I am trying to do is set up a surgery call schedule
using sheet1 as a 12-month calendar in list form (text, not date format).
I'll call it the "working calendar". Each subsequent sheet uses the same
calendar format to identify the days off requested by each surgeon, one sheet
per surgeon. Each sheet has the calendar locateded in the same range as the
working calendar. The days that each surgeon asks off will be color-shaded
on that surgeon's sheet. I want to be able to select a date (cell) on the
working calendar and have the corresponding same dates (cells) be selected as
the active cell in all sheets (a 3-D linear array?). (Activating all the
sheets by using the shift key on all the sheet tabs does not do the job
completely.) Anyway, the working calendar has a dropdown list in each
calendar cell to choose from all 8 surgeons. The dropdown list refers to a
range on the working calendar sheet with each surgeon name in a separate cell
in a single column of 8 surgeons.
I want the dropdown list in the working calendar sheet to exclude surgeons
that have requested off on the day selected as the active cell in the working
calendar. So.... a conditiional formula in each surgeon's dropdowm list
range cell on the working calendar sheet needs to be able to reference the
active cell in that surgeon's particular sheet to see if it is color-shaded
or not. If the active cell in that surgeon's sheet is color-shaded, then the
formula will blank his/her name from the dropdown list in the working
calendar. I hope this clarifies things, do you think? Replacing a man's
aorta is easier than this. Thanks.