Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenation
Hi, I have multiple lists of details which I have concatenated into a new list. ie week number (42) period number (10) and week commencing date (09/01/06) now reads "Week 42, Period 10, wc 09/01/06". My problem is getting a seperate worksheet to read this list of information, but allow the new worksheet to lookup the list without adding in the amount of spare rows. IE, reference cell a1 (concatenated data) into cell a1 of new worksheet, then cell a2 linked to cell a10 and so on. How do I get Excel to reference one sheet in sequencial cells but the new worksheet to miss the spare cells I need to use for other data? I do not want to individually link each cell. hope I have explained myself, Matt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenation
You have sheet 1 with values/formulas that will be in every row
You have sheet 2 with values/formulas that will be in every 9th row Which sheet has the values and which has the formulas that need to link to the other -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "mattguerilla" wrote in message ... Hi, I have multiple lists of details which I have concatenated into a new list. ie week number (42) period number (10) and week commencing date (09/01/06) now reads "Week 42, Period 10, wc 09/01/06". My problem is getting a seperate worksheet to read this list of information, but allow the new worksheet to lookup the list without adding in the amount of spare rows. IE, reference cell a1 (concatenated data) into cell a1 of new worksheet, then cell a2 linked to cell a10 and so on. How do I get Excel to reference one sheet in sequencial cells but the new worksheet to miss the spare cells I need to use for other data? I do not want to individually link each cell. hope I have explained myself, Matt |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenation
this is basically for a calendar tool. The first sheet, has concatenated week number, period number etc and appears on every row (52 weeks). The second sheet has a break down for each day in the year, and the top of each page needs to link to the correct concatenated cell in sheet one. So, the first sheet has data on each row, while the second needs to link to that data every tenth row. Hope you can help. I want to be able to change the data in the first sheet to make each new year easy to create. "Ken Wright" wrote: You have sheet 1 with values/formulas that will be in every row You have sheet 2 with values/formulas that will be in every 9th row Which sheet has the values and which has the formulas that need to link to the other -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "mattguerilla" wrote in message ... Hi, I have multiple lists of details which I have concatenated into a new list. ie week number (42) period number (10) and week commencing date (09/01/06) now reads "Week 42, Period 10, wc 09/01/06". My problem is getting a seperate worksheet to read this list of information, but allow the new worksheet to lookup the list without adding in the amount of spare rows. IE, reference cell a1 (concatenated data) into cell a1 of new worksheet, then cell a2 linked to cell a10 and so on. How do I get Excel to reference one sheet in sequencial cells but the new worksheet to miss the spare cells I need to use for other data? I do not want to individually link each cell. hope I have explained myself, Matt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenation
Lets suppose your data starts on row 2 in A2 on sheet 1 and goes down
through A53 Lets also suppose you want to link cells A10 on sheet 2 to A2 on sheet1 A20 on sheet 2 to A3 on sheet1 A30 on sheet 2 to A4 on sheet1 A40 on sheet 2 to A5 on sheet1 etc In cell A10 on sheet 2, if you put the following formula in:- =OFFSET(Sheet1!$A$1,ROW()/10,0) then select A1:A10 on sheet 2, and grab the fill handle at the very bottom right of the selection and drag down to about A530, you will see that every 10th row has been populated with a link to a cell on sheet 1, where it has incremented the linked row by only 1 row each time. Does this help? Regards Ken.................... "mattguerilla" wrote in message ... this is basically for a calendar tool. The first sheet, has concatenated week number, period number etc and appears on every row (52 weeks). The second sheet has a break down for each day in the year, and the top of each page needs to link to the correct concatenated cell in sheet one. So, the first sheet has data on each row, while the second needs to link to that data every tenth row. Hope you can help. I want to be able to change the data in the first sheet to make each new year easy to create. "Ken Wright" wrote: You have sheet 1 with values/formulas that will be in every row You have sheet 2 with values/formulas that will be in every 9th row Which sheet has the values and which has the formulas that need to link to the other -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "mattguerilla" wrote in message ... Hi, I have multiple lists of details which I have concatenated into a new list. ie week number (42) period number (10) and week commencing date (09/01/06) now reads "Week 42, Period 10, wc 09/01/06". My problem is getting a seperate worksheet to read this list of information, but allow the new worksheet to lookup the list without adding in the amount of spare rows. IE, reference cell a1 (concatenated data) into cell a1 of new worksheet, then cell a2 linked to cell a10 and so on. How do I get Excel to reference one sheet in sequencial cells but the new worksheet to miss the spare cells I need to use for other data? I do not want to individually link each cell. hope I have explained myself, Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenation Problem / VBA ? | Excel Worksheet Functions | |||
Concatenation issue - Help, PLEASE! | Excel Discussion (Misc queries) | |||
marrying a rounded number to concatenation. | New Users to Excel | |||
How do I put each member of string concatenation in its own line? | Excel Worksheet Functions | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) |