Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referrencing to a diff cell
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referrencing to a diff cell
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referrencing to a diff cell
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referrencing to a diff cell
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referrencing to a diff cell
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referrencing to a diff cell
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referrencing to a diff cell
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign Color to a Cell based on another Cell on a diff Sheet | Excel Worksheet Functions | |||
plotting column chart whch colors the bars diff for diff comm | Charts and Charting in Excel | |||
SUMIF formula required to search for 2 diff values in 2 diff colum | Excel Worksheet Functions | |||
how you make links between diff. cells on diff. work sheets | Excel Worksheet Functions | |||
Retrieve cell value basis the inputs into a diff cell // sk. | Excel Worksheet Functions |