Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
Hi,
I have the following Column A B C D E F G Row 1 Z Y X W V U 2 22 1A 2B 3C 4B 5A 6Z 3 33 1H 3H 4K 7B 8I 9L 4 44 7Q 7N 5M 3P 4G 3E What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate the help. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
Hi
Try =INDEX($A$1:$G$4,MATCH(33,$A$1:$A$4,0),MATCH("X",$ A$1:$G$1,0)) -- Regards Roger Govier "skier464" wrote in message ... Hi, I have the following Column A B C D E F G Row 1 Z Y X W V U 2 22 1A 2B 3C 4B 5A 6Z 3 33 1H 3H 4K 7B 8I 9L 4 44 7Q 7N 5M 3P 4G 3E What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate the help. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
Ahh, you really shouldn't multi-post. See another option using OFFSET (albeit
volatile, it requires only a single reference cell to anchor the source table) in your multi-post in .worksheet.functions. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "skier464" wrote: Hi, I have the following Column A B C D E F G Row 1 Z Y X W V U 2 22 1A 2B 3C 4B 5A 6Z 3 33 1H 3H 4K 7B 8I 9L 4 44 7Q 7N 5M 3P 4G 3E What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate the help. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
It worked fine for the example I gave, but it didn't work in the my large
spreadsheet. I have rows labeled 1 - 25000, and columns labeled 1 - 150, and am trying to call and possible row column combo. I.e. Row 4500, column 45 gives a value at there intersection. "Roger Govier" wrote: Hi Try =INDEX($A$1:$G$4,MATCH(33,$A$1:$A$4,0),MATCH("X",$ A$1:$G$1,0)) -- Regards Roger Govier "skier464" wrote in message ... Hi, I have the following Column A B C D E F G Row 1 Z Y X W V U 2 22 1A 2B 3C 4B 5A 6Z 3 33 1H 3H 4K 7B 8I 9L 4 44 7Q 7N 5M 3P 4G 3E What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate the help. Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
Didn't seem to work, it returned a value but not the right value
.. Maybe I had an error in the formula? Here is the formula I used =OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5, 'data'!A:A,0)-1) 'data' being my main spread sheet, Sheet1 being the secondary sheet. B15 being the column variable, and B5 being the row variable. Also I get ref when I change the row variable much above 100. "Max" wrote: Ahh, you really shouldn't multi-post. See another option using OFFSET (albeit volatile, it requires only a single reference cell to anchor the source table) in your multi-post in .worksheet.functions. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "skier464" wrote: Hi, I have the following Column A B C D E F G Row 1 Z Y X W V U 2 22 1A 2B 3C 4B 5A 6Z 3 33 1H 3H 4K 7B 8I 9L 4 44 7Q 7N 5M 3P 4G 3E What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate the help. Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
Think you adapted it wrong ..
.. Here is the formula I used =OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5, 'data'!A:A,0)-1) Try this adaptation instead: =OFFSET(data!$A$1,MATCH(Sheet1!B5,data!A:A,0)-1,MATCH(Sheet1!B15,data!$1:$1,0)-1) where Sheet1's B5 houses the row header numbers: 22, 33, 44... and Sheet1's B15 houses the col header letters: Z, Y, X ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "skier464" wrote: Didn't seem to work, it returned a value but not the right value . Maybe I had an error in the formula? Here is the formula I used =OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5, 'data'!A:A,0)-1) 'data' being my main spread sheet, Sheet1 being the secondary sheet. B15 being the column variable, and B5 being the row variable. Also I get ref when I change the row variable much above 100. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
Thanks that seemed to have solved the problem
"Max" wrote: Think you adapted it wrong .. .. Here is the formula I used =OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5, 'data'!A:A,0)-1) Try this adaptation instead: =OFFSET(data!$A$1,MATCH(Sheet1!B5,data!A:A,0)-1,MATCH(Sheet1!B15,data!$1:$1,0)-1) where Sheet1's B5 houses the row header numbers: 22, 33, 44... and Sheet1's B15 houses the col header letters: Z, Y, X ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "skier464" wrote: Didn't seem to work, it returned a value but not the right value . Maybe I had an error in the formula? Here is the formula I used =OFFSET('data'!$A$1, MATCH(Sheet1!B15, 'data'!$1:$1, 0)-1, MATCH(Sheet1!B5, 'data'!A:A,0)-1) 'data' being my main spread sheet, Sheet1 being the secondary sheet. B15 being the column variable, and B5 being the row variable. Also I get ref when I change the row variable much above 100. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
Hi
Then just extend the ranges to =INDEX($A$1:$ET$25000,MATCH(33,$A$1:$A$25000,0),MA TCH("X",$A$1:$ET$1,0)) -- Regards Roger Govier "skier464" wrote in message ... It worked fine for the example I gave, but it didn't work in the my large spreadsheet. I have rows labeled 1 - 25000, and columns labeled 1 - 150, and am trying to call and possible row column combo. I.e. Row 4500, column 45 gives a value at there intersection. "Roger Govier" wrote: Hi Try =INDEX($A$1:$G$4,MATCH(33,$A$1:$A$4,0),MATCH("X",$ A$1:$G$1,0)) -- Regards Roger Govier "skier464" wrote in message ... Hi, I have the following Column A B C D E F G Row 1 Z Y X W V U 2 22 1A 2B 3C 4B 5A 6Z 3 33 1H 3H 4K 7B 8I 9L 4 44 7Q 7N 5M 3P 4G 3E What I need is if ROW 1 = X and Column A = 33 then the answer is 4K. This is for a spreadsheet that is 200 columns by 25,000 rows so I really appreciate the help. Thanks! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Analysis Question
You're welcome !
Glad you got it working .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "skier464" wrote: Thanks that seemed to have solved the problem |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Analysis Toolpak Question | Excel Discussion (Misc queries) | |||
multiple items in database | Excel Worksheet Functions | |||
Database Functions - question using formulas as criteria | Excel Worksheet Functions | |||
Import from Database using field from excel. | Excel Discussion (Misc queries) | |||
The Template Wizard retains the original location for my database | Excel Discussion (Misc queries) |