Formula with more than 64 levels of nesting.

Alvi

New Member
Joined
Jun 17, 2024
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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.

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

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
 
Upvote 0
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.
 

Attachments

  • Excel.jpg
    Excel.jpg
    155.9 KB · Views: 14
Upvote 0
you have not enabled googlesheet to share to anyone, they have to request with a message
 
Upvote 0
Sorry I thought I did. In the link above I have enabled it.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top