Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range question
'
' 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.......... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range question
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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Sigmanut wrote: ' ' 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range question
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 "Sigmanut" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Odd Dynamic Range Question | Excel Discussion (Misc queries) | |||
dynamic range question | Excel Programming | |||
Dynamic range question | Excel Discussion (Misc queries) | |||
dynamic range question | Excel Programming | |||
Question regarding dynamic range setting | Excel Worksheet Functions |