Text as Number - formula to read data

[mpatsis]

I am looking for a formula or method to lookup Text as Number without changing the value to a number.
SheetExport is exported data. Default format is Text as Number - this cannot be changed. This worksheet is usually saved as a separate workbook.
SheetA is destination worksheet. Code is entered in column A and remaining cells should auto populate based on formulas
SheetB is saved in same workbook as SheetA, used to populate Name (and additional information not shown in this example)

In the attached example row 3 & 4 are displaying correctly but in order for it to work I had to place a " ' " in front of number in SheetB. This isn't practical for each cell.

Ultimately, I would like to change the format/formula in SheetA and leave the other two as is.

Any help is appreciated.
Thanks

[Richard Buttrey]

Hi,

It's not at all clear (at least to me) what it is you're trying to achieve or exactly how you use this workbook. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results. You say you want to change Sheet A's format/formula but don't say in what way. Neither do I understand why you mention the label ' marker.

One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data. Absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result.
You must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data).

[mpatsis]

Hi,
I tried to articulate clearly but obviously missed the mark...
We use this sheet to pull stock levels from our accounting software. We export the data then use this sheet to present it in a more useful format. I would like to enter the product code in the sheet then update the data and have it display current information.
My desired result:
2802550 is input in cell B7 (SheetA)
Formula in D7 (SheetA) looks up 2802550 (SheetA) in SheetB and returns the value from column E (SheetB)
Formula in G7 (SheetA) looks up 2802550 (SheetA) in SheetExport and returns the value from column D (SheetExport)
Currently, if I input 2802550 (SheetA) into B7 with either General or Text format, D7 (SheetA) populates correctly but I get N/A in G7 (SheetA). If I put an apostrophy in front of 2802550 (sheetA), G7 (SheetA) populates correctly but I get N/A in cell D7 (sheetA).
In order to get them both to work I have to put an apostrophy in front of 2802550 (SheetA) and an apostrophy in front of 2802550 in SheetB. And ultimately all values in columns b&c in SheetB. This is because I can't (or don't want to) change the format of SheetExport.
Changing the format to 'text' doesn't seem to do anything.
I hope that is a better explanation of the situation.

Also, I noticed I have a formula with a link to an external file in column H (SheetA) - ignore that, it is the link to the real SheetExport

Thank you

[samba_ravi]

g2=IFERROR(VLOOKUP(B3,SheetExport!$A$4:$G$11,4),VLOOKUP(B3&"",SheetExport!$A$4:$G$11,4))
Try this, copy an paste towards down

[CARACALLA]

G3=SUMIF(SheetExport!$A$4:$A$11,SheetA!B3,SheetExport!$D$4:$D$11)

copy across and down

[Richard Buttrey]

2802550 does not exist in the Export sheet. I presume that was a typo.

Since the references in the Export sheet are text the VLOOKUP needs to be

Please Login or Register  to view this content.