Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 11-11-2014, 10:36 AM
hionman hionman is offline HELP: Return a value with a date that falls between two other dates Windows 7 64bit HELP: Return a value with a date that falls between two other dates Office 2007
Novice
HELP: Return a value with a date that falls between two other dates
 
Join Date: Nov 2014
Posts: 3
hionman is on a distinguished road
Default HELP: Return a value with a date that falls between two other dates

Hi all,

I have been trying to find a resolution to this without getting any where here is my problem.

On Sheet 1:



Column No and Name: A(Job Number) B (Property Code) C (Where I want the Customer Number to be) D (Job Order Date)

On Sheet 2:

Column No. and Name: A (Property Code) B (Tenant Number) C (Customer Start Date) D (Customer Termination Date)


a. I would like column C in Sheet 1 to return the tenant number in (sheet 2, column e) who was the tenant of the property at the time the job number and job order date was carried out.
b. If there is no property number – for it to return no tenant number
c. If a job order was made when there was no tenant in the property (the gap between the termination date and start of another tenants start date) to show no tenant number.
_______________________

As there are a lot of tenants who lived in the same property, we can't do a straight vlookup with property code and tenant number as this usually brings the most recent tenant who is currently living in the property.

Instead since there were alot of tenants living in the same house at various times, we want to know who the correct tenant was at the time when the job order date was made .

_________
The formula I have used is the following:
=IFERROR(INDEX(Details!$B$2:$B$25,IF(SUMPRODUCT(--(Details!$A$2:$A$25=B3),--(Details!$C$2:$C$25<=D3),--(IF(Details!$D$2:$D$25<>"",Details!$D$2:$D$25,TODA Y())>=D3),ROW(Details!$A$2:$A$25)-ROW(Details!$A$2)+1)=0,"",SUMPRODUCT(--(Details!$A$2:$A$25=B3),--(Details!$C$2:$C$25<=D3),--(IF(Details!$D$2:$D$25<>"",Details!$D$2:$D$25,TODA Y())>=D3),ROW(Details!$A$2:$A$25)-ROW(Details!$A$2)+1))),"NO TENANT PRESENT")
This formula is working fine in the example worksheet where this formula was tested. Please see zip file attached.

_____________________________

But when I transfer this formula to my intended spreadsheet it doesn’t work, Instead, it just says NO TENANT PRESENT along the entire column, even though with the data that is there it should be pulling tenant numbers through.
Please see zip file attached for my spreadsheet where formula doesn’t work.
Have I got the formula wrong, do I need to make a slight amend to it, if so can you help or is it a formatting problem with the column.

I have pressed ctrl+shift+enter at the end of the formula for array and pressed enter as well and both give the same result.

Please help I have been working on this for days and I know I am nearly there.

Thanks everyone for taking their time reading this.
Attached Files
File Type: zip My Spreadsheets.zip (730.9 KB, 12 views)
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
HELP: Return a value with a date that falls between two other dates how do you reflect when PV falls below EV ketanco Project 1 10-13-2014 03:30 PM
Date Format: how to output dates that are not US? tinfanide Word VBA 0 10-28-2013 03:30 AM
Default dates for a Date Picker BoringDavid Word VBA 2 09-11-2013 01:42 AM
HELP: Return a value with a date that falls between two other dates MS Project Start Dates not matching the Status Date sjodom Project 3 12-04-2011 08:45 AM
HELP: Return a value with a date that falls between two other dates lookup dates and return corresponding data newton.rogers Excel 7 08-15-2011 11:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft