Formula with more than 64 levels of nesting.

[Alvi]

Hi,
I am working on a pricing sheet which has multiple price lists and multiple levels of prices based on the wood species. So I need an item price show up based on the product line, wood species and price level selected.

 

[Joe4]

Welcome to the Board!

You should never really have to create a formula like that. You should instead create a price table, and then use a VLOOKUP or XLOOKUP formula to look up the price you need.
See: VLOOKUP function - Microsoft Support

If you need help setting it up, please provide more details.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[etaf]

would some form of table lookup work
lots of functions available in 365 version
BUT there is too little info there for me to know what you need

Product Line 1 - Wood Species - Price

so a Table constructed like that
then you can use the product line & woodspecies combined to get a price

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Alvi]

Loading Google Sheets

Hi I have shared the speeadsheet. in that sheet please refer to "2.SellingCalculationSheet","9.IVL" sheets. In the "2.SellingCalculationSheet" based on B30, Q22 & AO22 values, BB30 value should be populated from "9.IVL" Sheet. Initially I had issues with the Char length and hence I used AG22 and AG21 to reduce the length.

 

[etaf]

you have not enabled googlesheet to share to anyone, they have to request with a message

 

[Alvi]

sampleQuote.xlsx

1.Enquiry INITIAL ENQUIRY STAGE CUSTOMER NAME: PHONE NO:,,FAX NO:, CONTACT PERSON: PROJECT NAME: Short Description Of Project (Including Application & Equipment) Detail Any Technical Risk, New Technology, Challenging Feed Water, High Spec Outputs, Documentation & Validation Requirements (Powe...

docs.google.com

Sorry I thought I did. In the link above I have enabled it.