Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Formula to check previous row results before displaying result

Posted on 2016-11-16
15
37 Views
Last Modified: 2016-11-17
HI Experts

I hope you can help

I have a sheet that is completed by various personnel to detail packing requirements for shipments including weights, dimensions and stock information.

Basically, I would like to know if there is  a way in which a formula can look at the carton number in column A and if it is the same as the next line entry, then the carton dimensions are not repeated until the carton number changes.

Ive tried this formula:-

=IF(A3<>A2,g2,IF(A3<>SUM(A2+1),G2,""))

But it gives me the same result ??

I have attached an example, if you could help I would be very grateful

J
Sheet_Example.xlsx
0
Comment
Question by:spicecave
  • 6
  • 5
  • 4
15 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41889640
HI,

How do you get

24x24x25
is it a formula?

Regards
0
 

Author Comment

by:spicecave
ID: 41889656
HI RG

Apologies

No this is just typed in its the height, width and depth of a packaging cardboard box that we send goods in.

What I need is for the cell below the first entry to look to see if the carton number is the same in column A and if it is, then return a blank. If it isn't (ie Carton 2) then it will allow the new carton dimensions to be visible.

This formula will be entered AFTER all the details are in unless there is a macro that can be run so that after the first entry and subsequent entry in row 3, it will look at the carton number in A3 and if its the same as A2, blocks the cells in G3 so the user cannot duplicate the carton dimensions until the carton number changes. Is this possible?

J
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41889667
if cell G3 does not have the information in the first place why create a formula?
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:spicecave
ID: 41889680
Hi RG

Apologies

I believe the user has misled me into what is requested

What is actually requested is, for example, after entering the data in row 2, a macro looks at cell A3 and if it is the same as A2 (eg 1) then cell G2 is locked. If the user enters a number that is different from A2 (ie 2) then G2 is unlocked and is enabled for cell entry (eg carton dimensions)

Is this possible?

J
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41889695
then try
Sub macro()
ActiveSheet.Unprotect
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Rng.Offset(, 6).Locked = False
For Each c In Rng
    If c.Value = c.Offset(-1).Value Then c.Offset(, 6).Locked = True
Next
ActiveSheet.Protect
End Sub

Open in new window

0
 

Author Comment

by:spicecave
ID: 41889733
HI RG

This is great except when I enter the macro and then try to enter a new row of data in row 12, it comes up with the 'please unprotect sheet' alert.

Is there a way to isolate the locked cells to JUST column G as when the macro is run, the user will still need to enter data however, not in Column G if the carton numbers are duplicated as explained above ??

J
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41889758
then try
Sub macro()
ActiveSheet.Unprotect
Cells.Locked = False
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Each c In Rng
    If c.Value = c.Offset(-1).Value Then c.Offset(, 6).Locked = True
Next
ActiveSheet.Protect
End Sub

Open in new window

0
 

Author Comment

by:spicecave
ID: 41889790
HI RG

Thanks for the response

So Ive entered the code and it will now let me enter data into the next row (row 12) however, if I enter 4 (carton 4) in column A12 and some data through to Column G12. If I enter data into the next row (A13) as carton 4 (duplicating the previous entry) it still allows me to enter freely into Column G (G13) instead of the macro seeing that the previous entry was also carton 4 and therefore, G13 should be blocked.

Also, when I run this macro and try typing into Column G cells from G2 to G11, the locked doesn't work ??

Any ideas ? Thank you for your help by the way I really appreciate it.

J
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41890091
then you have to use in the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect
Cells.Locked = False
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
For Each c In Rng
    If c.Value = c.Offset(-1).Value Then c.Offset(, 6).Locked = True
Next
Me.Protect
End Sub

Open in new window

0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41891093
You could probably do it with Data Validation rather than VBA.

Select G2 to G11 and use the following to set a Custom rule in the Data Validation:

=IF($A2=$A1,AND(G2<0,G2>0),LEN(G2)>5)

This says if the value in A2 matches the value in A1 the entry in G2 has to be greater than AND less than zero which is impossible so no entry can be made, otherwise it has to be greater than 5 characters "HxWxD".

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41891096
Alternatively, do you have a list of Carton codes with their respective dimensions. If so, use a VLOOKUP to get the dimensions rather than entering them:

=IF(A2=A1,"",VLOOKUP(A2,DimensionTable,2,False))

Thanks
Rob
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41891105
See attached with both examples on separate sheets.
Sheet_Example.xlsx
0
 

Author Comment

by:spicecave
ID: 41891258
HI Rob

Your Data Validation option is perfect ! Thank you so much

One question for the purpose of limiting the input further, is it possible to remove the Retry option on the Warning Alert in a Data Validation pop-up?

J
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41891399
Unfortunately, I don't know of a way of doing that. There are 3 options for what error message comes up and the other two allow the user to continue with an invalid entry.

Also, one disadvantage of Data Validation, it doesn't stop the user from just copying and pasting from another cell or using the mouse to drag fill cells. It only stops physical entries.
0
 

Author Closing Comment

by:spicecave
ID: 41891495
Thanks guys for the help

Appreciated as always !!

J ;o))
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question