LinkBack URL
About LinkBacks
could anyone help with a formula please
i have created a dropdown list and i would like to query a figure with a range and compute text that is in another column based on a match within the range
many thanks
steve
could anyone help with a formula please
i have created a dropdown list and i would like to query a figure with a range and compute text that is in another column based on a match within the range
many thanks
steve
Last edited by AliGW; 05-16-2020 at 11:35 AM.
Sounds like you need VLOOKUP or INDEX/MATCH based on your description.
Check out the Forum Rules though as the Moderators might not be happy with your thread title.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Really appreciate your help, do you have an example
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Can't really give an example without, well, an example (worksheet)
hello,
I've hopfully added an example, could you have a look please
what i have is a drop down list of Thickness with different widths, i would like the product code to popup if width falls within the lower range and the upper range.
so for 0.350 the width is either 1.23 or 1.33 and this would fall into 1.2 - 1.6 range and the product code for this would either be SRB1-ABSNL OR SBSR1-ABSNL
could you please point me in the right direction with a formula to work this out
many thanks Steve
Last edited by scrumpywill; 05-13-2020 at 01:49 PM.
This proposal employs two helper cells (M3:N3) which may be moved and/or hidden (i.e. font set to white) for aesthetic purposes.
M3:N3 are populated using: =INDEX(F6:F19,MATCH($L3,$E6:$E19,0))
Since there may be more than one product code L7:L9 are populated using:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. As you are new here, I have done it for you this time.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
this is fantastic !! many many thanks, im i ok asking more question if i struggle as i build the table
really appreciate this "Awesome"
cheers steve
Steve - please acknowledge that you have read and understood the note in post #8.
Then, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
thank you
hello, thank you for your formula, its what i want but don't fully understand how this works or how to change and add more
if i select a thickness 0f 0.400 how would i get SRBZ to populate in the list as well as the others that already get populated ?
also if you could help me understand this part of the Formula AGGREGATE(15,6 what does the 15,6 refer to
thanks very much for your help
The 15 refers to the SMALL function and the 6 is an option to ignore errors produced in the array. If you utilize the Evaluate Formula feature you'll see that many #DIV0 errors are displayed before the final evaluation of the formula.
As to the thickness of 0.400, based on the description in post #6 I don't understand how SRBZ should be displayed as the upper range is 1.3 (cell B26) and the cell G7 displays 1.40
Let us know if you have any questions.
Hello, thank you i think the formula needs to be equal to 1.3 as well, could this be added ?
Perhaps this will work.
Since there is only one value for the channel in this instance, leave the upper range cell blank.
The formula could then be modified to read:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Hello, good morning, I've just checked the new formula and the SRBZ is computed above the 1.3mm would it me possible for the formula to include the SRBZ from 1.2 to 1.3 and anything in-between
sorry i think its my communication and understanding "not understanding" how the formulas work.
many thanks for your help and support
Steve
original formula: =IFERROR(INDEX(J$24:J$68,AGGREGATE(15,6,(ROW(J$24:J$68)-ROW(J$23))/(A$24:A$68<=M$3)/(B$24:B$68>=N$3),ROWS(A$1:A1))),"")
The formula checks the rows of lower and upper ranges (24:68) to find those that have lower ranges less than or equal to the value in M3 and upper ranges that are greater than or equal to the value in N3.
Putting 1.2 in A26 and 1.3 in B26 does not result in SRBZ being displayed in the list for 0.400 thickness, on the other hand putting 1.3 in A26 and 1.4 in B26 does.
Let us know if you have any questions.
Hello thank you again,
i think im starting to understand you have M3 & N3 as the upper and lower range could the formula work out as a24 b24 and checked f6 & g6 i think then the f7 would compute the SRBZ
i know its difficult for me to explain as i see "sorry" we have a match in cell f7 so the product code would fall into this as well
thanks again for your help
steve
I am not sure that I understand what you are asking in post #18, however I believe that I have a proposal that remedies the problem of SRBZ being computed above the 1.3mm as stated in post #16.
Formula:Please Login or Register to view this content.
As seen in the attached file SRBZ will be included for .400 but not .450
Let us know if you have any questions.
This is excellent, thank you very much for your help, is it possible to have the formula on a different workbook and the dropdown on another workbook
many thanks steve
In my opinion, doing a lookup from a different workbook is probably better done using VBA. You could put the formula and drop down on another worksheet in the same workbook and still use formulas however. If you don't want the other information on the Example sheet to be visible then it could be hidden as in the attached copy of the file.
Note that the formula is modified to read:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Hello, so so nearly getting there with this project the 0.600 thickness should compute the same product as all the others down to 0.350 but in our formula is doesn’t show - could you see it the formula can look at the “width” and compare a match in-between the upper & lower range or equal to the upper or lower range
If I select 0.600 the width is 1.60 in F11 so this product would be sbsr1-absnl but if I select G12 then the product code would be srbmlz
Hopefully with your help I can get this working, thank you for sticking with me
Cheers Steve
I'm going to attempt this from a different direction.
A column (K) has been added to rows 24:67 on the Example sheet, populated using: =OR(AND(Sheet1!D$2>=A24,Sheet1!D$2<=B24,F$3=E24),AND(Sheet1!E$2>=A24,Sheet1!E$2<=B24,G$3=E24))
The output on sheet 1 is populated using: =IFERROR(INDEX(Example!J$24:J$67,AGGREGATE(15,6,(ROW(Example!J$24:J$67)-ROW(Example!J$23))/(Example!K$24:K$67=TRUE),ROWS(Example!A$1:A1))),"")
In addition to SBSR1-ABSNL and SRBMLZ the formulas also identify SRB1-ABSNL so there it still possible something that I don't understand about how selections are made.
Let us know if you have any questions.
thank you, on the file that i download there is only one work sheet, the example sheet isn't in the file - could you reattach so everything is in the one file. i like how the dropdown in on its own sheet, thank you for this
looking at the figures the calculations are looking good, but i will confirm one i see the full file
thanks again this is really helpful
cheers Steve
The Example sheet is there, just hidden to illustrate keeping the data and output in the same workbook might work. To see the Example sheet select the Format feature on the Home tab > select Hide & Unhide > select Unhide sheet. In this case the only hidden sheet is Example so you simply select OK.
Let us know if you have any questions.
That is awesome, thank you - i will have a good review over this and see if i can get my head round everything - im adding more info, but i can see how you are making this work
mega thank you, have a magic weekend
cheers Steve
Hello I hope your well, ive been going more work on the sheet today - i have the extra column giving me the true & false but im struggling to get the product code to show - can you see anthing wrong in the formula =IFERROR(@INDEX(Rev Crease Range!J$31:J$75,AGGREGATE(15,6,(ROW(Rev Crease Range!J$31:J$75)-ROW(Rev Crease Range!J$30))/(Rev Crease Range!K$31:K$75=TRUE),ROWS(A$1:A1))),"")
Everything else is working apart showing the product code
could you have a look please
many thanks Steve
I am not sure why there would be an at symbol in front of INDEX, I suggest that you try the formula with it removed.
To give more complete help, we would probably need to see the file in which the formula is being used.
Let us know if you have any questions.
Last edited by JeteMc; 06-04-2020 at 10:51 AM.
hello, ive added the workbook, can you see where ive gone wrong ?
cheers Steve
Try the following in cell K8 on the Example sheet: =IFERROR(INDEX('Rev Crease Range'!J$31:J$75,AGGREGATE(15,6,(ROW(J$31:J$75)-ROW(J$30))/('Rev Crease Range'!K$31:K$75=TRUE),ROWS(A$1:A1))),"")
As the reference is to a sheet name that has spaces, Rev Crease Range, it needs to be enclosed with the tick marks.
BTW (ROW(J$31:J$75)-ROW(J$30)) is just producing an array so it doesn't need to reference the specific cells on the Rev Crease Range sheet.
Also this is a regular formula, just paste it into the cell and press the enter key before dragging the fill handle down.
Let us know if you have any questions.
good morning, im just looking at this formula =OR(AND(Sheet1!D$2>=A24,Sheet1!D$2<=B24,F$3=E24),AND(Sheet1!E$2>=A24,Sheet1!E$2<=B24,G$3=E24))
im just wondering if the AND part should be =E29 at the end so it refers to the 0.8 in that cell =OR(AND(Sheet1!D$2>=A24,Sheet1!D$2<=B24,F$3=E24),AND(Sheet1!E$2>=A24,Sheet1!E$2<=B24,G$3=E29))
would you be able to explain how this formula works for me
many thanks
steve
Hello,
I've just attached another update, could you have a look at this for me I'm unable to get the product codes for the corrugated, could you please review and advise
many thanks
Replying to post #31: No, the formula is written as it should be.
The formula compares the data row by row, so in row 24 if the value in F3 is equal to the value in E24 AND the value in sheet1 D2 is somewhere between the value in A24 AND the value in B24 OR if the value in G3 is equal to the value in E24 AND value in sheet1 E2 is somewhere between the value in A24 AND the value in B24 the display TRUE, if those conditions can not be met display FALSE.
Replying to post #32: I believe that the formula for the solid column should be: =OR(AND(Example!L$5>=A31,Example!L$5<=B31,Example!L$4=E31),AND(Example!M$5>=A31,Example!M$5<=B31,Example!M$4=E31))
Same logic as stated above.
I believe that the formula for corrugate should read:Formula:Please Login or Register to view this content.
Let us know if you have any quesitons.
many thanks - if you can have a look at p7 i would like this to show SRBA/BABS as it meets the .600 thickness
thanks again
Convert the formula in K20 to read and then copy down:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
thank you, I must have something missing from the formula in row L49
if i select the .600 thickness i get the following matches and in-between's
Example sheet O16 Match A39 B39
Example sheet O16 A40 B40
Example sheet O16 A41 B41
Example sheet O16 A42 B42
Example sheet O16 A44 B44
Example sheet R16 A43 B43
Example sheet R16 A45 B45
Example sheet R16 A46 B46
Example sheet R16 A47 B47
would you be able to have a look to see where im going wrong
many thanks
I am guessing that these matches and betweens are for corrugate.
When I select .600 thickness I don't get any product codes for corrugate.
If I should get matches then I am again confused as I can not find any.
My understanding is that O16 (2.70) is the thickness of the width of 1.5 so it should only match or be between in rows 45:54. The smallest low range in those rows is 3.8 so none of those should work.
Similarly R16 (4.20) is the thickness for the width of 3.0 so it should only match or be between in rows 71:75. Again no possibilities as the smallest low range is 5.7.
Let us know if you have any questions.
hello, if you could go to the rev crease range and see the formula in =ROUND(L7*J$2+J$3,1) through to cell S for each of the thicknesses- these are widths required to fit into the lower range A column and upper range column B "maybe i should name these Lower Range widths & Upper Range Widths"
im looking for any matches or equal to or inbetween for each thickness in column L
does this make any sense - maybe the solid board needs a slight tweak
cheers Steve
It sounds as if you are saying to ignore the widths in the cells in row 15 (Example sheet) and just find matches and betweens with the values in row 16 (Example sheet) for the lower and upper ranges (columns A:B of the Rev Crease Range sheet).
If that is the case then the formula in cell L39 (and then copy down) may be modified to read:Formula:Please Login or Register to view this content.
If that isn't the case then please talk us through the process that you would use as if you were doing this manually, as in "once a thickness is selected the next step would be..."
Let us know if you have any questions.
thank you, this looks correct but let me run over this in the next few days - i see have the calculation is working now.
I'm learnings lots from you and enjoying the experience
have a great weekend
cheers Steve
Hello, i hope you're ok ive checked the new formula and this is giving me the results spot on now using this =OR(AND(Example!L$16>=A39,Example!L$16<=B39),AND(Example!M$16>=A39,Example!M$16<=B39),AND(Example!N$16>=A39,Example!N$16<=B39),AND(Example!O$16>=A39,Example!O$16<=B39),AND(Example!P$16>=A39,Example!P$16<=B39),AND(Example!Q$16>=A39,Example!Q$16<=B39),AND(Example!R$16>=A39,Example!R$16<=B39))
I've also changed the solid board to this, which is also working spot on
=OR(AND(Example!L$5>=A31,Example!L$5<=B31),AND(Example!M$5>=A31,Example!M$5<=B31))
I've another question if you could help? i want to highlight a recommended product code out of the range this could be one, two or three maybe four - if you look at the corrugated 0.650 we get 14 products that would suit that board thickness, i would like to guide customers to use a certain product out of the range.
thanks again for your help
cheers Steve
I feel that whether or not that can be accomplished in Excel will depend on the criteria that goes into making the decision of which products you would recommend.
I.E.: If the criteria a objective then I would imagine it is possible, if subjective then I am less confident.
So I believe the question we need answered, in order to help, is which of the 14 products would be recommended and why?
Good Morning, thank you for your message.
I've uploaded the sheet after adding some info.
I've highlighted areas which have a slightly different specification for solid & corrugated board
Solid board, if you see the highlighted areas the recommendation would be to use 07 & 08 nibs upto 0.600 micron and from 0.650 we would recommend anything with a 1,0mm nib so the recommended product codes would be K39 & K40 even though K34:K37 would still work if used
I've colour coded the corrugated to the same
thanks again for you help
Steve
I am still not sure that I understand, however here is an attempt.
On the Rev Crease Range sheet cells V4 and V8 display the largest width for each product using: =MAXIFS(E31:E75,K31:K75,TRUE) and =MAXIFS(E31:E75,L31:L75,TRUE)
The Solid Board recommended is populated using:Formula:Please Login or Register to view this content.
The Corrugate recommended is populated using:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Sorry for the delay coming back to you, I've been putting the new sheet through its paces, its looking good, Thank you !
I have a few meeting coming up where i will discuss and let you know how i get on.
this so far has been a fantastic learning experience and really shows me the power of excel with the correct training, thanks again for all your hard work
cheers Steve
You're Welcome and thank you for the feedback. If the meetings go well then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks