Auto Increment Specific Cell Value Based On Different But Related Cell Value

Nov 20, 2011

In my excel workbook, I have a customer table and invoice sheet among many others.

As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.

Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.

For example, customer called Bob (Bob-1);
Number of purchases = 1
Customer Type = Single
Discount = Yes

Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:

Number of purchases = 2
Customer Type = Multiple (can be achieved by using IF statement on No. of purchases)
Discount = No

View 1 Replies


Pick Data From A Specific Row/column (eg 10/B) Related To Active Cell

Dec 2, 2009

I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)

what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.

if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!

View 2 Replies View Related

Auto Increment Cell Values Along With Text?

Jan 7, 2014

i have excel sheet with name labels.i want count how many times user repated along with his branch code. acctully am done this using this formula, but not getting what am exepected.

Formula in B1 is ="JSK-SW-1"&(countif($A$2:$A2,$A2))
output is value is incremented, but i want text also be change.
Name Count
Sateesh JSK-SW-1
Rajesh Raj- SE-1
Sateesh JSK-SW-2
Rajesh Raj-SE-2
Anil JAK-DE-1

View 1 Replies View Related

Auto Increment Cell Number Upon Open

Sep 7, 2006

I am trying to get my invoice sheet to automatically increment the invoice number when I open the workbook. I know it is in the code i.e. private sub? but it doesnt seem to work.

View 3 Replies View Related

Auto Increment Based On ID Variable

Oct 3, 2013

I have a following the following dataset:

ID YearWk Sales
1 201310 $1000
1 201311 $1200
1 201312 $1300
1 201313 $1400
2 201310 $2000
2 201311 $2200
2 201312 $2300
2 201313 $2400

I'm trying to figure out how to create a new variable that will increment and reset based on ID.. similar to the FIRST. function in SAS.

Final Dataset:
ID YearWk Sales Count
1 201310 $1000 1
1 201311 $1200 2
1 201312 $1300 3
1 201313 $1400 4
2 201310 $2000 1
2 201311 $2200 2
2 201312 $2300 3
2 201313 $2400 4

View 3 Replies View Related

Rename Tab Based On Cell Value And Increment Invoice Number

May 2, 2012

What I'm trying to do is from a template worksheet that I have in a workbook with other worksheets, I'm trying to come up with code that will create a new worksheet based on the template (copy), increment the invoice number, and rename the worksheet tab to be "Invoice # xxxx" (new invoice number from prev. step.).

I have figured out how to use a button on the template to execute, but as I said my VBA skills are lacking to say the least.

View 9 Replies View Related

Formula Calculation To Be 1 Of 2 Values & Increment Cell Based On Result

May 24, 2008

I'm quite a novice at Excel. I have a column of values that I sum as follows;


0 <----------------sum of A1:A5

A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.

View 7 Replies View Related

Excel 2007 :: Auto-update Date In Specific Cell?

Oct 18, 2011

i want to insert the last date written in a cell into a specific cell automatically,

1- Date column is D containing dates from starting of the month

2- A1 is cell in which last date from column D should automatically be inserted.

automatically copy the last entered date in column D and pastes it to cell A1,

View 4 Replies View Related

VBA Change Cell Value To Specific Letter Based On Value Of Different Cell In Different Worksheet

Jun 6, 2014

I've done the following:

Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Range("C4").Value = NT
End If
End Sub

However, this doesn't even work.

When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...

View 5 Replies View Related

Auto Highlight Cell Based On Other Cell Value?

Dec 6, 2013

I'm having a difficult time making this work properly.

To simplify, I want:

cell A1 to fill with blue when cell G1=0 or greater.
cell A1 to fill with yellow when G1<0

Next step is to apply this to cells A1:A100 based upon cells G1:G100.

View 3 Replies View Related

Auto Name Tab Based On Cell

Oct 5, 2009

I would like the tab at the bottom of the sheet to Automactially name = to cell C5
So if Cell C5 = "Ryan"

The tab at the bottom of the sheet will be named "Ryan"

View 9 Replies View Related

Highlighting Cells Related To A Cell

Jul 14, 2009

in this sheet i have 3 groups of data. group 3 is the sum of group 2 - group 1.

how can i make the cells highlight that relate to the cell sum in group 3.

i.e when i click cell b38 cell b21 and b4 should highlight.

View 9 Replies View Related

Auto Populating Based On Specific Data

Jan 7, 2014

I have a unique task i am trying to achieve (when i say unique i mean i am out of my depth). I am trying to take specific data from a couple of different sheets to populate other sheets whether it be copying the text of fill cell (color)

Excel Automation Test.pdf

I have Attached a PDF detailing.

View 2 Replies View Related

Auto Grouping Based On Cell Value?

Apr 1, 2014

I'm building a sales tracking spreadsheet and need to find a way to auto group certain rows based on their cell value. In my tracker I'd like to group by company name and be able to collapse it to look at the details.

Here's an example of my starting point and how I'd like the data to look like in the end.

Starting State:
Company Name contact name sales stage Forcast amount
Company A david brown contracts 10,000
Company A John Smith Won 20,000
Company A D'Arcy Davis Canceled 10,000
Company B
Company B

Desired State:
Starting State:
Company Name contact name sales stage Forcast amount
+Company A 30,000
+Company B

View 1 Replies View Related

Auto-filter Based On Cell Value

Feb 18, 2014

I have two problems:

1) Eliminating doubles when ranking
2) Auto-filtering based on cell values

The first problem is an issue with my formula I believe (I am using a RANK + COUNTIF-1) formula (Shown in tab #2, column L of the attached document)

The second problem will need a macro, but I am not very strong with VBA. In the attached document, I am looking to be able to filter the table in tab one based on the selections I have included at the top. I was able to filter the table based on # of IDs displayed using a formula. The second filter is looking to only show the values in the table that satisfy the condition that column E must match the filter chosen (cell C3).

For example, of the filter chosen in cell C3 is "Yes", I would like the table to rank and display only those rows with a "Yes" in column E.

Attached: Excel_Help1.xlsx

View 2 Replies View Related

Auto Email Via Outlook Based On Cell Value

May 4, 2013

I need to send an email when value of timer in column F exceeds 72 hrs. E-mail address of recipient is in column C and 2 fixed addresses are to be put in C.C.

basic outline would be

name(A) work(B) E-Mail(C) Work Started (D) Work Finished(E) Time Elapsed for Work from Start (F)

Can this emails be sent to repeat also after 24 hrs if the timer in F is not stopped (i.e till no entry is put into Column E)?

View 2 Replies View Related

Auto Hide Columns Based On Cell Value

Mar 12, 2012

I got an excel, with a "validation list" in a certain cell.

Users can select value1, value2 or value3. based on this value, some columns will have to unhide (standard= hidden)
(only for value2 this is the case).

I used the worksheet_change event to determin the value, but so far I'm only getting it to hide...


Private Sub Worksheet_Change(ByVal Target As Range)
If C24 = "value2" Then
Columns("H:O").Hidden = False
Columns("H:O").Hidden = True
End If
End Sub

View 5 Replies View Related

Auto Hide Rows Based On Cell Value In Same Row

Apr 1, 2013

I am creating a copy of a spreadsheet (table format) in a separate workbook using formulas that will update the copy as the original updates. I would like to auto hide the rows that have the value "Returned" in column G. I have columns A - G , rows 2 - 2000. The value "Returned" is the result of a simple = formula. Is there some way to accomplish this?

View 4 Replies View Related

Auto Sort- Row Refernce Based On Cell

Feb 18, 2009

I am typing a number into Cell B4 (ex. zza4582) this number is a serial number that will be in many differnt cells in column "A" on a different sheet "Template Paste". On each row with that serial number I need the information from "A-I" to be copied in the main sheet starting on row 10 and continuing on from row to row depending on how many times the serial number is found.

View 10 Replies View Related

Auto Hide Rows Based On Cell Above

Jan 30, 2009

I've attached a screenshot to illustate what I am doing and a one tab version of the workbook. I've had to do it in a zip folder as they were too big individually.

I've got a table that starts at row 12 and finishes at row 217 and the users enter information into the rows over a year. The creator of the sheet has set it up so there is a "z" in the second cell of each row and as this cell is overtyped with the new information the row changes colour and is included in the selected print macro that is set up.

I want to add in a macro that changes the row height to 0 based on the "z" being present in the row above 2nd cell. So all that is showing in the table are the rows that have info in them and one blank one underneath. So everytime a new row of info is entered either a new line will reveal itself underneath or there is a control button on the sheet that the user can press to reveal a new empty line.

I don't know how to write VB, but I've found some code online that claims to do what I need, but I need it to be altered to use the presence of the "z" in the row above (2nd column) as the trigger for the rule:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Value = 1 Then

Rows(Target.Row).RowHeight = 0


End If

End Sub

Or should I be starting off with minimised rows and changing it so the height increases as the "z" in the row above is overtyped?

Can this happen automatically as the z is overtyped or does the macro need to be triggered by a control button for example?

Is there a better way to do this? I don't want to get rid of all the extra empty rows and have a macro to create a new row for 2 reasons: 1.They have formulas and macros running set up by the creator that I don't want to mess with and 2. There are 52 sheets in the workbook, 1 for each week of the year and the next sheet takes the information from the previous weeks sheet so on the last sheet, number 52, it has every line that has been entered over the year from week one to week 51 carried over. If I created a new row on week2, I would have to then create that row on every sheet following week 2 and I think that would make it more complicated. I would need the macro to be able to run on any of the 52 sheets.

Private Sub Worksheet_Change(ByVal Target As Range)

Cells.RowHeight = 12.27

Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0

End Sub

View 9 Replies View Related

Auto Complete Cell Based On 1st Letter

Jul 1, 2008

I would like to have excel automatically fill an excel cell with a predefined list. For example, if you put the letter l it will automatically include the word large. This is like you do when you fill out a form online. See attachment

View 2 Replies View Related

Increment Cell Location Up By One Cell For A Column

May 21, 2009

Increment all cells located in Column H up by one (the one above it) cell Except for the header cell (since its the header)(H1),

H2 will always be a blank due to the make up of my database so that will not cause the header to be re-written.

Column H is not an entirely filled column and some cells are empty (if this info was needed in optimizing the code),

Im seeking the quickest and easiest way of implementing this since the database is very large.

View 2 Replies View Related

Increment Number From Cell Value Until Another Cell Value Reached

Dec 7, 2012

We need to reach a cell value by using increment of another cell value. But ideally the results will all be in one cell.

Now to make it a little more advance there could be an overhang of the board in A3 which would need to be deducted from the first increment.

Example :

A1 = 5193mm (Lenght of beam)
A2 = 600mm (Width of board)
A3 = 200 (Overhang)
A4 = 400 - 1200 - 1800 - 2200 - 2800 - 3400 - 4000 - 4600 (End Result, The formula wont do the next sum as it exceeds A1)

Of course the vaules of A1 and A2 will vary but that shouldnt make any difference to the formula.

View 9 Replies View Related

Filtered Listbox Selection To Return Value To Related Cell?

Apr 30, 2014

See the attached : ToolMatrix4.xlsm

Combobox2 filters listbox2 which is pulled from sheet2. How do I get the X to return the value to the relevant cell on sheet 2, ie create a relevant listindex for the listbox

Also, one other minor thing, why selecting the last record in the listbox doesnt write to the worksheet?

View 4 Replies View Related

Put Message Box Related To A Cell If Threshold Values Reached

Mar 25, 2012

I am trying to put a message box related to a cell if a threshold values is reached.

E.g.: Cell A: 85

If cell A values moves to 86 I am trying to put in a message that says something like " This value is not allowed". Is there a way to do this ?

View 8 Replies View Related

Auto Creating A Calendar Based On A Date In A Cell.

Dec 30, 2009

I am playing around with creating a calendar based on a date (month) I enter into a cell. I am doing this with a simple =date+1.

I have a sample attached. Only problem I am having is that for months with 30 days I don't want the last day to show the first of the next month. But I can't simply remove that as if I entered a month with 31 days I would need it to show the 31st.

View 4 Replies View Related

Auto Fill A Cell With A Value Based On Pre-defined Range

Mar 19, 2013

A have three columns with "Kilograms range" and corresponding value.

A========== B========== C
KG From====-To======= Predefined Value

what formula will fill up the cell with the predefined value automatically based on the range, after a kilograms are entered in a different cell as explained in the attachment.

View 2 Replies View Related

Auto Update Of Values From Other Sheet Based On Changes In One Cell

Jul 24, 2013

i want to update values fon an excel sheet on a month by month basis. So i have jan to dec folders and excel sheets under each folder , i want to update my monthly forecast sheet based on the numbers in the folder I want to just change the cell in my monthly forecast sheet to Jan or Feb and the values should be pulled from the closed excel files in the monthly folders. I tried concatenation but it gave me #REF .. Is there a way to do it other than using "pull" ??

View 1 Replies View Related

Auto Populate A Third Cell - Based On Data From Two Columns

Mar 24, 2013

I have data in three columns

4.5 9.50 4.657
4.5 11.60 4.789
4.5 12.50 4.654
4.5 13.50 3.930
4.5 15.20 3.826

I need to be able to auto-populate the cell in column G based on values in columns A & B. If I type 4.5 in E2, and 13.50 in F2, G2 should auto-populate with the value from column C

4.5 13.50

View 2 Replies View Related

Auto Print Based On A Cell Value For Multiple Works

Jun 10, 2009

There are 20 worksheets in my workbook. For the first ten, I would like to print out the only sheets in which cell E28 = 0. For the last ten, I would like to print out the sheets in which cell T1 = 0. Each sheet has a unique name.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved