Excel 07 - can't find the right forumla

Andolin

New Member
Joined
Jul 23, 2007
Messages
3
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Gettingbetter

Well-known Member
Joined
Oct 12, 2004
Messages
602
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

New Member
Joined
Jul 23, 2007
Messages
3
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.
sheet1.jpg

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

New Member
Joined
Jul 23, 2007
Messages
3
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Forum statistics

Threads
1,181,728
Messages
5,931,696
Members
436,798
Latest member
spprtpplcm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top