Functions

[BDB]

How do I use a formula dragged from above (referring to cells in another
sheet) but using the same row number as referred to in the cell to the
immediate right?

 

[Max]

Assuming you mean

In Sheet1
----------
You have in A1: =Sheet2!A1
and you want to drag A1 down to produce
in A2: =Sheet2!B1
in A3: =Sheet2!C1
and so on ?

Here's one way:

In Sheet1
-----------
Put in A1: =OFFSET(Sheet2!$A$1,,ROWS($A$1:A1)-1)
Copy A1 down

This'll return the desired links to Sheet2's A1, B1, C1, etc

Note that the formula could also be placed in any starting cell in Sheet1,
not necessarily in cell A1, and then copied down for the same effect

 

[BDB]

Max - thank you kindly for your reply.

Yes, very close except that the only pattern to the offset is that it is the
same Column but a different pattern to the selection of row numbers.

I am hoping for the row numbers in the formula to change to that in the
reference cell.

I have the following formula in cell AK3 of Sheet 1:
=IF('Sheet 2'!D8="2B",'Sheet 2'!H8,('Sheet 2'!G8)*(1+'Sheet'!$I$2))

and a cell to the right, AM3, I have:
='Sheet 2'!I8

I'd like the computer to take the row number referred to in the righthand
corresponding cell in the Column AM and replace the row number in the above
formula for the rest of the cells in Column AK, so I don't have to manually
insert them again [as I have previously done for the AM column]. There is no
pattern to the selection of the rows other than through the reference cell to
the right.

Will a macro do this instead?

I hope this makes more sense.

Regards,

Boyd

 

[Max]

Think some further clarification is needed:

I have the following formula in cell AK3 of Sheet 1:
=IF('Sheet 2'!D8="2B",'Sheet 2'!H8,('Sheet 2'!G8)*(1+'Sheet'!$I$2))

Is there a typo in the formula above: ... 'Sheet'!$I$2))
Should it be ... 'Sheet 2'!$I$2))
or do you really have a sheet named as : Sheet

Pl also paste the formulas you have in AK4, AK5, AK6
for better clarity as to the formula structure down in col AK

and a cell to the right, AM3, I have:
='Sheet 2'!I8

Pl paste the formulas you have in AM4, AM5, AM6
for clarity

What are the values returned in the cells AM3, AM4, AM5, AM6 ?

Is the row number to be used in col AK's formula to be read from the values
returned in col AM, or ??

If you'd like to, send me a copy of your file (may be easier to figure out
what the deuce is happening <g>)

Send to either:

demechanik <at>yahoo<dot>com
or
xdemechanik <at>yahoo<dot>com

(both valid)

Will a macro do this instead?

Think we could probably try using INDIRECT to string something up first,
might suffice

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max - thank you kindly for your reply.

Yes, very close except that the only pattern to the offset is that it is the
same Column but a different pattern to the selection of row numbers.

I am hoping for the row numbers in the formula to change to that in the
reference cell.

I have the following formula in cell AK3 of Sheet 1:
=IF('Sheet 2'!D8="2B",'Sheet 2'!H8,('Sheet 2'!G8)*(1+'Sheet'!$I$2))

and a cell to the right, AM3, I have:
='Sheet 2'!I8

I'd like the computer to take the row number referred to in the righthand
corresponding cell in the Column AM and replace the row number in the above
formula for the rest of the cells in Column AK, so I don't have to manually
insert them again [as I have previously done for the AM column]. There is no
pattern to the selection of the rows other than through the reference cell to
the right.

Will a macro do this instead?

I hope this makes more sense.

Regards,

Boyd

 

[BDB]

Max - thank you again for your persistence.

1) Yes - you are correct - typo is as you have indicated (thanks).

2) Formulas for AK4, 5 and 6 are respectively as follows:
=IF('Sheet 2'!D20="2B",'Sheet 2'!H20,'Sheet 2'!G20)*(1+'Sheet 2'!$I$2);
=IF('Sheet 2'!D26="2B",'Sheet 2'!H26,'Sheet 2'!G26)*(1+'Sheet 2'!$I$2);
=IF('Sheet 2'!D50="2B",'Sheet 2'!H50,'Sheet 2'!G50)*(1+'Sheet 2'!$I$2).

3) Formulas for AM4, 5 and 6 respectively:
='Sheet 2'!I20;
='Sheet 2'!I26;
='Sheet 2'!I50.

4) Values returned in AM3, 4, 5 and 6 are respectively:
40.13;
29.75;
51.98;
32.94.

5) No - simply to be read from the formula - that is for the above insert
rows numbers in the initial forumla of 20, 36, 50 etc. (but no pattern to
this - needs to pull it out of the AM reference.

I hope this helps and is sufficient.

Kind regards,

Boyd

Think some further clarification is needed:

I have the following formula in cell AK3 of Sheet 1:
=IF('Sheet 2'!D8="2B",'Sheet 2'!H8,('Sheet 2'!G8)*(1+'Sheet'!$I$2))

Is there a typo in the formula above: ... 'Sheet'!$I$2))
Should it be ... 'Sheet 2'!$I$2))
or do you really have a sheet named as : Sheet

Pl also paste the formulas you have in AK4, AK5, AK6
for better clarity as to the formula structure down in col AK

and a cell to the right, AM3, I have:
='Sheet 2'!I8

Pl paste the formulas you have in AM4, AM5, AM6
for clarity

What are the values returned in the cells AM3, AM4, AM5, AM6 ?

Is the row number to be used in col AK's formula to be read from the values
returned in col AM, or ??

If you'd like to, send me a copy of your file (may be easier to figure out
what the deuce is happening <g>)

Send to either:

demechanik <at>yahoo<dot>com
or
xdemechanik <at>yahoo<dot>com

(both valid)

Will a macro do this instead?

Think we could probably try using INDIRECT to string something up first,
might suffice

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max - thank you kindly for your reply.

Yes, very close except that the only pattern to the offset is that it is the
same Column but a different pattern to the selection of row numbers.

I am hoping for the row numbers in the formula to change to that in the
reference cell.

I have the following formula in cell AK3 of Sheet 1:
=IF('Sheet 2'!D8="2B",'Sheet 2'!H8,('Sheet 2'!G8)*(1+'Sheet'!$I$2))

and a cell to the right, AM3, I have:
='Sheet 2'!I8

I'd like the computer to take the row number referred to in the righthand
corresponding cell in the Column AM and replace the row number in the above
formula for the rest of the cells in Column AK, so I don't have to manually
insert them again [as I have previously done for the AM column]. There is no
pattern to the selection of the rows other than through the reference cell to
the right.

Will a macro do this instead?

I hope this makes more sense.

Regards,

Boyd

 

[Max]

Here goes ..

In Sheet 1
------------
Put in AK3:

=IF(OFFSET('Sheet 2'!$I$8,MATCH(AM3,'Sheet
2'!$I$8:$I$1000,0)-1,-5)="2B",OFFSET('Sheet 2'!$I$8,MATCH(AM3,'Sheet
2'!$I$8:$I$1000,0)-1,-1),OFFSET('Sheet 2'!$I$8,MATCH(AM3,'Sheet
2'!$I$8:$I$1000,0)-1,-2)*(1+'Sheet 2'!$I$2))

Copy AK3 down

Adapt the range: 'Sheet 2'!$I$8:$I$1000
to suit

The above utilizes MATCH(...) within OFFSET(...) to find the rows in Sheet
2's col I which contains the values returned by the formulas in col AM. It'll
work OK so long as the values within the range 'Sheet 2'!$I$8:$I$1000 are all
unique, which going by the sample values you indicated as the returns in AM3
to AM6, stands a good chance of being so. The column offsets "-5", "-1" &
"-2" refer to cols D, H and G

I'm not sure whether there's a way to extract the row part of the link
formula references as entered into col AM.

Hope it'll work for you ..

 

[Max]

So.. how did it go for you ?