Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Formula
I have two sheets with data. The first sheet, which contains the source
information, has data in the range A1:A4 as follows: Company A Company B Company C Company D The second sheet, which I want to link to data in sheet 1, will also have a column A which links to the above data. However, I want to merge cells so that Range A1:A3 are merged, and then link this cell to "Company A". Thereafter, I would like to be able to copy and paste down and have the Range A4:A6 contain "Company B", range A7:A9 contain "Company C", etc. The problem is that when I copy from the merged cell (A1:A3 which is linked to A1 on Sheet 1) and paste to the merged cell (A4:A6), instead of taking the next sequential on sheet 1 (A2), it instead links to cell A4 ("Company D"). Any ideas on how I can adjust for this? Thx. |
#2
|
|||
|
|||
One way ..
In Sheet1 --------- Put in B1: =INDEX(A:A,INT((ROWS($A$1:A1)-1)/3)+1) Copy B1 down until zeros appear, signalling exhaustion of data from col A. Col B will triplicate the values in col A, viz.: Company A Company A Company A Company B Company B Company B etc In Sheet2, just point to col B in the link formula & copy down, i.e. in the 1st merged cell A1:A3 (effectively only cell A1) put the formula: =Sheet1!B1 then fill down to yield: Company A Company B Company C etc -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jason" wrote in message ... I have two sheets with data. The first sheet, which contains the source information, has data in the range A1:A4 as follows: Company A Company B Company C Company D The second sheet, which I want to link to data in sheet 1, will also have a column A which links to the above data. However, I want to merge cells so that Range A1:A3 are merged, and then link this cell to "Company A". Thereafter, I would like to be able to copy and paste down and have the Range A4:A6 contain "Company B", range A7:A9 contain "Company C", etc. The problem is that when I copy from the merged cell (A1:A3 which is linked to A1 on Sheet 1) and paste to the merged cell (A4:A6), instead of taking the next sequential on sheet 1 (A2), it instead links to cell A4 ("Company D"). Any ideas on how I can adjust for this? Thx. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |