willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 915
- Office Version
- 365
- Platform
- Windows
I will be building a simple Macro in an excel workbook that the user can open and run.
The user will have to have the first Workbook open when they run the macro so I can reference it as active workbook (for this example I will call it Workbook1, this workbook name can change) .
What the Macro will do is put a formula in column E workbook1.
It is the formula (in yellow area) that I need assistance with...for now.
What I need the formula to do is if A2,B2,C2 and D2 (on Active workbook1) all match I:J (on the last sheet of Workbook:Database),
leave cell blank. If they do not match, the result would be the word "flag"
A4&"|"&B4&"|"&C4&"|"&D4 MATCH I:I&"|"&J:J&"|"&K:K&"|"&L:L
I am also curious if the formula can reference the last sheet in a workbook without knowing the sheet name?
as the formula in Workbook 1 will be referencing the last created sheet in workbook: Database
*if not I will need to consider a work around for this, a code that determines the last sheet name and references it to a cell and then have that formula reference that cell??
Additional Question Relating to the above:
Note as per the picture:
The first row the status changed
the second row, nothing changed
The third row, the quantity changed
The 4th row, was new data entirely
Now if the first question wasn’t complicated enough: could we go one step further and add IF conditions?
Instead of just flagging the changes with the word "flag" could it be modified to display text as follows?:
If change is between column A and I: "Date Change"
If change is between column B and J: "Qty Change"
If change is between column C and K: "Status Change"
If change is between column D and L: "Part # Change"
If change is multiple: "multiple"
If this is too complicated then just the match between 2 workbooks with the word "flag" is fine and I will re-post the 2nd question.
Thank you to anyone who can help. I know how to do an index match with multiple criteria however this is a bit more involved.
The user will have to have the first Workbook open when they run the macro so I can reference it as active workbook (for this example I will call it Workbook1, this workbook name can change) .
What the Macro will do is put a formula in column E workbook1.
It is the formula (in yellow area) that I need assistance with...for now.
What I need the formula to do is if A2,B2,C2 and D2 (on Active workbook1) all match I:J (on the last sheet of Workbook:Database),
leave cell blank. If they do not match, the result would be the word "flag"
A4&"|"&B4&"|"&C4&"|"&D4 MATCH I:I&"|"&J:J&"|"&K:K&"|"&L:L
I am also curious if the formula can reference the last sheet in a workbook without knowing the sheet name?
as the formula in Workbook 1 will be referencing the last created sheet in workbook: Database
*if not I will need to consider a work around for this, a code that determines the last sheet name and references it to a cell and then have that formula reference that cell??
Additional Question Relating to the above:
Note as per the picture:
The first row the status changed
the second row, nothing changed
The third row, the quantity changed
The 4th row, was new data entirely
Now if the first question wasn’t complicated enough: could we go one step further and add IF conditions?
Instead of just flagging the changes with the word "flag" could it be modified to display text as follows?:
If change is between column A and I: "Date Change"
If change is between column B and J: "Qty Change"
If change is between column C and K: "Status Change"
If change is between column D and L: "Part # Change"
If change is multiple: "multiple"
If this is too complicated then just the match between 2 workbooks with the word "flag" is fine and I will re-post the 2nd question.
Thank you to anyone who can help. I know how to do an index match with multiple criteria however this is a bit more involved.