Excel 07 - can't find the right forumla

[Andolin]

First post here from an Excel noob.

I have tables on two sheets that contain the same type of data within each sheet's respective columns. Column B (or 2 as it appears in '07) contains tracking numbers on both sheets. The 7th column of each sheet contains a date with the 1st sheet having a date of receipt & the 2nd the date of shipment.

My need is for Excel to provide the time between (DAYS360 I assume) each row's dates where like tracking numbers from Column B are found.

The trouble for me is the tracking numbers do not appear in the same rows on both sheets, and in some cases a tracking number will appear on only one of the sheets.

I think I need to LOOKUP for Column B, but I don't know how to write the formula that tells the program to DAYS360 the other columns IF matching tracking numbers are found on both sheets.

Sorry for what is probably a pretty basic question, but I'm lost in Excel.

 

[Gettingbetter]

Hello Andolin,

Welcome to the board,

First of all if you want your columns to show as letters instead of numbers (which I think most excel users use, I may be wrong).
Click Tools (from the toolbar), Options
Click on the General Tab,
Untick R1C1 reference style
Click OK

Secondly here is the formula you are after,

=DAYS360(C3,VLOOKUP(B3,Sheet2!B2:H500,8,FALSE))

C3 is the date in your first sheet, change Sheet2 to the name of your second sheet, and change B2:H500 to your range. the 8 represents which column you wish the vlookup to return.

Cheers
GB

 

[Andolin]

Thank you for your help!

I changed the options as you suggested, but my attempt with the formula didn't meet with the same success.

Where have I gone wrong?

In your formula =DAYS360(C3,VLOOKUP(B3,Sheet2!B2:H500,8,FALSE)) I wasn't quite certain about what "8" represented, but my translation of the formula probably has other issues.

Update...
OK, here's a snapshot of what I'm working with now. I ditched the macro tables since they weren't making life any easier.

Sheet 1 and sheet 2 have the same columns.
Here's the formula I'm trying: =DAYS360(G2,VLOOKUP(B2,Sheet2!B2:G299,8,FALSE))

Excel gives me the #REF error, but I can't solve the problem using it's corrective steps.

Any suggestions?

Thanks in advance.

 

[Andolin]

OK - I now understand (I think) what the 8 in the formula represents, and so for my spreadsheet I believe it should be a 2 as that's the tracking number field.

=DAYS360(G2,VLOOKUP(B2,Sheet2!B2:G303,2,FALSE))

With this formula used against the spreadsheet pictured above I'm actually getting a value instead of an error. Trouble is the value is this "-38065" which is obviously wrong.

I'm thinking it's calculating backwards, but I reversed the formula and only got a similar outcome.

Any help is much appreciated.

 

[barry houdini]

Hello Andolin the 2 in this formula

=VLOOKUP(B2,Sheet2!B2:G303,2,FALSE)

is the column index, B2 will be looked up in the 1st column of your lookup range, in this case sheet2!B2:B303 and the 2 gives the column to return a value from so with 2 you will get the value returned from column C. If you want column G it should be a 6 (as G is the 6th column of your lookup range).

Also if you just want the difference between the dates you don't need DAYS360, just subrtract one date from the other so that would make your formula

=G2-VLOOKUP(B2,Sheet2!B$2:G$303,6,0)

format result cell as general