D
donkirk
Attached is the workbook I need help with. From what I have bee
discovering from the Excel Macro-VBA Editor-Help screens, what I wan
to do should require fairly simple VBA code. However, I have no VB
coding experience or skills (the VBA code in Module 3 was gleaned fro
the Excel Tips Forum. Thank you Harlan Grove)
Sheets ‘Run’ through ‘Run (30)’ are identical, except that the cel
formulas on sheets 2-30 are only active if the active sheet i
“Enabled” by the user and will only populate with pulled data if al
preceding sheets are “Enabled”. The nature of the workbook is such tha
a user will always start with ‘Run’ and move sequentially through th
sheets.
Problem 1
As you can see, there is a ‘Units’ sheet. Depending where in the worl
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-mos
column for List Name.
On Sheet ‘Run’, each data entry cell subject to varying units has
light green cell near it. The cells have Data Validation referring t
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 validatio
drop-down list, the active cell is formatted with the unit as displaye
in the appropriate list on the ‘Units’ sheet.
For example: If ‘mm2’ is selected as the appropriate unit for Bit TF
(cell E27), and a value of 22.35 were entered in ‘E27’, I would wan
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 th
cells on the next sheet to pull their formatting from the same cells o
the preceding sheet. (i.e. ‘Run (2)’!E27 pulls its formatting fro
‘Run’!E27)
Problem 2
I need a summary sheet for the book. (As you can see, I’ve been tryin
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 acros
sheets ‘Run’ to ‘Run (30)’ and pull only unique values from the cell
and list them on the summary sheet in one row, sorted in ascendin
numeric order from left to right.
Then, for each unique value, I would want the sheet to further populat
itself by listing the associated data for each unique value in Ru
order in the column below.
For example: Each Run uses a Transmitter (TX No. (cell D18) in th
sheets). The same Transmitter may be used on different runs; eithe
sequentially, or in rotation with other transmitters. I would like th
summary sheet to pull the unique Serial Numbers for all Transmitter
used during the course of the job and list them in a row (se
‘Equipment Summary Sheet’ at end of workbook)
The critical data associated with the Transmitter is the number o
pulses it executed during the run (it’s mechanical and needs to b
rebuilt after ‘X’ thousand pulses). The associated data for ‘TX No.’ i
‘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’ dat
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,00
should automatically populate cell D22 of the existing ‘Equipmen
Summary Sheet’
So, I don’t want my workbook redone for me. I just need some code - o
formulas - that will resolve my problems and I’ll take it from there.
Thanks,
Don Kir
Attachment filename: geolink lwd run sheet.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=58616
discovering from the Excel Macro-VBA Editor-Help screens, what I wan
to do should require fairly simple VBA code. However, I have no VB
coding experience or skills (the VBA code in Module 3 was gleaned fro
the Excel Tips Forum. Thank you Harlan Grove)
Sheets ‘Run’ through ‘Run (30)’ are identical, except that the cel
formulas on sheets 2-30 are only active if the active sheet i
“Enabled” by the user and will only populate with pulled data if al
preceding sheets are “Enabled”. The nature of the workbook is such tha
a user will always start with ‘Run’ and move sequentially through th
sheets.
Problem 1
As you can see, there is a ‘Units’ sheet. Depending where in the worl
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-mos
column for List Name.
On Sheet ‘Run’, each data entry cell subject to varying units has
light green cell near it. The cells have Data Validation referring t
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 validatio
drop-down list, the active cell is formatted with the unit as displaye
in the appropriate list on the ‘Units’ sheet.
For example: If ‘mm2’ is selected as the appropriate unit for Bit TF
(cell E27), and a value of 22.35 were entered in ‘E27’, I would wan
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 th
cells on the next sheet to pull their formatting from the same cells o
the preceding sheet. (i.e. ‘Run (2)’!E27 pulls its formatting fro
‘Run’!E27)
Problem 2
I need a summary sheet for the book. (As you can see, I’ve been tryin
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 acros
sheets ‘Run’ to ‘Run (30)’ and pull only unique values from the cell
and list them on the summary sheet in one row, sorted in ascendin
numeric order from left to right.
Then, for each unique value, I would want the sheet to further populat
itself by listing the associated data for each unique value in Ru
order in the column below.
For example: Each Run uses a Transmitter (TX No. (cell D18) in th
sheets). The same Transmitter may be used on different runs; eithe
sequentially, or in rotation with other transmitters. I would like th
summary sheet to pull the unique Serial Numbers for all Transmitter
used during the course of the job and list them in a row (se
‘Equipment Summary Sheet’ at end of workbook)
The critical data associated with the Transmitter is the number o
pulses it executed during the run (it’s mechanical and needs to b
rebuilt after ‘X’ thousand pulses). The associated data for ‘TX No.’ i
‘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’ dat
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,00
should automatically populate cell D22 of the existing ‘Equipmen
Summary Sheet’
So, I don’t want my workbook redone for me. I just need some code - o
formulas - that will resolve my problems and I’ll take it from there.
Thanks,
Don Kir
Attachment filename: geolink lwd run sheet.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=58616