Drop down menu that changes the reference sheet.

For 'Description of violation' array-enter this formula and fill down.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

For the 'Cost per' enter this formula in the regular way.

Formula:

Please Login or Register  to view this content.

and for 'Quantity' this formula

Formula:

Please Login or Register  to view this content.

These all use the INDIRECT function to refer to the individual sheet names in your drop-down. If you are not familiar with INDIRECT you may want to study it's peculiar syntax.

The workbook is attached.

Does this help?

Hi FlameRetired,

Thanks! This works great except I have one question on the last portion "COLUMN($G$21:$AM$21)-MIN(COLUMN($G$21:$AM$21))+1),FALSE),ROWS($1:6)))" where the "$G$21:$AM$21" seems to be pointing to the acknowledgement sheet and all those cells are blank.

Is that correct?

Many thanks!

Yes, the purpose of COLUMN($G$21:$AM$21)-MIN(COLUMN($G$21:$AM$21))+1
is to count the number of columns in your range.

Originally Posted by ChemistB

Yes, the purpose of COLUMN($G$21:$AM$21)-MIN(COLUMN($G$21:$AM$21))+1
is to count the number of columns in your range.

ChemistB, thanks for the backup.

Originally Posted by JasonNeedsHelp

Thanks All! Much apprecate this!

You are welcome, and thanks for the rep.