PDA

View Full Version : Formula getting a cell's data from another worksheet.



mikeburg
06-15-2016, 08:37 AM
Using an Excel formula, I am trying to get a cell's data from another worksheet referring to the worksheet name from another cell.

For example,

Worksheet 1 B5 = 24 (the sheet tabs name)

Worksheet 1 W6 is my formula that does not work


="B5"!$A$4



Sheet 2 whose tab name is 24 $A$4 has the data, 115 in this case, I want to return to cell W6.

Apparently I can't get the syntax correct. I am using XP version of Excel. Thanks so very much! mikeburg

Kenneth Hobs
06-15-2016, 09:00 AM
Use =Indirect() and string concatenation operator, &.

mdmackillop
06-15-2016, 10:47 AM
see here for a tutorial (http://www.contextures.com/xlFunctions05.html)

mikeburg
06-15-2016, 01:55 PM
Sorry, I can't quite figure it out. The tutorial helped a lot but still not working.
What am I doing wrong:
=INDIRECT(" ' "&B5&" '!"&A4)
(I added spaces for clarity).
I really appreciate your help. mikeburg

Kenneth Hobs
06-15-2016, 02:18 PM
I see space characters in " ' " and " '!". Those should be removed.

Copy the string from a manual reference and then compare to your concatenated string less the Indirect(). You need a space between ampersands.

e.g.
='[combotest.xlsm]HONDA LIST'!$A$1
="'[" & B5 & "]HONDA LIST" & "'!A4"

Attach a simple file if needed.