Referrencing to a diff cell

[Squeeker]

I have one sheet layed out as follows:

0 | 1 | 2 | 3 | 4 | etc:
200
201
202
203
etc:

They are cordinates.

My second table is layed out as follows:

0 | 200 | {info} | {info} |

I want to match table one {say} 0 200 to table two 0 200 then pull
information from one of the {info} columns.

I know how to join using &" "&, but I can't figure out if to use an 'IF'
Statement or a LOOKUP or ROW or ADDRESS and how to make it work

I can get it to pull down and use absolute column, but when pull sideways,
it doesn't change correctly because when teh 0's turn to 1's it doesn't pull
them to the next column - if that makes sense.

I do notttt understand Macro's so please, if there is a way to do this in
code, that is what I am looking for.

 

[joel]

You are trying to match two columns in the 1st table to two itmes i
the 2nd table a return a 3rd column in the 2nd table.

You ned to use a sumprduct formula. this is similar to using a looku
function but you need to match two columns


=sumproduct(--(A1:A100=X1),--(B1:B100=Y1),C1:C100)

Don't worry about the sum portion if you are returning only one item.

The "--(A1:A100=X1)" is similar to an if. It will return an array o
true or false where true will be the rows that matches X1.

 

[JLatham]

Let's take one of the pieces of the problem first: that of getting the 0 1 2
3 4 entries from table 1 to show up as a list in your second table. The
easiest way to do that is to copy all of those entries and then use
Edit --> Paste Special with the Transpose option selected somewhere on the
same sheet with the second table. Then use that list as the source for your
data validation for the first column. You can always hide this 'helper'
column of data for neatness.

As for pulling data from table 1 into table 2:
Assumptions:
Table 1 begins at A1 on a sheet (A1 being the empty cell above 200 and to
the left of 0). I'll call that sheet 'Table1Sheet'.
The second table begins in H2 on the same sheet, so the 0 is in H2, the 200
is in I2.
That puts your first {info] entry at J2.

For my table 1 I went from 0 to 4 in row 1, and from 200 to 209, so that
table actually occupies the range $A$1:$F!11.

In cell J2, use this formula:
=INDEX($A$1:$F$11,MATCH($I2,$A$1:$A$11),MATCH($H2,$A$1:$F$1))

It doesn't make much sense (to me) to have multiple {item} entries next to
your
0 200 entries (the selections), since for any given pair of coordinates,
there is only one value in table 1. But maybe I'm missing something
somewhere?

 

[Squeeker]

I haven't tried your sugestions yet, because you all aren't undersatanding
quite what I am doing...

this is the formula from the cross table that works for column 0,
=IF(B$7&$A8=A!$A3&A!$B3,A!$L3, "**")
Problem is when I cntrl drag to column 1, it doent bring in the right
information.
I have played with which values should be absolute. The second table is the
table that has all the lists, I have tables for each set of cords, example
0,200 - 399, 1,200-399..... I have them each brought over into the page for
ALL(A) the lists that when i update the indivial pages, it updates in the ALL
page. From the all page, I am pulling the information for the Map(SM) page.
The formula above works well for the 0 column, problem is when the 0 turns to
1, it starts over at 200 for the B column(2nd page) and it isn't finding the
right information.

Let's take one of the pieces of the problem first: that of getting the 0 1 2
3 4 entries from table 1 to show up as a list in your second table. The
easiest way to do that is to copy all of those entries and then use
Edit --> Paste Special with the Transpose option selected somewhere on the
same sheet with the second table. Then use that list as the source for your
data validation for the first column. You can always hide this 'helper'
column of data for neatness.

As for pulling data from table 1 into table 2:
Assumptions:
Table 1 begins at A1 on a sheet (A1 being the empty cell above 200 and to
the left of 0). I'll call that sheet 'Table1Sheet'.
The second table begins in H2 on the same sheet, so the 0 is in H2, the 200
is in I2.
That puts your first {info] entry at J2.

For my table 1 I went from 0 to 4 in row 1, and from 200 to 209, so that
table actually occupies the range $A$1:$F!11.

In cell J2, use this formula:
=INDEX($A$1:$F$11,MATCH($I2,$A$1:$A$11),MATCH($H2,$A$1:$F$1))

It doesn't make much sense (to me) to have multiple {item} entries next to
your
0 200 entries (the selections), since for any given pair of coordinates,
there is only one value in table 1. But maybe I'm missing something
somewhere?

I have one sheet layed out as follows:

0 | 1 | 2 | 3 | 4 | etc:
200
201
202
203
etc:

They are cordinates.

My second table is layed out as follows:

0 | 200 | {info} | {info} |

I want to match table one {say} 0 200 to table two 0 200 then pull
information from one of the {info} columns.

I know how to join using &" "&, but I can't figure out if to use an 'IF'
Statement or a LOOKUP or ROW or ADDRESS and how to make it work

I can get it to pull down and use absolute column, but when pull sideways,
it doesn't change correctly because when teh 0's turn to 1's it doesn't pull
them to the next column - if that makes sense.

I do notttt understand Macro's so please, if there is a way to do this in
code, that is what I am looking for.

 

[JLatham]

Well, I'm getting confused by all the new sheets introduced A, ALL(A),
Map(SM) and others yet unknown? Then you mention "all page" and "second
page". Please be consistent: refer to them by their sheet name since those
are going to play into the formulas anyhow.

Are you trying to fill the

0 1 2 3
200
201
202
grid from the
0 200
entries, or the other way around (the other way around is what I thought
initially).

Actually, I think the best thing (for me at least) would be for you to send
me the workbook attached to an email and put plenty of notes about what you
want on the various sheets, show the formula you have above and where it is
on the sheets.

One thing that would even help right now would be to know what cell that
formula is in, AND what it would look like if it worked in the next cell and
what cell that formula would be in.

If you elect to send the book to me, send it to (remove spaces)
Help From@ JLatham Site. com
At least remind me of your user name here: Squeeker.

I haven't tried your sugestions yet, because you all aren't undersatanding
quite what I am doing...

this is the formula from the cross table that works for column 0,
=IF(B$7&$A8=A!$A3&A!$B3,A!$L3, "**")
Problem is when I cntrl drag to column 1, it doent bring in the right
information.
I have played with which values should be absolute. The second table is the
table that has all the lists, I have tables for each set of cords, example
0,200 - 399, 1,200-399..... I have them each brought over into the page for
ALL(A) the lists that when i update the indivial pages, it updates in the ALL
page. From the all page, I am pulling the information for the Map(SM) page.
The formula above works well for the 0 column, problem is when the 0 turns to
1, it starts over at 200 for the B column(2nd page) and it isn't finding the
right information.

Let's take one of the pieces of the problem first: that of getting the 0 1 2
3 4 entries from table 1 to show up as a list in your second table. The
easiest way to do that is to copy all of those entries and then use
Edit --> Paste Special with the Transpose option selected somewhere on the
same sheet with the second table. Then use that list as the source for your
data validation for the first column. You can always hide this 'helper'
column of data for neatness.

As for pulling data from table 1 into table 2:
Assumptions:
Table 1 begins at A1 on a sheet (A1 being the empty cell above 200 and to
the left of 0). I'll call that sheet 'Table1Sheet'.
The second table begins in H2 on the same sheet, so the 0 is in H2, the 200
is in I2.
That puts your first {info] entry at J2.

For my table 1 I went from 0 to 4 in row 1, and from 200 to 209, so that
table actually occupies the range $A$1:$F!11.

In cell J2, use this formula:
=INDEX($A$1:$F$11,MATCH($I2,$A$1:$A$11),MATCH($H2,$A$1:$F$1))

It doesn't make much sense (to me) to have multiple {item} entries next to
your
0 200 entries (the selections), since for any given pair of coordinates,
there is only one value in table 1. But maybe I'm missing something
somewhere?

I have one sheet layed out as follows:

0 | 1 | 2 | 3 | 4 | etc:
200
201
202
203
etc:

They are cordinates.

My second table is layed out as follows:

0 | 200 | {info} | {info} |

I want to match table one {say} 0 200 to table two 0 200 then pull
information from one of the {info} columns.

I know how to join using &" "&, but I can't figure out if to use an 'IF'
Statement or a LOOKUP or ROW or ADDRESS and how to make it work

I can get it to pull down and use absolute column, but when pull sideways,
it doesn't change correctly because when teh 0's turn to 1's it doesn't pull
them to the next column - if that makes sense.

I do notttt understand Macro's so please, if there is a way to do this in
code, that is what I am looking for.

 

[JLatham]

One piece that really confuses me. from your original post:
========
My second table is layed out as follows:

0 | 200 | {info} | {info} |

I want to match table one {say} 0 200 to table two 0 200 then pull
information from one of the {info} columns.
======
How are you deciding which on of the {info} items to get?? Is there always
just a possibility of 2 {info} entries, or can there be more on out to the
right?

You may be well served in the long run to add another column that combines
the two values together so that you can use a VLOOKUP() to get your results
easier.

I haven't tried your sugestions yet, because you all aren't undersatanding
quite what I am doing...

this is the formula from the cross table that works for column 0,
=IF(B$7&$A8=A!$A3&A!$B3,A!$L3, "**")
Problem is when I cntrl drag to column 1, it doent bring in the right
information.
I have played with which values should be absolute. The second table is the
table that has all the lists, I have tables for each set of cords, example
0,200 - 399, 1,200-399..... I have them each brought over into the page for
ALL(A) the lists that when i update the indivial pages, it updates in the ALL
page. From the all page, I am pulling the information for the Map(SM) page.
The formula above works well for the 0 column, problem is when the 0 turns to
1, it starts over at 200 for the B column(2nd page) and it isn't finding the
right information.

Let's take one of the pieces of the problem first: that of getting the 0 1 2
3 4 entries from table 1 to show up as a list in your second table. The
easiest way to do that is to copy all of those entries and then use
Edit --> Paste Special with the Transpose option selected somewhere on the
same sheet with the second table. Then use that list as the source for your
data validation for the first column. You can always hide this 'helper'
column of data for neatness.

As for pulling data from table 1 into table 2:
Assumptions:
Table 1 begins at A1 on a sheet (A1 being the empty cell above 200 and to
the left of 0). I'll call that sheet 'Table1Sheet'.
The second table begins in H2 on the same sheet, so the 0 is in H2, the 200
is in I2.
That puts your first {info] entry at J2.

For my table 1 I went from 0 to 4 in row 1, and from 200 to 209, so that
table actually occupies the range $A$1:$F!11.

In cell J2, use this formula:
=INDEX($A$1:$F$11,MATCH($I2,$A$1:$A$11),MATCH($H2,$A$1:$F$1))

It doesn't make much sense (to me) to have multiple {item} entries next to
your
0 200 entries (the selections), since for any given pair of coordinates,
there is only one value in table 1. But maybe I'm missing something
somewhere?

I have one sheet layed out as follows:

0 | 1 | 2 | 3 | 4 | etc:
200
201
202
203
etc:

They are cordinates.

My second table is layed out as follows:

0 | 200 | {info} | {info} |

I want to match table one {say} 0 200 to table two 0 200 then pull
information from one of the {info} columns.

I know how to join using &" "&, but I can't figure out if to use an 'IF'
Statement or a LOOKUP or ROW or ADDRESS and how to make it work

I can get it to pull down and use absolute column, but when pull sideways,
it doesn't change correctly because when teh 0's turn to 1's it doesn't pull
them to the next column - if that makes sense.

I do notttt understand Macro's so please, if there is a way to do this in
code, that is what I am looking for.

 

[Squeeker]

I sent an email with the sheets attached. Thanks for your help, sorry for
making it confusing.

Well, I'm getting confused by all the new sheets introduced A, ALL(A),
Map(SM) and others yet unknown? Then you mention "all page" and "second
page". Please be consistent: refer to them by their sheet name since those
are going to play into the formulas anyhow.

Are you trying to fill the

0 1 2 3
200
201
202
grid from the
0 200
entries, or the other way around (the other way around is what I thought
initially).

Actually, I think the best thing (for me at least) would be for you to send
me the workbook attached to an email and put plenty of notes about what you
want on the various sheets, show the formula you have above and where it is
on the sheets.

One thing that would even help right now would be to know what cell that
formula is in, AND what it would look like if it worked in the next cell and
what cell that formula would be in.

If you elect to send the book to me, send it to (remove spaces)
Help From@ JLatham Site. com
At least remind me of your user name here: Squeeker.

I haven't tried your sugestions yet, because you all aren't undersatanding
quite what I am doing...

this is the formula from the cross table that works for column 0,
=IF(B$7&$A8=A!$A3&A!$B3,A!$L3, "**")
Problem is when I cntrl drag to column 1, it doent bring in the right
information.
I have played with which values should be absolute. The second table is the
table that has all the lists, I have tables for each set of cords, example
0,200 - 399, 1,200-399..... I have them each brought over into the page for
ALL(A) the lists that when i update the indivial pages, it updates in the ALL
page. From the all page, I am pulling the information for the Map(SM) page.
The formula above works well for the 0 column, problem is when the 0 turns to
1, it starts over at 200 for the B column(2nd page) and it isn't finding the
right information.

Let's take one of the pieces of the problem first: that of getting the 0 1 2
3 4 entries from table 1 to show up as a list in your second table. The
easiest way to do that is to copy all of those entries and then use
Edit --> Paste Special with the Transpose option selected somewhere on the
same sheet with the second table. Then use that list as the source for your
data validation for the first column. You can always hide this 'helper'
column of data for neatness.

As for pulling data from table 1 into table 2:
Assumptions:
Table 1 begins at A1 on a sheet (A1 being the empty cell above 200 and to
the left of 0). I'll call that sheet 'Table1Sheet'.
The second table begins in H2 on the same sheet, so the 0 is in H2, the 200
is in I2.
That puts your first {info] entry at J2.

For my table 1 I went from 0 to 4 in row 1, and from 200 to 209, so that
table actually occupies the range $A$1:$F!11.

In cell J2, use this formula:
=INDEX($A$1:$F$11,MATCH($I2,$A$1:$A$11),MATCH($H2,$A$1:$F$1))

It doesn't make much sense (to me) to have multiple {item} entries next to
your
0 200 entries (the selections), since for any given pair of coordinates,
there is only one value in table 1. But maybe I'm missing something
somewhere?


:

I have one sheet layed out as follows:

0 | 1 | 2 | 3 | 4 | etc:
200
201
202
203
etc:

They are cordinates.

My second table is layed out as follows:

0 | 200 | {info} | {info} |

I want to match table one {say} 0 200 to table two 0 200 then pull
information from one of the {info} columns.

I know how to join using &" "&, but I can't figure out if to use an 'IF'
Statement or a LOOKUP or ROW or ADDRESS and how to make it work

I can get it to pull down and use absolute column, but when pull sideways,
it doesn't change correctly because when teh 0's turn to 1's it doesn't pull
them to the next column - if that makes sense.

I do notttt understand Macro's so please, if there is a way to do this in
code, that is what I am looking for.

 

[joel]

Try the formula below which will return the values in the table.


=SUMPRODUCT(--($B7=A!$A$3:$A$100),--($A8=&A!$B$3:$B$100),A!$L$3:$L$100)

 

[JLatham]

Joel,
Unfortunately the values in column L are text. I don't know of a trick to
use SUMPRODUCT() to return text.

I have the (8MB) workook now. And what I'm recommending at this point is to
add a column on the [A] sheet (which has the long table on it) to combine
entries in the next 2 columns (old A and B) into a text string as: =B3 & C3
Then back in on the sheet (named SM) with the coordinates type table (200
columns by 6200+ rows) it uses a VLOOKUP() where I've set things up to
duplicate the entries in column A of sheet [A] by getting the first part from
row 7 always and the second part from column A always. So at the moment,
that formula looks something like:
=VLOOKUP(B$7 & $A8,A!$A$3:$N$6202,13,FALSE)
for SM!B8
The 0,1,2,3,4,...199 entries are in row 7 starting at column B, while the
200, 201 ... 399 entries are in column A starting at row 8.

Make sense to you?

But I still don't know how to tell when to choose the {item} from column L
in the lookup table or when to choose it from column M there. That's a
question I have in to the OP right now.

 

[JLatham]

.... although web search turns up this discussion which does offer a method to
return text using SUMPRODUCT():
http://www.ozgrid.com/forum/showthread.php?t=50581

A few limitations to it, but to be expected when you hammer something like
this totally out of shape!

Joel,
Unfortunately the values in column L are text. I don't know of a trick to
use SUMPRODUCT() to return text.

I have the (8MB) workook now. And what I'm recommending at this point is to
add a column on the [A] sheet (which has the long table on it) to combine
entries in the next 2 columns (old A and B) into a text string as: =B3 & C3
Then back in on the sheet (named SM) with the coordinates type table (200
columns by 6200+ rows) it uses a VLOOKUP() where I've set things up to
duplicate the entries in column A of sheet [A] by getting the first part from
row 7 always and the second part from column A always. So at the moment,
that formula looks something like:
=VLOOKUP(B$7 & $A8,A!$A$3:$N$6202,13,FALSE)
for SM!B8
The 0,1,2,3,4,...199 entries are in row 7 starting at column B, while the
200, 201 ... 399 entries are in column A starting at row 8.

Make sense to you?

But I still don't know how to tell when to choose the {item} from column L
in the lookup table or when to choose it from column M there. That's a
question I have in to the OP right now.

Try the formula below which will return the values in the table.


=SUMPRODUCT(--($B7=A!$A$3:$A$100),--($A8=&A!$B$3:$B$100),A!$L$3:$L$100)


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=185282

Excel Live Chat

.

 

[joel]

Couldn't you use an array formula? We are looking for an "ANDING" o
two conditions.


={if(AND($B7=A!$A$3:$A$100,$A8=&A!$B$3:$B$100),A!$L$3:$L$100,"**") }

 

[JLatham]

That probably would have worked. I wonder what the difference in speed is
between that and the VLOOKUP(). In this case there's a huge number of
calculations to perform and efficiency will be a factor. This workbook even
brings up a lengthy "not responding" message at times on a fairly buff
quad-core system with 8GB RAM in it. I've recommended to her that she switch
to manual calculation also when working with it. I filled her current
coordinate table with the VLOOKUP() formulas and the file went from 8MB to
12.9MB; (over 69,000 formulas added!!).

At the moment she seems happy with the solution and is moving on to learning
more about conditional formatting.

 

[Squeeker]

With JLathams help, I have figured out how to do this using VLOOKUP.
I just have to say, this is a great place for help with functions. You all
are fast and well informed and very open to help. The site is also very easy
to get around in.

Thank you all so much for being here.

Squeeker

Well, I'm getting confused by all the new sheets introduced A, ALL(A),
Map(SM) and others yet unknown? Then you mention "all page" and "second
page". Please be consistent: refer to them by their sheet name since those
are going to play into the formulas anyhow.

Are you trying to fill the

0 1 2 3
200
201
202
grid from the
0 200
entries, or the other way around (the other way around is what I thought
initially).

Actually, I think the best thing (for me at least) would be for you to send
me the workbook attached to an email and put plenty of notes about what you
want on the various sheets, show the formula you have above and where it is
on the sheets.

One thing that would even help right now would be to know what cell that
formula is in, AND what it would look like if it worked in the next cell and
what cell that formula would be in.

If you elect to send the book to me, send it to (remove spaces)
Help From@ JLatham Site. com
At least remind me of your user name here: Squeeker.

I haven't tried your sugestions yet, because you all aren't undersatanding
quite what I am doing...

this is the formula from the cross table that works for column 0,
=IF(B$7&$A8=A!$A3&A!$B3,A!$L3, "**")
Problem is when I cntrl drag to column 1, it doent bring in the right
information.
I have played with which values should be absolute. The second table is the
table that has all the lists, I have tables for each set of cords, example
0,200 - 399, 1,200-399..... I have them each brought over into the page for
ALL(A) the lists that when i update the indivial pages, it updates in the ALL
page. From the all page, I am pulling the information for the Map(SM) page.
The formula above works well for the 0 column, problem is when the 0 turns to
1, it starts over at 200 for the B column(2nd page) and it isn't finding the
right information.

Let's take one of the pieces of the problem first: that of getting the 0 1 2
3 4 entries from table 1 to show up as a list in your second table. The
easiest way to do that is to copy all of those entries and then use
Edit --> Paste Special with the Transpose option selected somewhere on the
same sheet with the second table. Then use that list as the source for your
data validation for the first column. You can always hide this 'helper'
column of data for neatness.

As for pulling data from table 1 into table 2:
Assumptions:
Table 1 begins at A1 on a sheet (A1 being the empty cell above 200 and to
the left of 0). I'll call that sheet 'Table1Sheet'.
The second table begins in H2 on the same sheet, so the 0 is in H2, the 200
is in I2.
That puts your first {info] entry at J2.

For my table 1 I went from 0 to 4 in row 1, and from 200 to 209, so that
table actually occupies the range $A$1:$F!11.

In cell J2, use this formula:
=INDEX($A$1:$F$11,MATCH($I2,$A$1:$A$11),MATCH($H2,$A$1:$F$1))

It doesn't make much sense (to me) to have multiple {item} entries next to
your
0 200 entries (the selections), since for any given pair of coordinates,
there is only one value in table 1. But maybe I'm missing something
somewhere?


:

I have one sheet layed out as follows:

0 | 1 | 2 | 3 | 4 | etc:
200
201
202
203
etc:

They are cordinates.

My second table is layed out as follows:

0 | 200 | {info} | {info} |

I want to match table one {say} 0 200 to table two 0 200 then pull
information from one of the {info} columns.

I know how to join using &" "&, but I can't figure out if to use an 'IF'
Statement or a LOOKUP or ROW or ADDRESS and how to make it work

I can get it to pull down and use absolute column, but when pull sideways,
it doesn't change correctly because when teh 0's turn to 1's it doesn't pull
them to the next column - if that makes sense.

I do notttt understand Macro's so please, if there is a way to do this in
code, that is what I am looking for.