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.

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" wrote:

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.

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.

"JLatham" wrote:

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" wrote:

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.

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 he Squeeker.


"Squeeker" wrote:

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.

"JLatham" wrote:

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" wrote:

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.

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.

"Squeeker" wrote:

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.

"JLatham" wrote:

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" wrote:

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.

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

"JLatham" wrote:

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 he Squeeker.


"Squeeker" wrote:

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.

"JLatham" wrote:

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" wrote:

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.

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

"JLatham" wrote:

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 he Squeeker.


"Squeeker" wrote:

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.

"JLatham" wrote:

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" wrote:

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.