excel - Unsure of how to use cell data to get data from another worksheet -

this weird sort of function i've never tried use before, i'm not sure how go asking without using example.

essentially, have column of strings represent datasheets in .xls file. each row has data in corresponds datasheet named in said column. in case explanation insufficient, here's example:

worksheet 1 : worksheet 1 value 1 : worksheet 1 value 2 : ....... worksheet 2 : worksheet 2 value 1 : worksheet 2 value 2 : ....... etc... 

at moment, spreadsheet requires manually update worksheet reference each column populate data, i.e. every cell, need manually fill out worksheet name in equation:

='worksheet 1'!b111 

this works, suboptimal, have large number of worksheets. want able able fill out cell 1 (the worksheet name) , have every other cell use worksheet name reference, so:

column      : column b : column c : ... "worksheet 1" : =a1!b111 : =a1!b34  : ... "worksheet 2" : =a2!b111 : =a2!b34  : ... 

first question: possible?

second quesion: how?

sorry if has been answered already, barely figure out how ask question in first place.

you can use indirect() function. text worksheet 1 in cell a1 can use

=indirect("'"&$a1&"'!b111") 

since sheet name can contain spaces, need start text of indirect single quote, append cell sheet name, append closing single quote, exclamation mard , cell reference.

if cells in column refer b111, copy formula down , change sheet name in column a. copy formula across , adjust cell reference b111 whatever desired before copying down.

if want pre-populate worksheet these values, fire macro recorder, create new sheet, go existing sheet , copy , paste new sheet, stop macro recorder. give starting point code need.