| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable in if statement
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
Hi Francis,
Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Francis Hookham" wrote in message ... | 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 | | |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
Thanks - will do - I was about to post again saying 'Ops, no square brackets
in this case. Francis "Niek Otten" wrote in message ... Hi Francis, Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Francis Hookham" wrote in message ... | 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 | | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
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 "Niek Otten" wrote in message ... Hi Francis, Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Francis Hookham" wrote in message ... | 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 | | |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
I'm not all that familiar with RC notation, but won't you need a colon
between the two cells to indicate a range? Something like: =INDIRECT("Present!R" & RC[-2] & ":C5") Hope this helps. Pete "Francis Hookham" wrote in message ... 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 "Niek Otten" wrote in message ... Hi Francis, Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Francis Hookham" wrote in message ... | 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 | | |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
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 "Francis Hookham" wrote in message ... | 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 | | | "Niek Otten" wrote in message | ... | Hi Francis, | | Look in HELP for the INDIRECT() function | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Francis Hookham" wrote in message | ... | | 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 | | | | | | | | |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
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 "Niek Otten" wrote in message ... 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 "Francis Hookham" wrote in message ... | 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 | | | "Niek Otten" wrote in message | ... | Hi Francis, | | Look in HELP for the INDIRECT() function | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Francis Hookham" wrote in message | ... | | 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 | | | | | | | | |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
Thanks Pete - I could not make that work - I've replied to Neik to see if he
can develope it for me Francis "Pete_UK" wrote in message ... I'm not all that familiar with RC notation, but won't you need a colon between the two cells to indicate a range? Something like: =INDIRECT("Present!R" & RC[-2] & ":C5") Hope this helps. Pete "Francis Hookham" wrote in message ... 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 "Niek Otten" wrote in message ... Hi Francis, Look in HELP for the INDIRECT() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Francis Hookham" wrote in message ... | 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 | | |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
<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 "Francis Hookham" wrote in message ... | 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 | | | "Niek Otten" wrote in message | ... | 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 | | | | "Francis Hookham" wrote in message | ... | | 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 | | | | | | "Niek Otten" wrote in message | | ... | | Hi Francis, | | | | Look in HELP for the INDIRECT() function | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | "Francis Hookham" wrote in message | | ... | | | 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 | | | | | | | | | | | | | | | | | | |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable in if statement
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. Thank you Niek for persevering The number in RC2 of Sheet 1 is the row* number in Sheet 2 which contains text in column 4 to be brought to RC4 in Sheet 1 Therefore I am looking for the formula in Sheet 1 RC4 So how do I use RC2 indirectly. * I was confusing the issue with another minor problem to be dealt with later. That is that the number in number in Sheet 1 RC2 is 3 less that the actual row number in Sheet 2 so, at some stage, I shall have to increment the row number. Let's leave that for now. Gratefully Francis |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Statement Variable | Excel Discussion (Misc queries) | |||
| How to pass a variable into an SQL statement | Excel Discussion (Misc queries) | |||
| if than statement has too many variable | New Users to Excel | |||
| testing more than one variable in an if statement | Excel Discussion (Misc queries) | |||
| Add 2nd variable to if statement - ifAnd? | Excel Worksheet Functions | |||
Linear Mode
