Excel List Match

[DMT Dave]

Hi Guy's, I can't remember without searching but one of you great people helped me with an Excel Match formula, works fantastic (i know it's not an Access Question)

I if i have a column that is validated to list (customer column), then in town column and postcode column i have the following:

=INDEX(Customers!$B$1:B$2000,MATCH($A$2,Customers!$A$1:$A$2000,0))
B being the town column
=INDEX(Customers!$C$1:B$2000,MATCH($A$2,Customers!$A$1:$A$2000,0))
C being the postcode column

What I want to ask as i don't know a great deal with Excel is, when there is no selection in Column A >> =INDEX(Customers!$C$1:B$2000,MATCH($A$2,Customers!$A$1:$A$2000,0))
we have #N/A until a selection is made

is there a way i can do 2 things here

1: not have #N/A (guessing not as it's looking for a match once a selection is made

2: have the set of rows in that column set to looking for a match in the specific row number, forgive me if i am not making sense

so atm i am copying the formula from the row above, pasting and physically changing INDEX(Customers!$C$1:B$2000,MATCH($A$2,Customers!$A$1:$A$2000,0)) 2 to 3, then pasting the formula into row 4 then changing the 2 to 4 etc....

as much as i can see what this formula does, i can't find a method (if there is one)

even if i can add the formula up to 2000 rows as that is the amount of rows i have validated the list in column A to

If there isn't an easy method, sorry for the long explanation and tell me to get stuffed

[Minty]

The simplest route is to surround your Index expression with an IFNA()

https://support.microsoft.com/en-us/...d-79b4951fd461

It works almost exactly the same as Nz() in access but looks for #N/A as the result

[DMT Dave]

ah Minty, thank you, I did search some excel formulas, but very limited on excel vbe so i will check out your link tonight on my laptop, thank you indeed

Yeh i know Nz() function in Access, use it fairly frequent

will check out

[DMT Dave]

Yes, thanks MInty for the heda up on correction, this works and guessing IFNA in Excel confirms If Nothing

=IFNA(INDEX(Customers!$B$1:B$2000,MATCH($B$2,Custo mers!$A$1:$A$20000)),"")

This clears the Cell until a list item is selected

thank you

[DMT Dave]

Good Day all, just been doing some digging on this one, seen a few different things on google search but can't seem to make this work, i have now worked on (thanks to Minty) for the info on using IFNA()

whilst i have had a look at special cells in Excel, what i am trying to achieve here is to have the formulas remain in the excel cells but clear the cells.

So what i have done so far is:

Count how many contacts there are on Excel sheet contacts sheet

add to a temp table in Access

export back out to excel Orders sheet in the relevant cells

as we can see, all row numbers are incremented in the formulas which works great but after some searching, i would now like to clear the cells but keep the formulas in place

researching is pointing to use special cells but can't appear to clear cells

sure one of you guys can point the correct method ?

Add To Temp Table

'Add Data To Access' Find Last Row With Data
    intLR = xlSHT2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblCodeUpdate")
    strFind = "MATCH($B$"


    strCode = rs.Fields("OrgCode")
    strNum = mID(strCode, InStr(1, strCode, strFind) + 9, 1)
    
    For i = 1 To intLR
    
    iNextRow = DMax("RecordNo", "tblCodeUpdate") + 1
    
    strTown = "=IFNA(INDEX(Customers!$B$1:B$2000,MATCH($B$" & iNextRow & ",Customers!$A$1:$A$20000)),"""")"
    strPC = "=IFNA(INDEX(Customers!$C$1:C$2000,MATCH($B$" & iNextRow & ",Customers!$A$1:$A$20000)),"""")"




    rs.AddNew
        rs!RecordNo = iNextRow
        rs!Town = strTown
        rs!PostCode = strPC
        rs.Update
        
    Next i

gives me



Now add to Excel sheet

'Add From Access To Excel

intLR = xlSHT.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' Find Last Row With Data
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblCodeUpdate WHERE RecordNo >= 2")
    
    With xlSHT
          
    For x = 1 To intLR
      
    Do Until rs.EOF
        
        xlSHT.Cells(1 + intLR, 3).Formula = rs!Town
        xlSHT.Cells(1 + intLR, 4).Formula = rs!PostCode
  
        rs.MoveNext
    intLR = intLR + 1
    
    Loop
    
    Next x

Gives me



What i have looked into in Excel



Any advice would be totally appreciated

[Welshgasman]

I generally just use and IIF() in my sheets.

IIF(C1<>"", C1*D1,"")

[Micron]

I generally just use and IIF() in my sheets.

You can use IIF in an Excel sheet? Don't think I can.

guessing IFNA in Excel confirms If Nothing

It's an error value that means "no value is available". I would not say that the cell contains nothing. At the very least, it contains a formula and that in itself is not 'nothing'.

[Welshgasman]

You can use IIF in an Excel sheet? Don't think I can.

Sorry, it is IF()

[Micron]

I guessed as much but when people are on different versions you never know.

[DMT Dave]

Thanks WGM and Micron, i have had many although very limited looking into this, my beleif is that i have a value in the cells and not the forumla, not had a huge play around to try and understand excel as much as other MS apps.

@WGM so do you suggest wrap the IF() around the experession ?

I have looked various searches also, 1: how to change cell values to formulas, 2: find a method of copy and paste so the row increments to Match

I know A1 has numeric, A2 has numeric A3 is Sum for example, when copying A3 to A4, A5 etc, it matches the row number if i am correct

when i have tried copying the formula in cell value, paste as many as i want but all try amd Match the cell initially copied, thats what made me think i am working with a vlaue rather than a formula ?

Thanks agian guy's you are all helpful

[DMT Dave]

looking at WGM suggestion, if I am reading that correct
IF A154<>"" (greater than nothing)
,A154"") (=nothing) ? this is to clear the value ?

but will keep the formula, or do i even have a formula ? is it just a cell value ?

[Welshgasman]

If you have a formula in a cell, that will resolve to a vlaue, if it is correct.
You cannot expect to delete the contents of a cell and expect the formula to remain?
Even if you had all that stuff on the far right out of sight and just referred to each cell from a cell on the left, if you delect the contents of a cell on the left, then you lose that value AND formula.

If you can, I would love to learn how.

I had someone on another forum who wanted to move a form to a certian location, and whilst the commands I and another member offered worked fine for us, they did not for him, He then asked if the form had to be open before you could move it?

[DMT Dave]

Well looking at your response Mr WGM, i may need to spend some time writing the formula, copying down to an amount of rows and changing the row number, re click on each row and edit the Match Row number ?

maybe only option

[Welshgasman]

That formula simply says
If the cell value is not nothing, then use it's value, else show nothing.
That means I can have the formulae further down rows where I have yet to have data, rather than having to copy down each time I get new rows populated.

Just the way i like to do it.

[Minty]

Well looking at your response Mr WGM, i may need to spend some time writing the formula, copying down to an amount of rows and changing the row number, re click on each row and edit the Match Row number ?

maybe only option

You have used an absolute cell references ( $B$3 ) everywhere in your formula.
Remove the $'s where you want the formula to automatically change based on the relative cell and you should be able to drag and copy it down as far as you want.