Solved excel get worksheet name and cell number

June 24, 2011 at 11:05:41
Specs: Windows XP, 1gb
=ADDRESS(ROW(List!$A$2:$A$2000)+MATCH(D4,List!$A$2:$A$2000,FALSE)-1,COLUMN(List!$A$2:$A$2000)+3) provides me the absolute address of the cell found in tab list!

can the result show the tab list! too (list!$d$50), rather than just ($d$50)? d4 is the current entry


See More: excel get worksheet name and cell number

Report •

#1
June 24, 2011 at 11:34:35
Assuming the file has been saved, this formula will return the tab name of the sheet in which the formula resides:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Concatenate that, along with an exclamation point, and you should be all set.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) & "!" & ADDRESS(ROW(list!$A$2:$A$2000)+MATCH(D4,list!$A$2:$A$2000,FALSE)-1,COLUMN(list!$A$2:$A$2000)+3)

If you are using this in a sheet other than List, then you'll need to use the MID(CELL... formula in List and then reference the cell in which it resides:

=List!B1&"!"&ADDRESS(ROW(List!$A$2:$A$2000)+MATCH(D4,List!$A$2:$A$2000,FALSE)-1,COLUMN(List!$A$2:$A$2000)+3)

In other words, you can't pull the tab name of a Sheet2 into Sheet1 directly.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 24, 2011 at 14:47:09
Your reply is appreciated! Using the MID, etc. I get the tab I'm working in, as you noted if not in current worksheet. I can't do it in List!

Looking at it differently, I need to populate the cells in LDs! in columns K,L,M, etc. with data found by a lookup for LDs! D4 in List! without having to do multiple lookups. I may have several hundred cells (in LDs!d4:d...) that i need data in K,L,M for operator choices in how to proceeed.

I figured if I could get the TAB and Address, I could use Offset to populate the LDs! cells from List!


Report •

#3
June 24, 2011 at 15:33:40
✔ Best Answer
What about using INDIRECT?

This will return the value in D_row (e.g. D50), not the address D50.

=INDIRECT("List!"&ADDRESS(ROW(List!$A$2:$A$2000)+
MATCH(D4,List!$A$2:$A$2000,FALSE)-1,
COLUMN(List!$A$2:$A$2000)+3))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 25, 2011 at 07:46:05
Not at office. Will try Indirect on Monday. Thank you

Report •

#5
June 27, 2011 at 11:43:38
Thanks very much. This was a very good way for me to get what I needed without successive lookup functions - just add 1 to the index and get next value.

Report •

#6
June 27, 2011 at 12:46:00
Not knowing exactly what you were doing with your lookups, I kind of thought that that was where you were headed, assuming the INDIRECT function worked for you.

I don't know if you are familar with the ROW() and COLUMN() functions, but they can be used to "add 1" to a value within a formula to make it easy to autofill.

For example, you have this as part of your formula:

COLUMN(list!$A$2:$A$2000)+3)

If that formula was in Row 3, you could use this and get the same result:

COLUMN(list!$A$2:$A$2000)+ROW())

If that formula was in some other row, e.g. 5, you could use this and get the same result:

COLUMN(list!$A$2:$A$2000)+ROW()-2)

That way you can drag the formula down and ROW() will increment automatically.

The same concept applies to the COLUMN() function.

Hope that helps!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
June 27, 2011 at 14:19:09
Solutions work quite nicely. Thanks for your immediate responses. I can build a lot on what you suggested.

Report •

Ask Question