Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-07-2022, 11:02 AM
DavidRood DavidRood is offline More formula help Windows 11 More formula help Office 2013
Novice
More formula help
 
Join Date: Sep 2022
Posts: 4
DavidRood is on a distinguished road
Default More formula help

The attached spreadsheet is a sample of what i am trying to do.

The first 3 rows work as required however once i get to the cancelled portion they won't work.

Row 5 and 6 show what I am trying to do but when I combine it with Row two these values won't show up.

So if any of you Excel gurus can help me it would be much appreciated.

thanks
Attached Files
File Type: xlsx Sample Material Control Logbook.xlsx (10.1 KB, 7 views)
Reply With Quote
  #2  
Old 09-08-2022, 01:33 AM
Marcia's Avatar
Marcia Marcia is offline More formula help Windows 11 More formula help Office 2021
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Hi David. I re arranged the sequence of the multiple IFs. I hope I got the formula right as per your requirement.
Code:
'=IF(P6=0,"OUTSTANDING",IF(AND(P6>0,P6<I6,R6="CANCELLED"),"CANCELLED",IF(AND(P6<I6, P6>0,R6=""),"PARTIAL",IF(AND(P6>0,P6<I6,R6,"CANCELLED*"),"PARTIAL ISSUE-CANCELLED",IF(P6=I6,"COMPLETE")))))
Attached Files
File Type: xlsx Sample Material Control Logbook.xlsx (11.0 KB, 3 views)
Reply With Quote
  #3  
Old 09-11-2022, 11:53 PM
ArviLaanemets ArviLaanemets is offline More formula help Windows 8 More formula help Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Another possible formula
Code:
=IF($R2="",IF(SUM($P2)=0,"OUTSTANDING",IF($P2<$I2,"PARTIAL","COMPLETE")),IF($R2="CANCELLED","CANCELLED","PARTIAL ISSUE-CANCELLED"))
The main difference from Marcia's one - it allows canceling entries, where nothing is recieved yet. And the formula assumes, whenever anything is entered into REMARKS column, it means the entry is canceled (maybe different name for this column is a good idea)!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Copy Row 2 Row But Next Column In Formula From Another Tab TimG Excel 3 04-16-2018 09:20 PM
More formula help Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula innkeeper9 Excel 2 09-13-2016 08:59 PM
Need help with dragging a formula and changing a reference column as I drag the formula. LupeB Excel 1 10-22-2015 03:02 PM
More formula help Formula Help ashreporter Excel 3 04-01-2015 10:40 PM
More formula help help with formula? doczilla Excel 2 09-25-2011 04:14 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:15 AM.


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