Formula Question

[RVelle]

Hello,

Having an issue, currently using Excel 2016, formula's only work when I overwrite a concatenated formula by manually updating those fields with the exact same data that was being auto populated.

I am using this as a small database, for 800 numbers, concatenating the base 800 numbers with a leading "1" infront of them, then using the concatenated field to do vlookups against PBX data to pull that in for easier reference. When I leave the concatenated data in the cells, the vlookups remain blank (no data/no errors at all), if I instead manually write data in the field that had the concatenate in it, the same data it was populating (ie 18001112222), that row of VLookups kick off as normal and all data good (all formulas are set to auto, and I have tried to also run them with Calc now, with no change, formats all set to General).

example:
A1: 8001112222
B1: =concatenate("1",A1)
C1: =VLOOKUP($B1,'PBX'!$A$3:$R$6001,2,FALSE)

*real C1 formula which again works fine only if I manually update B1: =IF(ISNA(VLOOKUP($AV4,'PBX Data'!$A$3:$R$6001,'Production 800s'!AW$2,FALSE)),"",IF(ISBLANK(VLOOKUP($AV4,'PBX Data'!$A$3:$R$6001,'Production 800s'!AW$2,FALSE)),"",VLOOKUP($AV4,'PBX Data'!$A$3:$R$6001,'Production 800s'!AW$2,FALSE)))

If I manually update B1 to 18001112222, C1 populates as it should, if I leave B1 with the formula, C1 remains blank.

Any thoughts?

Thanks in advance,

RVelle

 

[Fluff]

Concatenate returns a text string, so try
=CONCATENATE(1,A1)*1

 

[RVelle]

That worked, thanks much!

Is that something newer after excel v2010? I have never done it that way prior and they always worked.

Thanks again

RVelle

 

[Fluff]

As far as I'm aware the concatenate function has always returned a text value, but it's not something I've used much.