dynamic range question

[Sigmanut]

'
' Given a spreadsheet like this:
' A B C
'15 U 10 10
'16 U 7 12
'17 U 4 14
'
'In a new sheet or at least above row 12, insert the contents of column C
into the cell addressed by cells A & B.
'So the contents of U:10 is 10, U:7 is 12 and U:4 is 14.
'-----------------------------------------------------
I cannot remember how to do this and I cannot remember what this kind of
process is called when the range values come from the data.
Thanks..........

 

[Dave Peterson]

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Sigmanut]

Veery interesting answers but I think I've not posed the question I meant to.
It is really easier than you thought. I will restate and show my attempt at
the answer. My problem is in the syntax as you will see. Thanks for the help.

' Given a spreadsheet like this:
'
' Starting Result
' A B C S T U V W
'15 U 10 10 3
'16 U 7 12 4 14
'17 U 4 14 5
' 6
' 7 12
' 8
' 9
' 10 10
'
'Insert the contents of column C into the cell addressed by cells A & B.
'So the contents of U:10 is 10, U:7 is 12 and U:4 is 14.
'-----------------------------------------------------
'In the sample data above the range is A:15 to C17
Dim mValue As Integer
Dim mRow As Integer
Dim mCol As String
Dim mIdx As Integer

For mIdx = 15 To 17
mCol = Range("A" & mIdx).Value ' mCol should = "U"
mRow = Range("B" & mIdx).Value ' mRow should = 10
mValue = Range("C" & mIdx).Value ' mValue should = 10

Range(mCol&mRow).Value = mValue ' cell U10 should = 10
Next mIdx
End Sub

 

[Patrick Molloy]

so columns A and B are the target cell's address, where column A is the
Column abd B is the Row, and the value for that cell is in column C
so
row 1 says that cell U10 has the value 10 and
row 2 says cell U7 has the value 12
and so on ...

Dim rowIndex As Long
rowIndex = 1
Do Until Cells(rowIndex, "C") = ""
Cells(Cells(rowIndex, "B").Value, Cells(rowIndex, "A").Value) =
Cells(rowIndex, "C")

rowIndex = rowIndex + 1
Loop