I'm not sure if I have the scenario right. Assuming that Sheet1, B1 is where
you want to start.

First, I would select all the sheets (right click on Sheet 1 and choose
Select All Sheets). Click in B1, Sheet1 (answer column for all 80 sheets) and
type in =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) hit
Enter

Right click the sheets and ungroup.

This will put the sheet name in B1 for each sheet.

If you want to put the answers from Sheet2, B1:B256, into Column B of Sheet
1 type into B1, Sheet1 =Sheet2!$B:$B

You can then click on the fill box handle to drag to the right and then down
as far as you need it.

This will place the answers of each sheet in consecutive columns with the
Sheet Name at the top as a head for each column.

Hope I didn't miss the mark to badly.
--
Thank you,

scrowley(AT)littleonline.com


"Michael" wrote:

Hi

Sorry I trawled through the questions and none really applied to my issue.

Here is the scenario - I have 80 seperate worksheets in one spreadsheet
file. Column A is the list of headings (questions in my case) on each row,
and Column B is the response (data).

Each worksheet is set out like a survey so there are some rows that contain
a "heading" but no corresponding data (ie the field in column B is blank for
asthetic purposes).

So that I can run pivot tables on the collected data, I want to combine all
of the information from the 80 odd worksheets into one. They are all based on
a template, so Column A will always be the same. So what I want is to bring
all of the data in Columb B from each worksheet. I want each column to be
identifed by the worksheet name.

Is this possible??

As a footnote, FYI I describe myself as an intermediate user (so if you can,
dumb it down for me), and I may have a need of adding additional worksheets
and therefore data in the future. Finally I really really do not want to have
to cut and paste each set of data...