<I think it is too complicated the convey enough data in an email>
To be honest, I think the only way to solve this is to make you explain in words, not in a workbook, what you're trying to
achieve.
My bet is that, in trying to explain, you yourself will solve the problem.
But let's try.
<The IDs are offset relative to the row so A26 (R26C1) contains the ID 23 and D26 (R26C4) contains my name Hookham.>
What does this mean? What is "offset"? What is the relation between A26, ID 23, D26 and Hookham?
< the row can vary so I need to use the ID in the same row in column 2.>
????????????????
<wanted to use ID + 3 (not its row number) in the List sheet to put the name from the Present sheet into column D.>
What ID? 23? What does the +3 stand for?
Please take a while to think about the essence of your problem and how you would explain to someone using paper and pencil, not
Excel.
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Thanks for coming back Neik
| I think it is too complicated the convey enough data in an email
| - it would be best if you could see the two sheets
| - but I shall try to explain.
|
| The data sheet 'Present' contains ID and names of members.
| The IDs are offset relative to the row so A26 (R26C1) contains the ID 23
| and D26 (R26C4) contains my name Hookham.
|
| I want to bring the name into column D of sheet 'List'
| but the row can vary so I need to use the ID in the same row
| in column 2.
|
| So I wanted to use ID + 3 (not its row number) in the List sheet
| to put the name from the Present sheet into column D.
|
| In this case, whichever row in List contains ID 23 in column 2
| I want the name Hookham to appear in column D,
| using 23+3
|
| If that 's not clear please leave it altogether
| - I'll have to revert to a macro I used to use when there is
| a change of membership - this was going to be a slicker way of doing it.
|
| Thanks for trying
|
| Francis Hookham
|
|
| | > Since you're using the R1C1 Reference style, the second argument of the
| > INDIRECT() function should be FALSE
| >
| > If this doesn't work, please post the exact formula and the values of all
| > cells involved
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| >
| > | > | Sorry Nick - I cannot do it - keep getting #REF! or zero
| > |
| > | =INDIRECT("Present!R" & RC[-2] & "C5")...............#REF!
| > | =INDIRECT(Present!R & "RC[-2]" & C5).................#REF!
| > | =INDIRECT(Present!R + RC[-2] + C5)..........................0
| > |
| > | The number in RC[-2] of the active sheet is the row number in colomn 5
| > of
| > | sheet 'Present' which I want to get.
| > |
| > | Francis
| > |
| > |
| > | | > | > Hi Francis,
| > | >
| > | > Look in HELP for the INDIRECT() function
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | > | > | > | RC[-1] contains a formula which, in this row, produces the numeral
| > 31
| > | > |
| > | > |
| > | > |
| > | > | The next column contains this made up formula which produces what I
| > want
| > | > |
| > | > |
| > | > |
| > | > | =IF(RC[-1]<>"",Present!R[27]C5&" "&Present!R[27]C4,"")
| > | > |
| > | > |
| > | > |
| > | > | I should like to replace 'R[27]' with something like this but cannot
| > | > make it
| > | > | work
| > | > |
| > | > |
| > | > |
| > | > | R[RC[-1]-4]
| > | > |
| > | > |
| > | > |
| > | > | How can I use what is in RC[-1] to bring the text from the Present
| > page?
| > | > |
| > | > |
| > | > |
| > | > | Thanks you
| > | > |
| > | > |
| > | > |
| > | > | Francis Hookham
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|
