=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

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.

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!

What about using INDIRECT? This will return the

valuein 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.

Not at office. Will try Indirect on Monday. Thank you

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.

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.

Solutions work quite nicely. Thanks for your immediate responses. I can build a lot on what you suggested.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History