Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-03-2020, 02:21 PM
MK3 MK3 is offline Insert value if not existing Windows 10 Insert value if not existing Office 2019
Novice
Insert value if not existing
 
Join Date: Apr 2020
Posts: 1
MK3 is on a distinguished road
Cool 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.
Attached Files
File Type: xlsx Excel Insert if not available.xlsx (11.8 KB, 10 views)
Reply With Quote
  #2  
Old 04-03-2020, 03:34 PM
Logit Logit is offline Insert value if not existing Windows 10 Insert value if not existing Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Paste in E2 :

Code:
=IF(COUNTIF(H2:H4,A9),"",A9)
Reply With Quote
  #3  
Old 04-04-2020, 09:45 AM
BobBridges's Avatar
BobBridges BobBridges is offline Insert value if not existing Windows 7 64bit Insert value if not existing Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert value if not existing Insert RTF file into existing document donaldadams1951 Word VBA 1 06-26-2018 04:18 PM
Insert value if not existing 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:29 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft