S
Sam via OfficeKB.com
Merry Christmas All,
Scenario:
Worksheet cells already populated with numeric values by Excel Worksheet
Formulas.
Is it possible with Excel VBA to insert an additional numeric value in the
Top Left of various cells already populated with numeric values by Excel
Worksheet Formulas?
Can two separate numeric values exist in different areas of the same cell?
The Worksheet cells are formatted as GENERAL.
Two Charts involved -
CHART1:
Named Range "Range3" Horizontal Numeric Labels (one Row) not in any order.
Named Range "Range2" First Veritcal Numeric Labels (one Column) in Ascending
order.
Named Range "Range1" Second Vertical Numeric Labels (one Column) not in any
order.
Use CHART1, Formula to find the Row Number that should be used for CHART2
=INDEX(Range1,MATCH(1,Range2,0),0)
The Horizontal and two Vertical Numeric Labels will be used as co-ordinates
to return the Column Number of where a specific value is housed. There are
two Columns of Vertical Numeric Labels to ensure a correct match. So a value
is matched accordingly with each of the Vertical Labels and finally, one
value matched on the Horizontal Labels. The Returned Column Number needs to
be located on CHART2.
Based on the Named Ranges, this next Formula finds the relevant values in
CHART1 and Returns the correct Column Number that must be located in CHART2.
=MATCH(INDEX(Range1,MATCH(1,Range2,0),0),Range3,0)
So, the pair of co-ordinates to locate the relevant cell for numerical value
1 is Row 11, Column 9 on CHART2.
CHART2:
Use the above co-ordinates Row 11, Column 9 to find relevant cell. Top left
of this cell should now be populated via VBA with the numeric value 1 - NOT,
overwriting existing values created by Excel Worksheet Formulas.
Could a VBA process and Formula be created to automate the positioning of
numeric values 1-100 on CHART2 by using Formulas similar to the above that
locate the relevant Row and Column Numbers firstly on CHART1.
Locate Row Number:
=INDEX(Range1,MATCH(1,Range2,0),0)
Locate Column Number:
=MATCH(INDEX(Range1,MATCH(1,Range2,0),0),Range3,0)
Required Result:
Find the co-ordinates of numeric values 1-100 on CHART1. These co-ordinates
need to be applied to CHART2 to populate the Top Left of the correct cells
with the specific numeric values 1-100.
Best Wishes
Sam
Scenario:
Worksheet cells already populated with numeric values by Excel Worksheet
Formulas.
Is it possible with Excel VBA to insert an additional numeric value in the
Top Left of various cells already populated with numeric values by Excel
Worksheet Formulas?
Can two separate numeric values exist in different areas of the same cell?
The Worksheet cells are formatted as GENERAL.
Two Charts involved -
CHART1:
Named Range "Range3" Horizontal Numeric Labels (one Row) not in any order.
Named Range "Range2" First Veritcal Numeric Labels (one Column) in Ascending
order.
Named Range "Range1" Second Vertical Numeric Labels (one Column) not in any
order.
Use CHART1, Formula to find the Row Number that should be used for CHART2
=INDEX(Range1,MATCH(1,Range2,0),0)
The Horizontal and two Vertical Numeric Labels will be used as co-ordinates
to return the Column Number of where a specific value is housed. There are
two Columns of Vertical Numeric Labels to ensure a correct match. So a value
is matched accordingly with each of the Vertical Labels and finally, one
value matched on the Horizontal Labels. The Returned Column Number needs to
be located on CHART2.
Based on the Named Ranges, this next Formula finds the relevant values in
CHART1 and Returns the correct Column Number that must be located in CHART2.
=MATCH(INDEX(Range1,MATCH(1,Range2,0),0),Range3,0)
So, the pair of co-ordinates to locate the relevant cell for numerical value
1 is Row 11, Column 9 on CHART2.
CHART2:
Use the above co-ordinates Row 11, Column 9 to find relevant cell. Top left
of this cell should now be populated via VBA with the numeric value 1 - NOT,
overwriting existing values created by Excel Worksheet Formulas.
Could a VBA process and Formula be created to automate the positioning of
numeric values 1-100 on CHART2 by using Formulas similar to the above that
locate the relevant Row and Column Numbers firstly on CHART1.
Locate Row Number:
=INDEX(Range1,MATCH(1,Range2,0),0)
Locate Column Number:
=MATCH(INDEX(Range1,MATCH(1,Range2,0),0),Range3,0)
Required Result:
Find the co-ordinates of numeric values 1-100 on CHART1. These co-ordinates
need to be applied to CHART2 to populate the Top Left of the correct cells
with the specific numeric values 1-100.
Best Wishes
Sam