Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To link sheet1 cell to sheet2 three rows merged cell
Sheet1 has lsit of crew names.
Sheet2 has details about the crew in the following manner. a1 to a3 is merged as one cell for crew name. b1 will have residence, b2 his office, b3 his business. The problem I am facing is when I link sheet1 names to sheet2, it is not in same order in sheet2 becos three rows or merged as one cell. so pls help me in a) how to link names between two sheets to copy the names in sheet2 from sheet1 and b) I have all the data of names in access and it should be linked to this sheet1. Pls note using office 97. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
To link sheet1 cell to sheet2 three rows merged cell
Hi
This answers your question a). Place the following formula in sheet2. =OFFSET(Sheet1!$A$1,(ROW()-1)/3,0) Please note that if your names on sheet2 have more than 3 rows merged, then change the 3 in the above formula to however many rows are merged. Also if the first name is not in $A$1 then change the $A$1 to wherever the first name in your list is say $B$2 For question b). Click on Tools -- Data and then "Get External Data". Select Access Database and then you will be given a number of options for updating. Select "Enable Background update to make this happen all the time in the background. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: Sheet1 has lsit of crew names. Sheet2 has details about the crew in the following manner. a1 to a3 is merged as one cell for crew name. b1 will have residence, b2 his office, b3 his business. The problem I am facing is when I link sheet1 names to sheet2, it is not in same order in sheet2 becos three rows or merged as one cell. so pls help me in a) how to link names between two sheets to copy the names in sheet2 from sheet1 and b) I have all the data of names in access and it should be linked to this sheet1. Pls note using office 97. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
To link sheet1 cell to sheet2 three rows merged cell
D/BSC
Thanks for the guidance. From access able to get using external data but unable to use the offset formula. When I use the ""=OFFSET(Sheet1!$A$1,(ROW()-1)/3,0)"" in the required cell in sheet2, the first name is fine; then when I drag the formula to next cell the second name is also correct. but from third cell second name repeats. So can you pls sugest what to do from here. Would like to know what this does actually "(ROW()-1)/3,0) and also how to automatically update this sheet1 if i change the name in access. meaning how to automatically refresh when i open the excel file. can I put on open and write a vb?? Will be watching for your reply so that I can work on this tonight and eagerly waiting. Appreciate your guidance and pls guide me further. brgds/captgnvr "BSc Chem Eng Rick" wrote: Hi This answers your question a). Place the following formula in sheet2. =OFFSET(Sheet1!$A$1,(ROW()-1)/3,0) Please note that if your names on sheet2 have more than 3 rows merged, then change the 3 in the above formula to however many rows are merged. Also if the first name is not in $A$1 then change the $A$1 to wherever the first name in your list is say $B$2 For question b). Click on Tools -- Data and then "Get External Data". Select Access Database and then you will be given a number of options for updating. Select "Enable Background update to make this happen all the time in the background. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: Sheet1 has lsit of crew names. Sheet2 has details about the crew in the following manner. a1 to a3 is merged as one cell for crew name. b1 will have residence, b2 his office, b3 his business. The problem I am facing is when I link sheet1 names to sheet2, it is not in same order in sheet2 becos three rows or merged as one cell. so pls help me in a) how to link names between two sheets to copy the names in sheet2 from sheet1 and b) I have all the data of names in access and it should be linked to this sheet1. Pls note using office 97. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
To link sheet1 cell to sheet2 three rows merged cell
I'm not sure why the offset formula isn't producing the result you want. I
have 50 names on sheet 1 and they all come in to sheet 2 OK. Just make sure that you have "$" around the address of the first name in sheet 1 i.e. Sheet1!$A$1. Also make sure that all your merged entries contain 3 rows, if they don't, the formula will not work. To explain (Row()-1)/3,0): The zero tells OFFSET not to move across any columns becuase the names are all in the same column on sheet 1. (Row()-1)/3: Because we have our cells separated into 3's we have inadvertently created an arithmetic series. So to correctly reference sheet1 we must correct the offset. ROW() returns the top-most row number of the merged cell range, then we always need to subtract 1 to ensure that the result is always devisible by 3 because a spreadsheet starts at row 1 not row 0. Using an example. The second name which is in row 2 on sheet 1 must appear in row 4 (top-most row of the merged cell) of sheet 2. So to achieve this, we take 4-1 = 3 then divide by 3, now our offset formula offsets the original cell A1 by 1 row to A2. The next name is in row 3 on sheet 1 but must appear in row 7 on sheet 2, so 7-1 = 6 then divide 3 gives 2, so OFFSET must move two rows down from the orginal cell of A1 to A3 and so on. When you get the data from Access you should see a screen to "link" to the database. This screen also gives you the options to specify how the spreadsheet updates from the database. Not sure what else to tell you here, there's no need to write a macro. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: D/BSC Thanks for the guidance. From access able to get using external data but unable to use the offset formula. When I use the ""=OFFSET(Sheet1!$A$1,(ROW()-1)/3,0)"" in the required cell in sheet2, the first name is fine; then when I drag the formula to next cell the second name is also correct. but from third cell second name repeats. So can you pls sugest what to do from here. Would like to know what this does actually "(ROW()-1)/3,0) and also how to automatically update this sheet1 if i change the name in access. meaning how to automatically refresh when i open the excel file. can I put on open and write a vb?? Will be watching for your reply so that I can work on this tonight and eagerly waiting. Appreciate your guidance and pls guide me further. brgds/captgnvr "BSc Chem Eng Rick" wrote: Hi This answers your question a). Place the following formula in sheet2. =OFFSET(Sheet1!$A$1,(ROW()-1)/3,0) Please note that if your names on sheet2 have more than 3 rows merged, then change the 3 in the above formula to however many rows are merged. Also if the first name is not in $A$1 then change the $A$1 to wherever the first name in your list is say $B$2 For question b). Click on Tools -- Data and then "Get External Data". Select Access Database and then you will be given a number of options for updating. Select "Enable Background update to make this happen all the time in the background. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: Sheet1 has lsit of crew names. Sheet2 has details about the crew in the following manner. a1 to a3 is merged as one cell for crew name. b1 will have residence, b2 his office, b3 his business. The problem I am facing is when I link sheet1 names to sheet2, it is not in same order in sheet2 becos three rows or merged as one cell. so pls help me in a) how to link names between two sheets to copy the names in sheet2 from sheet1 and b) I have all the data of names in access and it should be linked to this sheet1. Pls note using office 97. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
To link sheet1 cell to sheet2 three rows merged cell
D/BSC THANK U FOR SUCH QUICK EXPLANATION. I am following what you have explained. getting wrong may be becos the sheet2 name starts in cell b4 and b5 merged as the first name. Of cource I tried with another sheet which has three cells merged. So if the name is not in the first row in sheet2 but in row 4 and 5 of coulm 'B', then can the same formula be modified?? Really appreciate your patience to explain the mathematics and was able to understand. Now I have to get a free slot to escape the tanker operations to try out this but in the mean time if u can give the formula if the name cell in sheet2 is in b4 n b5 merged and all the other names as two rows merged?? Pls one last time pls advice. Will be eager to see answer. brgds/captgnvr "BSc Chem Eng Rick" wrote: I'm not sure why the offset formula isn't producing the result you want. I have 50 names on sheet 1 and they all come in to sheet 2 OK. Just make sure that you have "$" around the address of the first name in sheet 1 i.e. Sheet1!$A$1. Also make sure that all your merged entries contain 3 rows, if they don't, the formula will not work. To explain (Row()-1)/3,0): The zero tells OFFSET not to move across any columns becuase the names are all in the same column on sheet 1. (Row()-1)/3: Because we have our cells separated into 3's we have inadvertently created an arithmetic series. So to correctly reference sheet1 we must correct the offset. ROW() returns the top-most row number of the merged cell range, then we always need to subtract 1 to ensure that the result is always devisible by 3 because a spreadsheet starts at row 1 not row 0. Using an example. The second name which is in row 2 on sheet 1 must appear in row 4 (top-most row of the merged cell) of sheet 2. So to achieve this, we take 4-1 = 3 then divide by 3, now our offset formula offsets the original cell A1 by 1 row to A2. The next name is in row 3 on sheet 1 but must appear in row 7 on sheet 2, so 7-1 = 6 then divide 3 gives 2, so OFFSET must move two rows down from the orginal cell of A1 to A3 and so on. When you get the data from Access you should see a screen to "link" to the database. This screen also gives you the options to specify how the spreadsheet updates from the database. Not sure what else to tell you here, there's no need to write a macro. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: D/BSC Thanks for the guidance. From access able to get using external data but unable to use the offset formula. When I use the ""=OFFSET(Sheet1!$A$1,(ROW()-1)/3,0)"" in the required cell in sheet2, the first name is fine; then when I drag the formula to next cell the second name is also correct. but from third cell second name repeats. So can you pls sugest what to do from here. Would like to know what this does actually "(ROW()-1)/3,0) and also how to automatically update this sheet1 if i change the name in access. meaning how to automatically refresh when i open the excel file. can I put on open and write a vb?? Will be watching for your reply so that I can work on this tonight and eagerly waiting. Appreciate your guidance and pls guide me further. brgds/captgnvr "BSc Chem Eng Rick" wrote: Hi This answers your question a). Place the following formula in sheet2. =OFFSET(Sheet1!$A$1,(ROW()-1)/3,0) Please note that if your names on sheet2 have more than 3 rows merged, then change the 3 in the above formula to however many rows are merged. Also if the first name is not in $A$1 then change the $A$1 to wherever the first name in your list is say $B$2 For question b). Click on Tools -- Data and then "Get External Data". Select Access Database and then you will be given a number of options for updating. Select "Enable Background update to make this happen all the time in the background. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: Sheet1 has lsit of crew names. Sheet2 has details about the crew in the following manner. a1 to a3 is merged as one cell for crew name. b1 will have residence, b2 his office, b3 his business. The problem I am facing is when I link sheet1 names to sheet2, it is not in same order in sheet2 becos three rows or merged as one cell. so pls help me in a) how to link names between two sheets to copy the names in sheet2 from sheet1 and b) I have all the data of names in access and it should be linked to this sheet1. Pls note using office 97. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
To link sheet1 cell to sheet2 three rows merged cell
If every 2 rows are merged and the start cell is B4 then change the formula
to the following: =OFFSET(Sheet1!$A$1,(ROW()-4)/2,0) Notice all I changed was instead of dividing by 3 I now divide by 2 because every second row is merged and also because we start in row 4 on sheet2 we need to subtract 4 from ROW() and not 1 (originally we started on row 1 of sheet2). Remember this also assumes that the first name is in A1 of Sheet1. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: D/BSC THANK U FOR SUCH QUICK EXPLANATION. I am following what you have explained. getting wrong may be becos the sheet2 name starts in cell b4 and b5 merged as the first name. Of cource I tried with another sheet which has three cells merged. So if the name is not in the first row in sheet2 but in row 4 and 5 of coulm 'B', then can the same formula be modified?? Really appreciate your patience to explain the mathematics and was able to understand. Now I have to get a free slot to escape the tanker operations to try out this but in the mean time if u can give the formula if the name cell in sheet2 is in b4 n b5 merged and all the other names as two rows merged?? Pls one last time pls advice. Will be eager to see answer. brgds/captgnvr "BSc Chem Eng Rick" wrote: I'm not sure why the offset formula isn't producing the result you want. I have 50 names on sheet 1 and they all come in to sheet 2 OK. Just make sure that you have "$" around the address of the first name in sheet 1 i.e. Sheet1!$A$1. Also make sure that all your merged entries contain 3 rows, if they don't, the formula will not work. To explain (Row()-1)/3,0): The zero tells OFFSET not to move across any columns becuase the names are all in the same column on sheet 1. (Row()-1)/3: Because we have our cells separated into 3's we have inadvertently created an arithmetic series. So to correctly reference sheet1 we must correct the offset. ROW() returns the top-most row number of the merged cell range, then we always need to subtract 1 to ensure that the result is always devisible by 3 because a spreadsheet starts at row 1 not row 0. Using an example. The second name which is in row 2 on sheet 1 must appear in row 4 (top-most row of the merged cell) of sheet 2. So to achieve this, we take 4-1 = 3 then divide by 3, now our offset formula offsets the original cell A1 by 1 row to A2. The next name is in row 3 on sheet 1 but must appear in row 7 on sheet 2, so 7-1 = 6 then divide 3 gives 2, so OFFSET must move two rows down from the orginal cell of A1 to A3 and so on. When you get the data from Access you should see a screen to "link" to the database. This screen also gives you the options to specify how the spreadsheet updates from the database. Not sure what else to tell you here, there's no need to write a macro. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: D/BSC Thanks for the guidance. From access able to get using external data but unable to use the offset formula. When I use the ""=OFFSET(Sheet1!$A$1,(ROW()-1)/3,0)"" in the required cell in sheet2, the first name is fine; then when I drag the formula to next cell the second name is also correct. but from third cell second name repeats. So can you pls sugest what to do from here. Would like to know what this does actually "(ROW()-1)/3,0) and also how to automatically update this sheet1 if i change the name in access. meaning how to automatically refresh when i open the excel file. can I put on open and write a vb?? Will be watching for your reply so that I can work on this tonight and eagerly waiting. Appreciate your guidance and pls guide me further. brgds/captgnvr "BSc Chem Eng Rick" wrote: Hi This answers your question a). Place the following formula in sheet2. =OFFSET(Sheet1!$A$1,(ROW()-1)/3,0) Please note that if your names on sheet2 have more than 3 rows merged, then change the 3 in the above formula to however many rows are merged. Also if the first name is not in $A$1 then change the $A$1 to wherever the first name in your list is say $B$2 For question b). Click on Tools -- Data and then "Get External Data". Select Access Database and then you will be given a number of options for updating. Select "Enable Background update to make this happen all the time in the background. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: Sheet1 has lsit of crew names. Sheet2 has details about the crew in the following manner. a1 to a3 is merged as one cell for crew name. b1 will have residence, b2 his office, b3 his business. The problem I am facing is when I link sheet1 names to sheet2, it is not in same order in sheet2 becos three rows or merged as one cell. so pls help me in a) how to link names between two sheets to copy the names in sheet2 from sheet1 and b) I have all the data of names in access and it should be linked to this sheet1. Pls note using office 97. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
To link sheet1 cell to sheet2 three rows merged cell
D/BSC GREATTTTTTTTTTT. Your patient explanations had made me understand and marvel at the interpretations of the formulas. It is working just the way I needed and pls accept my thanks and appreciation. brgds/captgnvr "BSc Chem Eng Rick" wrote: If every 2 rows are merged and the start cell is B4 then change the formula to the following: =OFFSET(Sheet1!$A$1,(ROW()-4)/2,0) Notice all I changed was instead of dividing by 3 I now divide by 2 because every second row is merged and also because we start in row 4 on sheet2 we need to subtract 4 from ROW() and not 1 (originally we started on row 1 of sheet2). Remember this also assumes that the first name is in A1 of Sheet1. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: D/BSC THANK U FOR SUCH QUICK EXPLANATION. I am following what you have explained. getting wrong may be becos the sheet2 name starts in cell b4 and b5 merged as the first name. Of cource I tried with another sheet which has three cells merged. So if the name is not in the first row in sheet2 but in row 4 and 5 of coulm 'B', then can the same formula be modified?? Really appreciate your patience to explain the mathematics and was able to understand. Now I have to get a free slot to escape the tanker operations to try out this but in the mean time if u can give the formula if the name cell in sheet2 is in b4 n b5 merged and all the other names as two rows merged?? Pls one last time pls advice. Will be eager to see answer. brgds/captgnvr "BSc Chem Eng Rick" wrote: I'm not sure why the offset formula isn't producing the result you want. I have 50 names on sheet 1 and they all come in to sheet 2 OK. Just make sure that you have "$" around the address of the first name in sheet 1 i.e. Sheet1!$A$1. Also make sure that all your merged entries contain 3 rows, if they don't, the formula will not work. To explain (Row()-1)/3,0): The zero tells OFFSET not to move across any columns becuase the names are all in the same column on sheet 1. (Row()-1)/3: Because we have our cells separated into 3's we have inadvertently created an arithmetic series. So to correctly reference sheet1 we must correct the offset. ROW() returns the top-most row number of the merged cell range, then we always need to subtract 1 to ensure that the result is always devisible by 3 because a spreadsheet starts at row 1 not row 0. Using an example. The second name which is in row 2 on sheet 1 must appear in row 4 (top-most row of the merged cell) of sheet 2. So to achieve this, we take 4-1 = 3 then divide by 3, now our offset formula offsets the original cell A1 by 1 row to A2. The next name is in row 3 on sheet 1 but must appear in row 7 on sheet 2, so 7-1 = 6 then divide 3 gives 2, so OFFSET must move two rows down from the orginal cell of A1 to A3 and so on. When you get the data from Access you should see a screen to "link" to the database. This screen also gives you the options to specify how the spreadsheet updates from the database. Not sure what else to tell you here, there's no need to write a macro. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: D/BSC Thanks for the guidance. From access able to get using external data but unable to use the offset formula. When I use the ""=OFFSET(Sheet1!$A$1,(ROW()-1)/3,0)"" in the required cell in sheet2, the first name is fine; then when I drag the formula to next cell the second name is also correct. but from third cell second name repeats. So can you pls sugest what to do from here. Would like to know what this does actually "(ROW()-1)/3,0) and also how to automatically update this sheet1 if i change the name in access. meaning how to automatically refresh when i open the excel file. can I put on open and write a vb?? Will be watching for your reply so that I can work on this tonight and eagerly waiting. Appreciate your guidance and pls guide me further. brgds/captgnvr "BSc Chem Eng Rick" wrote: Hi This answers your question a). Place the following formula in sheet2. =OFFSET(Sheet1!$A$1,(ROW()-1)/3,0) Please note that if your names on sheet2 have more than 3 rows merged, then change the 3 in the above formula to however many rows are merged. Also if the first name is not in $A$1 then change the $A$1 to wherever the first name in your list is say $B$2 For question b). Click on Tools -- Data and then "Get External Data". Select Access Database and then you will be given a number of options for updating. Select "Enable Background update to make this happen all the time in the background. If this helps please click "Yes" <<<<<<<<<< "CAPTGNVR" wrote: Sheet1 has lsit of crew names. Sheet2 has details about the crew in the following manner. a1 to a3 is merged as one cell for crew name. b1 will have residence, b2 his office, b3 his business. The problem I am facing is when I link sheet1 names to sheet2, it is not in same order in sheet2 becos three rows or merged as one cell. so pls help me in a) how to link names between two sheets to copy the names in sheet2 from sheet1 and b) I have all the data of names in access and it should be linked to this sheet1. Pls note using office 97. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate: Two cells from Sheet2 into One cell in Sheet1 | Excel Programming | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
how can i link sheet1 and sheet2 of book1 | Excel Discussion (Misc queries) | |||
A1 Sheet2 is linked to A1 sheet1 so that user enters value(abc123) a1 sheet1 and A1 sheet2 is updated | Excel Programming | |||
How to set sheet1!cell = sheet2!samecell and set format? | Excel Programming |