SOLVED

Can someone tell me why I keep getting a naming error on this formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-3069647%22%20slang%3D%22en-US%22%3ECan%20someone%20tell%20me%20why%20I%20keep%20getting%20a%20naming%20error%20on%20this%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3069647%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3DFIXED%20EXPENSES%2CR2%3D308-0)%2CBgt_FixedExp!D%3AD%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3DMATTRESS%20Revenue%2CR2%3D101-1)%2CBgt_Mattress!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3DPillow%20Revenue%2CR2%3D111-1)%2CBgt_Pillow!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3DOther%20Revenue%2CR2%3D120-1)%2CBgt_Other!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3DDiscounts%2CR2%3D190-4)%2CBgt_Discounts!H%3AH%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3DVariable%20EXPENSES%2CR2%3D201-1)%2CBgt_VarExp!K%3AK%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3069647%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3069649%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20someone%20tell%20me%20why%20I%20keep%20getting%20a%20naming%20error%20on%20this%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3069649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1284510%22%20target%3D%22_blank%22%3E%40rachel1205%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELiteral%20text%20values%20must%20be%20enclosed%20in%20straight%20double%20quotes%20%22%20%22%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22FIXED%20EXPENSES%22%2CR2%3D308-0)%2CBgt_FixedExp!D%3AD%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22MATTRESS%20Revenue%22%2CR2%3D101-1)%2CBgt_Mattress!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22Pillow%20Revenue%22%2CR2%3D111-1)%2CBgt_Pillow!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22Other%20Revenue%22%2CR2%3D120-1)%2CBgt_Other!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22Discounts%22%2CR2%3D190-4)%2CBgt_Discounts!H%3AH%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22Variable%20EXPENSES%22%2CR2%3D201-1)%2CBgt_VarExp!K%3AK%2C0)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20308-0%20etc.%20are%20text%20values%20too%2C%20instead%20of%20calculations%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22FIXED%20EXPENSES%22%2CR2%3D%22308-0%22)%2CBgt_FixedExp!D%3AD%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22MATTRESS%20Revenue%22%2CR2%3D%22101-1%22)%2CBgt_Mattress!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22Pillow%20Revenue%22%2CR2%3D%22111-1%22)%2CBgt_Pillow!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22Other%20Revenue%22%2CR2%3D%22120-1%22)%2CBgt_Other!F%3AF%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22Discounts%22%2CR2%3D%22190-4%22)%2CBgt_Discounts!H%3AH%2C0)%2CIF(AND(VLOOKUP(I2%2CAccounts!A%3AE%2C5%2C0)%3D%22Variable%20EXPENSES%22%2CR2%3D%22201-1%22)%2CBgt_VarExp!K%3AK%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3070216%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20someone%20tell%20me%20why%20I%20keep%20getting%20a%20naming%20error%20on%20this%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3070216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20tip%2C%20however%2C%20now%20I%20am%20getting%20a%20Value%20error.%20I%20did%20as%20you%20suggested%2C%20and%20put%20double%20quotes%20around%20Fixed%20Expenses%20etc...%20and%20the%20308-0%20etc..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20why%20I%20am%20getting%20a%20value%20error%20now%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

=IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=FIXED EXPENSES,R2=308-0),Bgt_FixedExp!D:D,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=MATTRESS Revenue,R2=101-1),Bgt_Mattress!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=Pillow Revenue,R2=111-1),Bgt_Pillow!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=Other Revenue,R2=120-1),Bgt_Other!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=Discounts,R2=190-4),Bgt_Discounts!H:H,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)=Variable EXPENSES,R2=201-1),Bgt_VarExp!K:K,0)

13 Replies

@rachel1205 

Literal text values must be enclosed in straight double quotes " ":

 

=IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="FIXED EXPENSES",R2=308-0),Bgt_FixedExp!D:D,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="MATTRESS Revenue",R2=101-1),Bgt_Mattress!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Pillow Revenue",R2=111-1),Bgt_Pillow!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Other Revenue",R2=120-1),Bgt_Other!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Discounts",R2=190-4),Bgt_Discounts!H:H,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Variable EXPENSES",R2=201-1),Bgt_VarExp!K:K,0)

 

If 308-0 etc. are text values too, instead of calculations:

 

=IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="FIXED EXPENSES",R2="308-0"),Bgt_FixedExp!D:D,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="MATTRESS Revenue",R2="101-1"),Bgt_Mattress!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Pillow Revenue",R2="111-1"),Bgt_Pillow!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Other Revenue",R2="120-1"),Bgt_Other!F:F,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Discounts",R2="190-4"),Bgt_Discounts!H:H,0),IF(AND(VLOOKUP(I2,Accounts!A:E,5,0)="Variable EXPENSES",R2="201-1"),Bgt_VarExp!K:K,0)

@Hans Vogelaar 

 

Thank you for the tip, however, now I am getting a Value error. I did as you suggested, and put double quotes around Fixed Expenses etc... and the 308-0 etc..

 

Any idea why I am getting a value error now?

@rachel1205 

Could you attach a sample workbook demonstrating the problem, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

How do I attach a sample workbook?

@rachel1205 

Some but not all users see an area "Drag and drop here or browse files to attach" below the area where you compose a reply.

If you see it, simply drag the workbook from Windows Explorer (or Finder) into that area.

If not, you'll have to follow the other suggestion from my previous reply.

Hi Hans,

I will get this to you via One Drive this weekend.

Thank you for all of your help.
Hans, here is the link to my excel file with the value problem. I put a Tab labelled Instructions to help you understand what I am doing. https://1drv.ms/x/s!AgXFzJ74gEObam1YmvJaHILI_0I?e=YfdneS

@rachel1205 

Thanks. I see now that the formula tries to return an entire column such as Bgt_FixedExp!D:D or Bgt_Mattress!F:F.

That is not possible of course. Can you explain what the formula should actually return?

It should return the actual value in the column.

@rachel1205 

That doesn't make much sense. See if the attached version does what you want.

No, it does not work. Row 5 should return a value of -16083, which is from column D in the Bgt_FixedExp tab and Row 20 should return a value of -7500, which is from column D in the Bgt_FixedExp tab also. All other values for column 2-21 should be 0. I hope this helps.
best response confirmed by rachel1205 (Occasional Contributor)
Solution

@rachel1205 

Try this then.

It worked. Thanks a bunch for your help.