Excel complex formula for me..

[ternelson]

Hi All,

Couple of things i havent been able to get sorted out yet, first if C15 is 50 or less add 1 to D15 and 2 to E15, or if C15 is =51 <75 add 1 to D15 and 3 to E15, etc..

2nd if O2 or R2 is Purple,Orange,teal or aqua and H2 is red add(Sheet2-C15 to sheet1-F17),green add(Sheet2-D15 to sheet1-F17),blue add(Sheet2-J15 to sheet1-F17),or tan add(Sheet2-C21 to sheet1-F17) and O4 or S4 is number range 1 to 25 add Sheet2 - B146:B170, hope this makes since... been working on this for the last 2 days and still i suck ....


terry

 

[OGCV1]

Terry,

So first part =IF(C15<50,(D15)+1,IF(C15<70,(D15)+2,D15))
Replace "D15" with the formula you have in that cell.
And same again for E15: =IF(C15<50,(E15)+2,IF(C15<70,(E15)+3,E15))

OGCV

 

[ternelson]

thank you it worked ....


Terry

 

[OGCV1]

No problem,

Working on the other part as well but thought I would ask is there a criteria that determines the colour of the cell?

 

[ternelson]

Sorry i think i may not have explained it just right ... the colors are from a drop down list not the color of the cells .... sometimes its not so easy to write what im trying to get at ... another question for you though if you dont mind... i have a a row of data starting from A15 on sheet2 going to L211, the A column is has ref numbers 4 though 200 columns B though L contain different data tied to the column, is there an easy way for me to pull certain data to sheet1 A37 to N37...( i have a few merged cells thats why it extends out to N... i have used a Vlookup in the cells and it works but its not the cleanest way to do it ... any thoughts ?? thanks for your time..

Terry

 

[OGCV1]

Ah ok had started writing some VBA, but if that the case then try this....
=IF(AND(OR(O2 = "Purple", R2 = "Purple", O2 = "Orange", R2 = "Orange", O2 = "Teal", R2 = "Teal", O2 = "Aqua", R2 = "Aqua"),H2 = "Red"),Sheet2!C15+Sheet1!F17, IF(AND(OR(O2 = "Purple", R2 = "Purple", O2 = "Orange", R2 = "Orange", O2 = "Teal", R2 = "Teal", O2 = "Aqua", R2 = "Aqua"),H2 = "Green"),Sheet2!D15+Sheet1!F17,0))

For the lookup part a INDEX MATCH formula might be what is needed, would need to know what data you were trying to look up though.

 

[ternelson]

the data is set numbers that the numbers will be different depending on the row ....
data info 3 4 5 6 7 8 9 10 11 12
[TABLE="width: 404"]
<colgroup><col width="25"><col width="124"><col width="30"><col width="26"><col width="32"><col width="31" span="2"><col width="21" span="5"></colgroup><tbody>[TR]
[TD="class: xl64, width: 25, align: right"] 108
[/TD]
[TD="class: xl65, width: 124"]Shirt[/TD]
[TD="class: xl65, width: 30, align: right"]1[/TD]
[TD="class: xl65, width: 26, align: right"]3[/TD]
[TD="class: xl66, width: 32, align: right"]1[/TD]
[TD="class: xl65, width: 31, align: right"]7[/TD]
[TD="class: xl65, width: 31, align: right"]5[/TD]
[TD="class: xl67, width: 21, align: right"]6[/TD]
[TD="class: xl68, width: 21, align: right"]-1[/TD]
[TD="class: xl68, width: 21, align: right"]20[/TD]
[TD="class: xl68, width: 21, align: right"]15[/TD]
[TD="class: xl68, width: 21, align: right"]10
[/TD]
[/TR]
</tbody>[/TABLE]
ref item

hopefully this helps ....

 

[Aladin Akyurek]

the data is set numbers that the numbers will be different depending on the row ....
data info 3 4 5 6 7 8 9 10 11 12
[TABLE="width: 404"]
<tbody>[TR]
[TD="class: xl64, width: 25, align: right"] 108 [/TD]
[TD="class: xl65, width: 124"]Shirt[/TD]
[TD="class: xl65, width: 30, align: right"]1[/TD]
[TD="class: xl65, width: 26, align: right"]3[/TD]
[TD="class: xl66, width: 32, align: right"]1[/TD]
[TD="class: xl65, width: 31, align: right"]7[/TD]
[TD="class: xl65, width: 31, align: right"]5[/TD]
[TD="class: xl67, width: 21, align: right"]6[/TD]
[TD="class: xl68, width: 21, align: right"]-1[/TD]
[TD="class: xl68, width: 21, align: right"]20[/TD]
[TD="class: xl68, width: 21, align: right"]15[/TD]
[TD="class: xl68, width: 21, align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
ref item

hopefully this helps ....

What is the expected value for this sample? (Earlier posts in this thread are hard to follow.)

 

[ternelson]

let me try to sum it up with some data samples ... i'll try to get it posted later today or tonight...

Terry

 

[ternelson]

Sorry got side tracked with different project... i need to make sheet names from a list and then copy workbook1 to all the worksheets that are made.. i was able to make the sheets from the list but have not figured out how to copy the 1st sheet any help would be great ... here is what i have so far ...

Sub createsheets()

   Dim newsheets As Worksheet
   Dim r As Integer
   r = 1
   Do While Sheets("Students").Cells(r, 1).Value <> ""
    Set NewSheet = Sheets.Add
    NewSheet.Name = Sheets("Students").Cells(r, 1).Value
    r = r + 1
   Loop
    
 End Sub

the problem im having with this is that the new sheets are pushing my 2nd sheet(students) to the end what is the best way to stop that from happening???
thanks again for all the help guys ...

Terry