#1
|
|||
|
|||
Insert value if not existing
Hi community
I am new and this is my first post. From column A6 onwards (ends at A25), I have listed some currencies. Column H2 until H4 has listed the 3 most common currencies that I need and the related exchange rate to each currency, which are always 2 different exchange rates per currency. This would be the matrix H2:J4 If I list a new currency in column A9 (for example GBP), which is not available in the matrix H2:J4, I would want that this currency to be inserted in E2. Another currency that would not be listed then in E3, and another (last one) in E4. I tried with the following but it does not write the currency in the cell. It shows a #NA in the cell. =if(A6=vlookup(A6;H2:H4;1;0);"";A6) This would not work anyway as it needs some kind of loop to check it withing the range of A6:A25) Can anyone help? Do I need wo write here a VBA code or is that possible with formulas? Attached is a file in oder to make it clearer. Thank you and cheers. |
#2
|
|||
|
|||
Paste in E2 :
Code:
=IF(COUNTIF(H2:H4,A9),"",A9) |
#3
|
||||
|
||||
General rule of thumb: Excel worksheet functions never write data to other cells. They can look at other cells and use those values to help calculate the result they display; but they display only one value, and it's always in the cell where the formula is. So if you want your worksheet to see a new value in row 9 (or wherever) and respond by inserting new values in other cells, it'll have to be done in VBA.
And yes, it's possible in VBA. I mean, I'm sure it's possible, because almost anything is. I shouldn't be too sure, until I understand better what it is you want to do. But I'm not really doubtful . Now, what you can do is put a formula in the cells where you want the new information. For example, in E2 you can put "=A9". I know that can't be what you really want, though—it's too simple. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Insert RTF file into existing document | donaldadams1951 | Word VBA | 1 | 06-26-2018 04:18 PM |
Insert Existing Comments at the End of Comment Selection Text | mercadogs | Word VBA | 3 | 12-31-2015 03:49 AM |
How to insert full documents into existing word document | Laraak | Word | 1 | 03-07-2013 11:59 PM |
Need help to insert a picture (.jpg) into slide 1 of an existing PowerPoint | CatMan | PowerPoint | 3 | 04-24-2012 01:25 PM |
How to insert a reference to an existing endnote | dwschulze | Word | 0 | 01-23-2010 08:59 AM |