YTL Excel #119: Amazing Lookup Solution!

Hi! I am trying to do the funcion where the person moves the bar and it moves the days. Can anyone help me on how to do that. I would really appreciate any advice on where to go forward on this. You can find the file on this site. Just look in the middle of the page to get the file and let me know if it is something doable.
Sebastian

forwarded to me - i did not create this.....

MAGIC #1

An Indian discovered that nobody can create a FOLDER anywhere on the
Computer which can be named as "CON". This is something pretty
Cool...and Unbelievable. ... At Microsoft the whole Team, couldn't answer why this
happened!
TRY IT NOW ,IT WILL NOT CREATE " CON " FOLDER


MAGIC #2

For those of you using Windows, do the following:

1.) Open an empty notepad file
2.) Type "Bush hid the facts" (without the quotes)
3.) Save it as whatever you want.
4.) Close it, and re-open it.

is it just a really weird bug? ?


MAGIC #3

Microsoft crazy facts

This is something pretty cool and neat...and unbelievable. .. At
Microsoft
the whole Team, including Bill Gates, couldn't answer why this happened!

It was discovered by a Brazilian. Try it out yourself...

Open Microsoft Word and type

=rand (200, 99)

And then press ENTER
Then see the magic....... ......... ......... ......

So I have this amazingly simple macro, and the speed difference between excel 2000 vs 2007 is simply amazing. On the sheet this person is using it normally takes 10 mins to run, today she ran it on Excel 2007 and it took 5 hours!!! Any help would be great here.


Code:

    Do While Selection  ""
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ActiveCell.Offset(4, 0).Select
    Loop

Thanks

I started making a spreadsheet which basically has a data sheet which is a list of invoices and a customer info sheet which holds additional information about each customer.

It also has DD input and Sage input sheets, the idea being you enter a date and it returns the relevant information from the first 2 sheets which can be used to import onto a BACs system and Sage.

What I've done so far works perfectly however, it's going to be dealing with thousands of rows of information and if I copy all the formulas down it makes it massive and it crashes if it tries to process anything/save/open etc. Plus I've got more sheets and things to do on it yet.

So, I was wondering if there was a way to reproduce what it currently does using macros? Or if anyone has any other suggestions?

The file is uploaded here

Please let me know if it's not clear what I've done, it probably much more complicated than it needs to be!

Hi guys,

I am looking for a solution that would help me to lookup a series of codes within a list of concatenated codes.

the concatenated codes list looks like this

"Header status"
2INV 2TOP 3PAE OPN
2INV 2LEA DYNM L1 OPN REL
2INV DYNM OPN
2DUP 2INV LX OPN
2INV 3PAE OPN
2INV L1 OPN
2INV L1 OPN
2INV 2LEA L1 OPN
2NTI ID69 OPN
2NTI ID69 OPN
2INV 2LEA DYNM L1 OPN REL

while my code list looks like this

"Code"
CFGU
6CFG
2CFG
2BDB
6E0
6DIS
2VAT
2CMT
2CTY
2SHP
1ADR
2PUR
2LEA
LXOH
6OBN
6OBS
OBN
OBS
6B0
2DUP

I need a solution that does not involve VB since there is no one to update the VB script if there will be future changes.

Thank you guys

I'm having a tough time trying to figure this one out. What I am trying to achieve is to calculate the P&L of each row, with each contract having a different way of calculating its p&l. The solution would be simple if these contracts did not expire, when they are about to expire it is rolled, position closed and the new contract is then traded so it appears consecutively which causes issues in my original formula since I did not anticipate it (each calculation for P&l has to look to see if there was a previous open position as well, it wouldn't do that if it is a new contract since the previous open position would be 0). There might be an instance where two different contracts roll on the same day as well. I tried named formulas, but these formulas wouldn't fit when I try to define it and gave me a wrong calculation. Any thoughts or solutions are welcome. If there needs to be a better explanation, ask away. Thanks!

EDIT*** - I want a formula that would check to see which contract it is and calculate the P&L based on which contract type and use the respective formula to calculate (from below), check to see if there was an open position in the previous of the same contract and if the previous line was a roll for the contract.

EDIT2*** - My previous formula was =IF(LEFT(B50,2)="TY",(H50-F50)*C50*1000-(ABS(C50)*1.02)+(H50-G50)*D50*1000-(ABS(D50)*1.02)+((H50-H46)*E46*1000),IF(LEFT(B50,2)="RX",((H50-F50)*C50*1000-(ABS(C50)*0.7)+(H50-G50)*D50*1000-(ABS(D50)*0.7))*I50+((H50-H46)*E46*1000),IF(LEFT(B50,2)="ES",(H50-F50)*C50*50-(ABS(C50)*1.82)+(H50-G50)*D50*50-(ABS(D50)*1.82)+((H50-H46)*E46*50),IF(LEFT(B50,2)="CL",(H50-F50)*C50*1000-(ABS(C50)*3.37)+(H50-G50)*D50*1000-(ABS(D50)*3.37)+((H50-H46)*E46*1000),0))))

But it runs into the issue of miscalculating since a rolling a contract (Lines 9 and 10)shifts the formula to the wrong cells it should be taking date from.

The calculation for each contract is as follows (they check if there is a previous open position, would work if contracts are same e.g. CLX0 and CLX0, not CLX0 and CLZ0):
TY=(H6-F6)*C6*1000-(ABS(C6)*1.02)+(H6-G6)*D6*1000-(ABS(D6)*1.02)+((H6-H2)*E2*1000)
RX=((H7-F7)*C7*1000-(ABS(C7)*0.7)+(H7-G7)*D7*1000-(ABS(D7)*0.7))*I7+((H7-H3)*E3*1000)*I7
ES=(H8-F8)*C8*50-(ABS(C8)*1.82)+(H8-G8)*D8*50-(ABS(D8)*1.82)+((H8-H4)*E4*50)
CL==(H9-F9)*C9*1000-(ABS(C9)*3.37)+(H9-G9)*D9*1000-(ABS(D9)*3.37)

******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout J6 J7 J9 J10 J11 J12 J13 J14 =
A B C D E F G H I J 1 Date Contract Long Sell Net Avg Buy Avg Sell Closing Price Exchange Rate P&L 2 10/18/2010 TYZ0 19 -16 2      126.6439       126.5908           126.8906    -801.32 3 10/18/2010 RXZ0 47 -44 -2      130.5609       130.5959           130.5600  1.3999 2,866.67 4 10/18/2010 ESZ0 23 -25 -1   1,171.2391    1,171.5700        1,178.2500    -212.36 5 10/18/2010 CLX0 21 -17 2        81.8424         81.7088             83.0800    -1,108.06 6 10/19/2010 TYZ0 57 -53 6      126.8451       126.8060           127.0313    -1,159.07 7 10/19/2010 RXZ0 73 -76 -5      130.2608       130.2566           130.2300  1.3741 447.54 8 10/19/2010 ESZ0 31 -34 -4   1,170.7419    1,169.8382        1,163.7500    119.20 9 10/19/2010 CLX0 18 -20 0        81.5444         81.9335             80.1600    4,581.94 10 10/19/2010 CLZ0 8 -7 1        80.4913         80.9386             80.1600    2,749.45 11 10/20/2010 TYZ0 46 -51 -5      126.9402       126.9210           127.0469    -1,520.81 12 10/20/2010 RXZ0 58 -60 -2      130.0741       130.0888           129.9500  1.3959 3,416.33 13 10/20/2010 ESZ0 49 -42 7   1,171.1276    1,170.3452        1,174.7500    -2,740.62 14 10/20/2010 CLX0 25 -26 0        81.2100         81.1715             82.5400    -121.87 Sheet1  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi All,

I am needing a solution for this little problem, I have a two sets of data, both which I have calculated variances plan Vs Actual, I would like to compare the previous months variances of different sheets. I have put a sort on the variances in descending order.

I want to match two fields E.g. B1 & D1 in sheet1 AND if they match then bring back L1 data in sheet2??? The both sheets have same column names and in same order.

Hope i have put it in a way which is understandle.

Thanks

Ok I have a row of stock tickers on the top row, just below them, I have the returns.

Ticker A B C D E
Return 1% 3% 5% 0.5% 7%

I want to put a formula in a cell that takes the ticker with the highest return. I don't want to manipulate the rows, so for example, I can do LARGE or MAX to get the highest return and then maybe use that figure to do an HLOOKUP if I flip the ticker and return rows but that has to be a weak way of doing it and the data is significant and dumped to Excel in the same format (ticker in the higher row, return below).

Any suggestions greatly appreciated.

Sheet 1--->A4=1MO, A5=2MO
E2=todays date in a formula (=Today())-->Wednesday, Jan, 21,2009

Sheet3 is a historical sheet that shows values for different past dates.
-->A4=1M0, A5=2MO, B2=Monday, Jan, 19,2009 , G4=5, G5=8.
-->H2=Tuesday, Jan, 20,2009 , columg G4=10, G5=29.

What I need is the following..I am not sure if Index match can do this but here we go.
I want to lookup A4 (sheet 1) in sheet 3.
It should however subtract 1 day from the date in E2(sheet 1).

In this case...since today is wednesday, it should subtract 1 day (Tuesday, Jan, 20,2009) go to sheet 2 and return the value of G4=10.

Is this possible?

This is not a question. It is an answer to when you want VLOOKUP to lookup the next occurrence of a value wihtin a 'table'. Obviously you will need to adapt the method for your own circumstances.