I apologize in advance if this has already been covered in this forum but I'm not sure of the correct terminology to use for what I'm trying to do so I can't search for it.
I'm building a price book spreadsheet with one worksheet covering the various pricing inputs (price per foot in length, price per inch in width, starting length, ending length, as well as the possible widths of the item). The second worksheet converts the data into what will be uploaded as CSV to the ecommerce portion of a website.
In the top cells of that second worksheet, I need to label the data, telling the shopping cart the range of data that can be selected in that column's drop down list.
The problem I'm running into is that one cell's label for the drop down data is dependent on the length (in feet) of the items in that cell. In one case the label needs to show the range of lengths, in one foot increments, from 10' to 34'. In another example, the label needs to show a range of sizes from 5' to 42'. In essence, the starting and ending sizes of the label are not constant and are dependent upon whatever is input into the price input worksheet.
In the first example, the website is expecting the first cell, the column's label, to have this text: 10'|11'|12'|13'|14'|15'|16'|17'|18'|19'|20'|21'|22'|23'|24'|25'|26'|27'|28'|29'|30'|31'|32'|33'|34'
The website will then extract the label's data, the text between the pipes, and convert them into rows for the drop down list.
In the second example, the column's label would be: 5'|6'|7'|8'|9'|10'|11'|12'|13'|14'|15'|16'|17'|18'|19'|20'|21'|22'|23'|24'|25'|26'|27'|28'|29'|30'|31'|32'|33'|34'|35'|36'|37'|38'|39'|40'|41'|42'
I could possibly create a nested IF statement, something like =IF(X+2<y,x&"'|"&x+1&"'|"&x+2&"'|"*x+3&"'",if(x+1<y,x&"'|"&x+1&"'|"&x+2&"'",if(x<y,x&"'|"&x+1"'",x&"'") where="" x="" is="" the="" shortest="" length="" and="" y="" longest="" length.=""
Here I used 4 possible lengths, from the shortest, X, to the longest, Y=X+3, but I would have to build this out to 50 nested IF statements to make it work in almost all circumstances, for the various possibilities of the shortest length being 1' to a longest possible length of 50'.
However, I don't really want to build (nor would Excel probably allow me to build) a nested IF statement this large.
Is there a way to start with what's in a cell, count out a new foot segment in text, until it matches the ending length that was input, without nesting a bunch of IF statements or using VBA?
(I'd prefer to not use either because I won't be able to support the price book once it's complete -- in case someone breaks a large, complicated formula or freaks out because of the macro/VBA warning when they open the spreadsheet.)</y,x&"'|"&x+1&"'|"&x+2&"'|"*x+3&"'",if(x+1<y,x&"'|"&x+1&"'|"&x+2&"'",if(x<y,x&"'|"&x+1"'",x&"'")>
I'm building a price book spreadsheet with one worksheet covering the various pricing inputs (price per foot in length, price per inch in width, starting length, ending length, as well as the possible widths of the item). The second worksheet converts the data into what will be uploaded as CSV to the ecommerce portion of a website.
In the top cells of that second worksheet, I need to label the data, telling the shopping cart the range of data that can be selected in that column's drop down list.
The problem I'm running into is that one cell's label for the drop down data is dependent on the length (in feet) of the items in that cell. In one case the label needs to show the range of lengths, in one foot increments, from 10' to 34'. In another example, the label needs to show a range of sizes from 5' to 42'. In essence, the starting and ending sizes of the label are not constant and are dependent upon whatever is input into the price input worksheet.
In the first example, the website is expecting the first cell, the column's label, to have this text: 10'|11'|12'|13'|14'|15'|16'|17'|18'|19'|20'|21'|22'|23'|24'|25'|26'|27'|28'|29'|30'|31'|32'|33'|34'
The website will then extract the label's data, the text between the pipes, and convert them into rows for the drop down list.
In the second example, the column's label would be: 5'|6'|7'|8'|9'|10'|11'|12'|13'|14'|15'|16'|17'|18'|19'|20'|21'|22'|23'|24'|25'|26'|27'|28'|29'|30'|31'|32'|33'|34'|35'|36'|37'|38'|39'|40'|41'|42'
I could possibly create a nested IF statement, something like =IF(X+2<y,x&"'|"&x+1&"'|"&x+2&"'|"*x+3&"'",if(x+1<y,x&"'|"&x+1&"'|"&x+2&"'",if(x<y,x&"'|"&x+1"'",x&"'") where="" x="" is="" the="" shortest="" length="" and="" y="" longest="" length.=""
Here I used 4 possible lengths, from the shortest, X, to the longest, Y=X+3, but I would have to build this out to 50 nested IF statements to make it work in almost all circumstances, for the various possibilities of the shortest length being 1' to a longest possible length of 50'.
However, I don't really want to build (nor would Excel probably allow me to build) a nested IF statement this large.
Is there a way to start with what's in a cell, count out a new foot segment in text, until it matches the ending length that was input, without nesting a bunch of IF statements or using VBA?
(I'd prefer to not use either because I won't be able to support the price book once it's complete -- in case someone breaks a large, complicated formula or freaks out because of the macro/VBA warning when they open the spreadsheet.)</y,x&"'|"&x+1&"'|"&x+2&"'|"*x+3&"'",if(x+1<y,x&"'|"&x+1&"'|"&x+2&"'",if(x<y,x&"'|"&x+1"'",x&"'")>
