 SOLVED

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

=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

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

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)

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

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?

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

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?

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

How do I attach a sample workbook?

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

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.

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

Hi Hans,

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

Thank you for all of your help.

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

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

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

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?

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

It should return the actual value in the column.

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

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

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

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

Try this then.

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

It worked. Thanks a bunch for your help.