Microsoft Office Forums Formula with date which need to match & got error message

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-22-2019, 05:59 AM
aleman_li aleman_li is offline Formula with date which need to match & got error message Windows 10 Formula with date which need to match & got error message Office 2016
Novice
Formula with date which need to match & got error message
 
Join Date: Apr 2019
Posts: 2
aleman_li is on a distinguished road
Default Formula with date which need to match & got error message

New beginner.
Version Window 10
Excel 2016 but my colleague Version 2010
I had French version using ( to seperate the formula but my colleague has English version which using (,)
Actual database with 18000 lines on Base file & 200 lines on BAF table.


I have two table which has different range of date, BAF value & the helper column which can find in following table .
The principal & the goal is to match the range of BAF table & give the correct period of information & if it's out of range, got error message.
It can allow duplicate record & can add the data on both BAF & Base file with new information.


Example
Case 1 : Base table : dated 1/3 to 31/3, BAF table has information : 1/3 to 31/3, 1/4 to 30/4, 1/5 to 31/5.
I would like the data only take the information from 1/3 to 31/3. (not 1/4 to 30/4 & 1/5 to 31/5)


Case 2 : Base table : Dated 1/4 to 15/4 & 16/4 to 30/4.
BAF table (same as case 1) : the result need to show between 1/4 to 30/4.


Case 3 : Base table: (a) dated 1/3 to 31/3 or 1/7 to 31/7 which assume BAF table does not have those period which need to show error message : N/A.


(b) dated 1/3 to 1/6 but BAF table has only the rate from 1/3 to 31/5 only, error message : end date larger than validity. pls input correctly.


Case 4 : Base table : dated 1/4 to 31/5, BAF table, same as Case 1.
I would like the result only show Apr BAF table.
Case 5 : Base table: dated 1/4 to 30/4, BAF table, 1/4 to 30/6.
I would like the result can show the BAF value without problem.


Base file table (around 18000 lines)
Start dateEnd dateHelper 1BAF rate1/430/4ABCDE (it will be change accordingly to the combination)2001/531/5ABCDE2501/331/3ABCDE130

BAF file table (around 200 lines)
Start dateEnd dateHelper 1BAF value1/331/3ABCDE130

If you need any further question for set up the formula, I also att'd the file for easy understand.

Tks for your help in advance !


Rgds
Ale
Attached Files
File Type: xlsx formula with date need to match.xlsx (13.3 KB, 6 views)
Reply With Quote
  #2  
Old 04-22-2019, 11:07 PM
ArviLaanemets ArviLaanemets is offline Formula with date which need to match & got error message Windows 8 Formula with date which need to match & got error message Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Either you need some additional conditions to compare with, or you did a mistake in 2nd data row on sheet Base File.

Otherwise the formula in attachment works.

Edit:
To make tables expanding automatically, define both tables as Tables (From menu Insert>Table), and edit formula to use Table syntax, like
Code:
=SUMIFS(tBAF[BAF];tBAF[POL];[@POL];tBAF[POD];[@POD];tBAF[S/L];[@S/L];tBAF[Container];[@Container];tBAF[From];"<=" & [@To];tBAF[To];">=" & [@From])
Edit::
Change parameter delimiters to commas, in case you use e.g. US settings!
Formula assumes, that it is entered into field of Table (e.g. tBase) on sheet BaseFile. When the formula is outside of tBase (but on same row it refers to for tBase values), you have to replace all [@FieldName] in formula with tBase[@FieldName].
I ma not sure how using "/" in field name of Table affects formulas - maybe there is additional pair of square brackets needed.
Attached Files
File Type: xlsx BAF.xlsx (14.1 KB, 1 views)
Reply With Quote
  #3  
Old 04-23-2019, 05:52 AM
aleman_li aleman_li is offline Formula with date which need to match &amp; got error message Windows 10 Formula with date which need to match &amp; got error message Office 2016
Novice
Formula with date which need to match &amp; got error message
 
Join Date: Apr 2019
Posts: 2
aleman_li is on a distinguished road
Default

Dear ArviLannemets


Tks for your formula.

However, it's not exactly not I am looking for.

As those value can not be using Sumifs since I am not asking to get the sum of the month in total.
Those information can be Zero too as there is actual data which the value is Zero. If the date is not match the formula which should show N/A (erreur message)



As per my message that each month had different BAF value.

I need the value which is based on the period of the month in BAF table & match the date that it's on the Base file.

Those explaination is on those Case 1 to Case 5 in my first message.



On the other hand, the actual database of those column which are not adjacent to each other that why I need to use Helper 1 Column since it might have another column information need to be included in it.



Hope you can help to get the other solution it & tks in advance !


Ale

Last edited by aleman_li; 04-23-2019 at 12:37 PM.
Reply With Quote
  #4  
Old 04-23-2019, 02:25 PM
ArviLaanemets ArviLaanemets is offline Formula with date which need to match &amp; got error message Windows 8 Formula with date which need to match &amp; got error message Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

SUMIFs() is not used in my example to calculate sum - it is used to return the sum of entries matching certain conditions. The task is to set those conditions in such way, that ideally the sum of value in single cell is returned - i.e. the value from single cell. This the formula did - except for one row, where the sum of 2 values was returned. I did write about this row, but I could not decide, why several values were found. Were dates in example table for this row wrong, or needed the formula more complex set of conditions - I had not enough information there.

As from your opening post those conditions were not clearly defined, I had to make a guess. I made one which did give best match with numbers you provided.

To get messages when no match was found, you probably have to add some helper columns. E.g. an column where COUNTIFS() function calculates the number of matching rows in BAF table. When the first formula returns 0, and the count is also 0, then there was no match. When the first formula returns 0, and count is >0, then there was a value 0 returned from BAF table.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word Error Message Run time Error 4605 baes10 Word VBA 1 08-30-2018 02:37 PM
Formula with date which need to match &amp; got error message Reserved error(-1517); there is no message for this error ktest Office 2 09-08-2017 05:31 AM
Formula with date which need to match &amp; got error message Automation error Unknown error" message once they open the Excel file hlina Excel 1 10-08-2013 09:14 PM
Macro date and match valor cg1980 Excel Programming 0 04-12-2011 09:38 AM
Formula with date which need to match &amp; got error message Imported message date change to today's date promark Outlook 1 12-23-2005 07:21 AM


All times are GMT -7. The time now is 04:50 AM.


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