index/match formula ?

[sp1974]

Hello. I need some help with the attached spread sheet. Current I am using two spreadsheets for similar press and am combining the two. What I need help with this time is currently in cell c9 the formula is =ROUNDDOWN(17.7/C7,0) or =ROUNDDOWN(39.3/C7,0) depending on which press I am using. The presses are shown in cells d34 - q34. Indigo =17.7 gilboa = 39.3. I have added these numbers to a table in the scales tab a24:e26. Is there a way to write a formula in cell c9 on the input tab to combine the 2 above formulas and insert the correct 17.7 or 39.3 when required?

[sp1974]

I will have to move from a single cell result to multiple cell result since there are multiple quantities required. I know this.

[NBVC]

How do you know which press you are using? Is it from A1?

I put Indigo in A2 and changed your formula to:

=ROUNDDOWN(VLOOKUP(A2,Scale!$A$25:$E$26,5,0)/C7,0)

You would probably have to enter the press name somewhere as I did or extract it from A1....

[NBVC]

Let's put it this way.. How did you determine to use 17.7 in your sample worksheet?

[sp1974]

17.7 is the drum repeat for the indigo press and 39.3 is the drum repeat for the gilboa. It runs on the indigo based on a size limitation 0-400,000 square inches and 400,001 runs on gilboa. The press name based on size limitations is shown d34 - q34

[NBVC]

I am going to assume that A1 containing the word "Indigo" means you are looking at Indigo stats... because I still don't know how you determined to use Indigo stat in your formula...

=ROUNDDOWN(VLOOKUP(IF(ISNUMBER(SEARCH("Indigo",A1)),"Indigo","Gilboa"),Scale!$A$25:$E$26,5,0)/C7,0)

[sp1974]

the Indigo in a1 is just there because I havent changed it yet. Indigo or Gilboa is based on total square inches by quantity. Take (c4*c5) and multiply that by quantity d18:q18. The results are shown in d20:q20. 0-399,999 would run on the indigo and 400,000 + run on the gilboa as shown in d34:q34. does that help any?

[sp1974]

the formula i am looking to achieve will figure the number around based on the drum repeat. example 4" long label would equal 4 around on the 17.7 drum and would equal 9 on the 39.3 drum repeat. the 4 or 9 is the answer I want to show

[NBVC]

I understand all that, but maybe I just am not seeing something obvious.

You have only one cell with this formula in it, cell C9, correct?

How do you know which of the presses to account for in this one formula? Your table in D34:Q34 shows for both presses... so the formula won't know by that, what value to use?

[sp1974]

right I have to change it to multiple cells was going to add a row to d21:q21 to show the number around for each quantity. sorry for confusion. i stated in my 2nd post i realized i needed to do that so sorry if i was unclear

[sp1974]

I guess all this helped me to work threw it. I thought it was going to be more difficult than it needed to be but the forlmula I used to determine the press will work with a few minor changes

=IF(D$20<400000,ROUNDDOWN(17.7/$C$7,0),ROUNDDOWN(39.3/$C$7,0))

Sorry for the frustration/confusion.

[sp1974]

yours works too. Im using yours. thanks again

[NBVC]

So then:

=ROUNDDOWN(VLOOKUP(D34,Scale!$A$25:$E$26,5,0)/$C$7,0)

this assumes you are always looking a C7.... if not, then

=ROUNDDOWN(VLOOKUP(D34,Scale!$A$25:$E$26,5,0)/C7,0)

[NBVC]

Well, I thought you wanted to refer to the table (in case, of future changes to the stats or added machines, etc..).... hence, the Vlookup()....

[sp1974]

I thought thats what I needed to do. I am going to refer to the table just for the reasons you stated so I will be using your formula.

[sp1974]

the presses are shown in cells d34 - q34

[sp1974]

right the larger quantities will run on gilboa and smaller runs on indigo