• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell Address

Belleke

Belleke

Well-Known Member
I have this formula.
Code: 
=INDIRECT("Sheet" & COLUMN()-COLUMN($C$1)+1 & "!$A$1")
But instead of the value I want the cell address.
Thanks in advance
 
Last edited by a moderator:
Did you find this?

exceljet.net

ADDRESS Function

The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative, mixed, or absolute format, and can be used to construct a cell reference inside a formula.
exceljet.net exceljet.net
 
That is not wat I am looking for, I am familiar with ADDRESS.
The idea is to enter a value in A1,the formula will search the tabs (better yet a list of tabs) and then display the cell address in C1
 
Last edited by a moderator:
Belleke said:
That is not wat I am looking for, I am familiar with ADDRESS.
The idea is to enter a value in A1,the formula will search the tabs (better yet a list of tabs) and then display the cell address in C1
Click to expand...
From the description it appears below is your requirement:
- Enter a value in A1
- Search for the value in multiple tabs in the workbook
a. Are you looking for an exact match or partial match?
b. Are you looking for a specific column or all columns in each tab?
c. Is the number of sheets to be looked at going to change as time changes?

At any rate, unless it is 2 or 3 specific sheets and a continuous range, it will be painful to build and maintain a formula!
 
Exact match.
All columns
A defined list o sheets would be perfect.
Sheet names won't change
 
For just one sheet (data in Sheet1 cell A1 and check data in Sheet 2 column A to F) , I can think of following formula which will make it crawl.
=ADDRESS(SUMPRODUCT((Sheet2!A:F=Sheet1!A1)*ROW(Sheet2!A:F)),SUMPRODUCT((Sheet2!A:F=Sheet1!A1)*COLUMN(Sheet2!A:F)),,,"Sheet2")
I would suggest using a VBA based event code like below:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim wks As Worksheet

If Target.Address(0, 0) = "A1" Then
    Application.EnableEvents = False
    Target.Offset(0, 2).Value = "Not found"
    For Each wks In ThisWorkbook.Sheets
        If wks.Name <> Target.Parent.Name Then
            Set rng = wks.UsedRange.Find(Target.Value, , , xlWhole)
            If Not rng Is Nothing Then
                Target.Offset(0, 2).Value = "'" & wks.Name & "'!" & rng.Address
                '\\ Resetting to normal Find routine to remove tick from Match Entire Cell Contents
                Set rng = wks.UsedRange.Find(Target.Value, , , xlPart)
                Exit For
            End If
        End If
    Next wks

    Application.EnableEvents = True
End If
End Sub
 
You must log in or register to reply here.
Back
Top