| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
to create a formula by removing RC[number]
I am using the following forumula in a macro.
ActiveCell.FormulaR1C1 = "=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)" . Here RC[-6]) is G7 column. So I have to write the same formula to work from any column which is active cell. I wrote the same formula as follows to enable to work from any of the column =IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0). But the G7 value will be shown as 'G7' so #item# error is showing ie the formula is coming wrongly =IF(COUNTIF([item.xls]item!B:B,'G7'),VLOOKUP('G7',[item.xls]item!B:L,11,0),0). It would be very helpful if anybody can help to solve this problem With thanks Pol |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
to create a formula by removing RC[number]
Check your other post.
pol wrote: I am using the following forumula in a macro. ActiveCell.FormulaR1C1 = "=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)" . Here RC[-6]) is G7 column. So I have to write the same formula to work from any column which is active cell. I wrote the same formula as follows to enable to work from any of the column =IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0). But the G7 value will be shown as 'G7' so #item# error is showing ie the formula is coming wrongly =IF(COUNTIF([item.xls]item!B:B,'G7'),VLOOKUP('G7',[item.xls]item!B:L,11,0),0). It would be very helpful if anybody can help to solve this problem With thanks Pol -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
to create a formula by removing RC[number]
The answer I gave you in your other post works for me
"pol" wrote: I am using the following forumula in a macro. ActiveCell.FormulaR1C1 = "=IF(COUNTIF([item.xls]item!C[-11],RC[-6]),VLOOKUP(RC[-6],[item.xls]Item!C[-11]:C[-1],11,0),0)" . Here RC[-6]) is G7 column. So I have to write the same formula to work from any column which is active cell. I wrote the same formula as follows to enable to work from any of the column =IF(COUNTIF([item.xls]item!B:B,G7),VLOOKUP(G7,[item.xls]item!B:L,11,0),0). But the G7 value will be shown as 'G7' so #item# error is showing ie the formula is coming wrongly =IF(COUNTIF([item.xls]item!B:B,'G7'),VLOOKUP('G7',[item.xls]item!B:L,11,0),0). It would be very helpful if anybody can help to solve this problem With thanks Pol |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a formula that gives number records with certain d | Excel Discussion (Misc queries) | |||
| create a formula that when I type a number in that cell it will to | Excel Worksheet Functions | |||
| Create formula which will number a list in Excel 2003 | Excel Discussion (Misc queries) | |||
| Need a formula to create an order number | Excel Worksheet Functions | |||
| keep number when removing formula | Excel Discussion (Misc queries) | |||
Linear Mode
