would really appreciate any assistance. i have inherited a spreadsheet in which figures in one column(colD) should sum to zero based on references in 3 "random" rows in the spreadsheet. i am attempting to automate this task.
Part of the ref1 in colA is added to row 2 as ref3. So i could link these 2 rows by extracting the number from the first row. There is a second number linking the second and third row - colB row 2 and colA row 3. i am thinking of finding the ref that links rows 2 and 3 and copy and paste
the ref from the first row but having some difficulty. Any assistance would be greatly appreciated



thanks in advance for you help
jer

see file enclosed

Attached Files

Book3.xls (23.0 KB, 10 views)

Ji jer,

In your workbook, other than the 'should be' version of column C being filled with '20125269' on all three data rows, I can't see what the issue is. you have no formulae to indicate any data relationships or what these 'random' rows might be. Even the '20125269' is quite easily retrieved from the 'AB3020125269' in column A. See attached.

Attached Files

Book3.xls (23.0 KB, 11 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

the question is how do i populate colC 3 with '20125269' as in the should be. the first 3 rows represent the sheet as i have inherited. i could not come up with or think of a formula that would do this, since the rows are not consecutive, i have put them like this as illustration only. hope this makes it clearer

The attachment in my post has the formulae to do that.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

thank you Paul however this is not it - the rows are not consecutive in original spreadsheet, i did this for illustration only. i modified the file to better represent the original file and how i would like it to look, the object being - to find the amounts that sum to zero
thank you for your suggestion
jer

Attached Files

Book3.xls (17.5 KB, 9 views)

Well, other than the first data row, I can see no apparent relationship between the rows you want the data to be replicated in and their sources. Unless you can explain the logic, no-one is likely to be able to provide a solution.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]