Conditional Number Formatting & Selective Summing

Attached is the workbook I need help with. From what I have been
discovering from the Excel Macro-VBA Editor-Help screens, what I want
to do should require fairly simple VBA code. However, I have no VBA
coding experience or skills (the VBA code in Module 3 was gleaned from
the Excel Tips Forum. Thank you Harlan Grove)

Sheets ‘Run’ through ‘Run (30)’ are identical, except that the cell
formulas on sheets 2-30 are only active if the active sheet is
“Enabled” by the user and will only populate with pulled data if all
preceding sheets are “Enabled”. The nature of the workbook is such that
a user will always start with ‘Run’ and move sequentially through the
sheets.


Problem 1

As you can see, there is a ‘Units’ sheet. Depending where in the world
the user is located, and depending on the preferences of the client,
different units are used (e.g.’Ft’ or ‘m’ for distance)

Each row of this sheet has a named list created using the left-most
column for List Name.

On Sheet ‘Run’, each data entry cell subject to varying units has a
light green cell near it. The cells have Data Validation referring to
the appropriate List from the ‘Units’ sheet.

What I want to occur is application of conditional number formatting,
such that, depending on the value selected from the validation
drop-down list, the active cell is formatted with the unit as displayed
in the appropriate list on the ‘Units’ sheet.

For example: If ‘mm2’ is selected as the appropriate unit for Bit TFA
(cell E27), and a value of 22.35 were entered in ‘E27’, I would want
the cell to display the value as 22.35 mm2


Problem 1.a

So as not to have to reset the units on each sheet, I would like the
cells on the next sheet to pull their formatting from the same cells on
the preceding sheet. (i.e. ‘Run (2)’!E27 pulls its formatting from
‘Run’!E27)


Problem 2

I need a summary sheet for the book. (As you can see, I’ve been trying
with my sparse Excel skills to do this on my own, but I give up!)

What I would like is code that would scan a referenced cell across
sheets ‘Run’ to ‘Run (30)’ and pull only unique values from the cells
and list them on the summary sheet in one row, sorted in ascending
numeric order from left to right.
Then, for each unique value, I would want the sheet to further populate
itself by listing the associated data for each unique value in Run
order in the column below.

For example: Each Run uses a Transmitter (TX No. (cell D18) in the
sheets). The same Transmitter may be used on different runs; either
sequentially, or in rotation with other transmitters. I would like the
summary sheet to pull the unique Serial Numbers for all Transmitters
used during the course of the job and list them in a row (see
‘Equipment Summary Sheet’ at end of workbook)

The critical data associated with the Transmitter is the number of
pulses it executed during the run (it’s mechanical and needs to be
rebuilt after ‘X’ thousand pulses). The associated data for ‘TX No.’ is
‘No. of Pulses’ (data cell A43). For each run a Transmitter is used on,
I want the summary sheet to automatically pull the ‘No. of Pulses’ data
and place it in the appropriate cell in the summary sheet. (e.g.
Transmitter 3 used on Run 20 pulses 30,000 times. The value 30,000
should automatically populate cell D22 of the existing ‘Equipment
Summary Sheet’

So, I don’t want my workbook redone for me. I just need some code - or
formulas - that will resolve my problems and I’ll take it from there.

Thanks,

Don Kirk

Attachment filename: lwd run sheet.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=590785
---
Message posted from http://www.ExcelForum.com/