Formula to copy information from one worksheet to an overview sheet

Hi Jen (in Quality Control?),

My brain gets like that too (and too often). See the attached where I've used an Indirect Address combination. The address function takes the last parameter/argument as the sheet name which makes this work.

Indirect Address using Sheet argument.xlsx

Given your current layout, (and I'll comment in a minute on that) the simplest way would be to use Row 1 on the Overview sheet to hold the Cell reference on the indivudual sheets. So for instance in B1- enter 'B1', in C1 enter 'F1', in D1 enter 'B3'...etc

Then the formula in B4 copied across and down would be

Formula:

Please Login or Register  to view this content.

However why are you collecting data in individual sheets like this. You are seriously compromising any analysis and making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database. In fact use the Overview sheet to record every single transaction. To make it easier to enter data use a single input row above the data and when everything is entered have a simple macro that would copy this row and add it to the database.