Extracting information from workbook sheets to a collector page

covlocks

New Member
Joined
Apr 11, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I raise a standard worksheet record for each engraving order and would like would like to keep track of these orders on a collector worksheet

Many years ago I used a similar system to collect information on a workbook that contained hundreds of sheets referring to many different components on a design, but that knowledge has drifted away in time, not having used vba for many years.

The 'indirect' command doesn't work for me, so I am hoping an excel wiz can help me please. Column A is obviously a worsheet/JobNumber which will just stay in the same format, pulling in info from the same cells on each sheet.

Without copying and editing 100's of cells, I would just like column B> just to pull in the information from the sheet name in column A.

Any help would be brilliant.
sheet.PNG
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Welcome to the Forum!

The 'indirect' command doesn't work for me ....
INDIRECT() is the usual way to deal with these issues. Here's a simple example, but the formula can be made more complex if required.

AB
1SheetCustomer
2Oder101John Doe
3Oder102John Doesn't
Master
Cell Formulas
RangeFormula
B2:B3B2=INDIRECT("'"&A2&"'!A2")

A
1Customer
2John Doe
Oder101

A
1Customer
2John Doesn't
Oder102
 
Upvote 0
Welcome to the Forum!


INDIRECT() is the usual way to deal with these issues. Here's a simple example, but the formula can be made more complex if required.

AB
1SheetCustomer
2Oder101John Doe
3Oder102John Doesn't
Master
Cell Formulas
RangeFormula
B2:B3B2=INDIRECT("'"&A2&"'!A2")

A
1Customer
2John Doe
Oder101

A
1Customer
2John Doesn't
Oder102
Thank you - I will try that
 
Upvote 0
Thank you - I will try that
It worked perfectly, thank you.
I think I missed out a couple of quotes when trying it before, the number of quotes required in the formula confused me.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top