Data from Multiple Sheets

[JBC]

I have a workbook with sheets labeled for each day of the month 1-31. The form is the same on all the sheets. I need to retrieve data from each of these sheets and put in a data sheet for charts. I use the formula ='1'!b31 meaning sheet 1 cell b31. however when I want to copy this formula to be ='2'!b31 It will copy ='1'!b32. What am I doing wrong? I need to retrieve all the cell b31 from all 31 sheets.

Thanks
JBC

[Palmetto]

What am I doing wrong?

Classic mistake in spreadsheet design.

See this link for Best Practices - Efficient Spreadsheet Design

Use a single sheet to hold all related raw data, then use Pivot Table, look up functions, Advanced Filter and other method to extract the data and create reports.

[JBC]

Thanks for your response. However the form would not be very user friendly for the person filling out the form. (Too much data). I would really like to know if you can copy this formula? If not what would you suggest?

Thanks Again.

[Huron]

If you want to list the values starting in row 2 (to allow for a header in row 1) try the following formula copied down any column in your summary sheet

Please Login or Register  to view this content.

[Palmetto]

I need to retrieve all the cell b31 from all 31 sheets.

If you are copying the cells down a column then Huron's suggested formula will work, with the following revision to make the cell reference absolute:
=INDIRECT("'"&ROW()-1&"'!$B$31")

If you are coping across columns, starting from column-A, then:
=INDIRECT("'"&COLUMN()&"'!$B$31")

However the form would not be very user friendly for the person filling out the form. (Too much data).

The logic of this statement escapes me, not knowing your WB/sheet structure. But since the structure all of your sheets (daily) can be assumed to be identical, what is the difference between entering the data on a single sheet (efficiency) versus many sheets (single use, inefficient)?

Long-term, your approach makes the task of extracting, analyzing and other data manipulations much more difficult.

[Huron]

@Palmetto

You don't need to add $. The "B31" part of the formula is text and so doesn't change when copied.

[Palmetto]

You don't need to add $. The "B31" part of the formula is text and so doesn't change when copied.

Yeah, I know. It occurred to me after posting but I didn't bother to make a correction since it had no real impact on the results.