Manipulating Cell Data When Entered?

Jan 31, 2014

I'm putting together a workbook to keep track of income generated from Contractors (I work in recruitment). I have an existing system that displays margin, hours worked and income (margin * hours) but what I am looking for is a neater solution that would still have the margin column, but when hours are entered into a cell it is factored by the margin and displays income in the same cell.

So to be sure I am describing correctly, I am after a formula that will enable me to

1. Enter a value into a cell (for the hours worked - lets call this B1)
2. Multiplies this value by another cell (a fixed amount, the margin - lets call this A1)
3. Displays this result (B1 * A1) in cell B1, the same cell as the hours were entered in.

View 2 Replies


ADVERTISEMENT

Manipulating Data

Nov 24, 2008

I'm looking for a formula or code that would take a client's name that appears in one cell, with the last name first then a comma and then the first name, and paste it into another cell with the first name first and then the last name with no comma.

Example: cell A2 contains "Smith, Joseph", cell B2 should have "Joseph Smith". If I need to provide additional information,

View 9 Replies View Related

Manipulating Data Within A Loop

Jan 25, 2007

I am working on a macro that opens up a workbook, formats the data by moving columns etc and then spits out the data to different workbooks according to a string in col R (M,C,S etc). I am able to split the file into the workbooks, but it copies the whole sheet and not just the visible cells (its working via a filter). I have tried adding code into the loop to basically copy the visible cells to Sheet2 and then delete (or clear) sheet1 so that I only have the relevant data but have very interesting. This is only the first of a few procedures I would like to add within the loop. Ideally I want to have a prompt for the filename and path for each file, and also another procedure to add a number into each seperate file. If I know why I cant get this simple function to run I will, hopefully, be able to add in the other bits!

Function SplitSheets()
On Error Resume Next
Dim ws As Worksheet, source As Range, dest As Range
Dim daily As Worksheet
Dim sType(4) As String
Dim i As Integer
Dim strSaveFileas As String
sType(1) = "M"
sType(2) = "S"
sType(3) = "C"
sType(4) = "N"
Set daily = Worksheets(1)
For i = 1 To 4...................

View 2 Replies View Related

Pulling And Manipulating Data In Different Areas

Mar 24, 2009

i need to pull data from one spread sheet and place it in a new spread sheet. and i am not that familiar with macros i am learning but i need to get this done.

View 13 Replies View Related

Manipulating Chart Source Data With VBA

Aug 8, 2007

I have a chart that I am trying to set up with dynamic data. I have the chart created, but the problem is that I will have a variable number of categories based on the data that is loaded, so I need to use VBA to change the range for the source data and labels on the fly (otherwise, I will end up with 20-50 categories that are all zeroes, putting a bunch of gobbledegook at the top of the chart).

What commands to I need to enter into VBA to get this to work?

I have put a formula in 2 cells to determine based on the current data what the source data values range should be, and what the category labels range should be.

The cell that holds the value range is Graphics!L6, and the cell that holds the labels range is Graphics!L7.

The current values that these cells hold are:

L6:
=Graphics!$J6:$J9
L7:
=Graphics!$K6:$K9
Oh, and the name of the chart is "Chart 1"

What do I need to put into VBA to get the chart to change the data source values and category labels to the ranges I have listed in those 2 cells? (and for that matter, have I listed the ranges correctly in those 2 cells so that they are usable?)

If possible, I would prefer to do this without having to use code to select the chart, because I want the updates to the chart to be invisible to the user.

View 9 Replies View Related

Entering, Formatting & Manipulating Data Using Macros

Apr 11, 2008

I would like to give credit and thanks to StephenR for producing the current Macros in use on this spreadsheet.

This was to sort out Raw Data that I get from a database, and with lots of help sort it and format so for a history of this you can look there.

Now I need to enter in some formulas, data from another spreadsheet and cell formatting…

Unsure if all this is possible but am hoping some of it is…

First Thing
The column Run Time has the data in them already this is the time in minutes am looking for a way to put a formula in, that will make them into hours so the formula will read like this “=180/60” this will give the number 3 as the whole number.

Second Thing
Using the whole number that the formula made in the Run Time column, am looking to fill cells with colour, too see what I mean here its best to take a look at my mock up spreadsheet.

Third Thing
Two rows below all the data that gets sorted out I would like to put in two totals, one for Run Time and another for Available Hrs “=SUM (Range)”

Forth Thing
I have data in another spreadsheet that is the Resrce Name, and Available hrs am looking to take that data from this spreadsheet that will be closed opened if needed, this data will assign workers to Work Centres.
I would like this to be a separate macro to run though all the sheets if possible.

View 14 Replies View Related

Locking Cell After Data Entered?

Jan 27, 2013

I have a command button [Start] that when clicked, will enter the time the process started. I then wish to have the cell locked so the time cannot be changed after it has been entered. I tried this code:

Private Sub CommandButton7_Click()
Range("I5").Select
ActiveCell.FormulaR1C1 = Now()
Cell.Locked = True
End Sub

But am getting an error for the "cell.locked = true"

Any proper code to lock the cell after the data has been entered?

View 4 Replies View Related

Fill Cell With Next Consecutive Number If Another Cell Has Data Entered?

Aug 16, 2012

I need to achieve the situation below with a formula. I have tried simply doing, for example, = B3+1 but this throws an error, probably because the value being checked is alphanumeric.

Data
A001

Data
A002

Add next number (A003) when data entered in cell to left

View 2 Replies View Related

Locking Cell Dependent On Data Entered

Oct 18, 2009

I'm working in MS Office Excel 2003. I have a spreadsheet with numerous drop down boxes and references to named ranges. Here's my current quandary:

In cell I12, I have a drop down box with listed choices A, B, C and D. In cells I15, I18, I21 and I24, I also have drop downs.

What I'm trying to do is when choice D is picked from I12 it locks cells I15, I18, I21 and I24 so that the choices in the drop down cannot be picked. Also, a popup message is displayed.

I was able to receive help in getting this pretty much taken care of. But the problem I'm having with the code is that the popup message is also being displayed whenever I make a choice from other drop down boxes. So if I choose option D from I12, it locks the other 4 cells and displays the message just fine, but then if I choose anything else from say K12 or L21 then the message continues to come up. Here's what I have...

View 5 Replies View Related

Locking Cell Dependent On Data Entered..

Oct 10, 2009

In cell D10, there is a drop down menu with choices A, B, C and D. Cells D13, 16, 19 and 22 also have a drop down menu. What I would like the code to do, is when option D is chosen from D10's menu, cells D13, 16, 19 and 22 are locked so that the user cannot choose an option from any of those cells.

So basically, if D10="OptionD" then [whatever the value is to lock cells D13, 16, 19 and 22 from drop down use)

If possible, I'd like this as an IF function, and not a VBA macro. I'm not completely familiar with all of the IF functions...um...functionalities, so forgive me if I ask the impossible. But if the IF cannot do it, VBA code is fine.

View 9 Replies View Related

Lock Cell After Data Entered And Have Moved Off That Cell

Jan 5, 2013

Currently have a column of cells that after the data has been entered we would like the cell to lock to prevent changes. The sheet is currently protected with the cells in question unlocked (D173:D250). We would like to be able to change the data if we unprotect the sheet. We would also like to be able to add/delete cells (list) in the code as needed.

View 9 Replies View Related

How To Allow Both Picklist Items And Manually Entered Data In A Cell

May 27, 2014

I have been using Excel for several years and I'm fairly proficient at its basic functionality. I've recently learned and made extensive use of pivot tables. However, I have not taken advantage of such features as Data Validation, VBA, etc.

View 3 Replies View Related

Make Formula Cell Appear Empty Until Data Is Entered

Jun 10, 2006

I'm creating a "universal-fluctuating" vendor inventory return worksheet for a auto parts store that consist of one criteria (cores, warranties, or N/R ) and will return one or two results of core cost and/or unit cost. This part of the task I have accomplished by using a drop down list for my criteria and my results will appear in two different columns using a Vlookup table. The problem is due to inventory fluctuating from cores and waranties on a month by month basis, vendor requiremnts differ for the number of units returned, and last make the boss happy on ink and papers supplies :D I was wondering if it is possible loop my code in a given column where it will move my code to the next row untill I reach a grand total?

View 8 Replies View Related

VB To Activate List Drop Down Boxes When Data Is Entered In A Cell

May 20, 2009

I have a Validation drop down box (column B) and a defined range drop down box (column E linked to the Totals tab with A2-A31 defined as Products) that I need to activate everytime data is added to a new line in column 'A' and cells in column 'B' or 'E' are selected. Additionally, they need to activate if the cells in column 'B' or 'E' are selected for any of the old data so changes can be made. This will help reduce the overall file size and calculation time for a year's worth of data.

There is already some code on the worksheet that looks like it can be adapted if someone knows how to code in the drop down boxes.

View 14 Replies View Related

Macro Create A Hyper Link Once Data Is Entered Into Specific Cell

Aug 17, 2009

Is it possible to have a macro create a hyper link once data is entered into specific cell, i have a multi sheet work book used as a stock card system.

Each stock item has its own worksheet and the product code is entered in to cell E3
(which in turn has a macro in it to change the sheet name to match the product code).

the macro also inserts a row of info into row 5 of another sheet used as a summary sheet ( CONTROL.SHEET or SHEET 4) using copy and paste link.

i need a macro to generate a hyperlink in cell D4 of the control sheet between the row info and the matching product code sheet ( the product code is displayed in cell E4 of the control sheet.

I only input one item at a time and then manually cut and insert the row info into the stock list on control sheet. so every time i input a new item, the row info is always in row 5 of the control sheet.

View 9 Replies View Related

Running Totals In The Cell As The Previous Week And So On Until New Data Is Entered And Updates The Total

Oct 2, 2009

I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesn’t seem to work? (Have thought about threats of violence but would probably lose my job) I’m if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.

View 4 Replies View Related

Add A Date Time Stamp In A Cell After Data Is Entered In A Different Cell And Date

Apr 18, 2014

I am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?

View 9 Replies View Related

Copy Row From Sheet 1 To Sheet 2 When Data Entered In Cell F

Jul 9, 2009

I have Worksheet 1, with columns A to E. I would like a row to be copied to Worksheet 2, as soon as cell F in Worksheet 1 is populated. Also the row to be deleted from Worksheet 1.

So, as soon as F1 in worksheet 1 is populated and enter button pressed, row A1:F1 will be copied to the next empty row in worksheet 2, while being deleted from worksheet 1. So eventually all rows in worksheet 1 will be deleted and rows in worksheet 2 will be populated.

View 2 Replies View Related

Manipulating Sheet

Apr 17, 2008

I am working with excel sheets for dimension population of metrics.

I want to make some process less manual. In one column I am given some values like ((1110 + 1230 + 3120 + SERVSELCT) - (1110.21 + 2110.30))
Based on these values I have to write the expanded form like:

The values that do not include the decimals are known as parents, for which I have to write PCYDYNNOTRANS_1110 and for the values that include a decimal I have to write CCYDYNNOTRANS_1110.21
I have to write this in the adjacent column for all the values I had mentioned above.

I was wondering if there is a way that I can make this less tedious, and I can have a list that would pop up in the adjacent column where I could select which one I should use i.e. either PCYDYNNOTRANS_ or CCYDYNNOTRANS_ and I would have to just copy paste the values.

View 9 Replies View Related

Macro - Sorting Data To Other Worksheets / Update As New Data Entered

Jan 30, 2014

I am trying to create a "Master Sheet" where I enter in the column data and after I have entered my data for each row, I can select the button which toggles the macro to run. I have it built to build new sheets as new clients are obtained. My problem is after I have a sheet that has client's data I cannot get new data to add itself below the data that is already there. I want each client's sheet to keep adding rows as more data comes in. My current macro is :

[Code] .....

Attached File : Data Entry Macro.xlsx

View 3 Replies View Related

Printing & Manipulating Columns

Jan 9, 2009

I have a spreadsheet list in the following format (Note Tax ID would be cell
A1, etc):
A B C
Tax ID Numbers Street
11-03-3040-0178-01-794HIGMAN PARK
11-03-2310-0001-00-3100HELMAR
11-03-3040-0169-00-0102HIGMAN PARK
11-03-3040-0165-00-4104HIGMAN PARK
11-03-0020-0039-01-6104S CRYSTAL
11-03-0021-0006-01-9105S EUCLID
11-03-0024-0004-08-8105PAUL
11-03-0018-0029-00-1105HIGMAN PARK
11-03-1880-0069-00-4107S CRYSTAL

This continues downward to cells 6577. I'm looking for a way to carry these three columns over at some point so I can put more data on a single page, thus minimizing the # of pages I will need to print, but yet remain in the proper sequence as established in column B.

View 2 Replies View Related

Adding To/Manipulating 'if Criteria'

Mar 26, 2009

=IF(AND(B3:ZZ3="Jan",B10:ZZ10>=500000,B13:ZZ13="POST"),"1","0")

And I'm pulling from a spreadsheet that looks like this:

Jan[B3], Jan[C3], Jan[D3]
500,001[B4], 632,000[C4], 992,000[D4]
PRE[B5], POST[C5], POST[D5]

(and the function is calculating how many januarys, greater than 500,000 that are also post).. how do i get it to ADD the amount of fields that meet that crieteria instead of giving me a 1? I want it to say "2" (adding together all that meet those needs)

View 4 Replies View Related

Manipulating Text Strings

Jan 12, 2010

I have a column filled with names per row. I wanted to standardize it by using the following format.

The comma after the last name is always an indicator to me that it is the last name.

<First Name> <Middle Initial(if any)> <Last Name>

My actual records contains the following:

BIMBO EPPING - OK
AHMAD SADICK - OK
Salana, Laura R - need to be arranged to > Laura R Salana
Cantos, Rhoda Grace J - need to be arranged to > Rhoda Grace J Cantos

View 13 Replies View Related

Manipulating InputBox Variable

Oct 20, 2009

I have two inputboxes. I want to set the default value of the second inputbox as (effectively) one cell down from the first inputbox entry. In other words, if I type G17 into the first inputbox, I want the second inputbox to then have a default value of G18.

View 5 Replies View Related

Copying And Manipulating Formula?

Oct 15, 2012

I am unsuccessfully trying to manipulate and copy a formula.

Column A would typically be used as a benchmark for one retailer and have the base price with the other columns simply showing differences in margin etc if the price was changed. This would typically mean the BWP and fixed terms etc would be constant.

However in this circumstance I need to ascertain for different retailers a,b,c,d, etc the shelf price and compare their NTS and % Margin. Whilst I can enter the different prices and change the terms as required, the whole sheet seems to link back to column A rather than simply drawing from the info in each respective column.

a
b
c

Price On Shelf
20.12
20.05
18.87

Shelf Price Excl. GST
17.50
17.43
16.41

[code]....

View 1 Replies View Related

Manipulating Value On Userfrom At Runtime

Sep 30, 2013

I have a userform that accepts some information and checks to see if the values have already been used (address and work order number) If the value is used the user must change the value ,an input box is triggered to get the new value. My question is how do I transfer the value back to the userform??

View 9 Replies View Related

Manipulating Pivot Table Via VBA

Jun 4, 2008

I have 3 pivot tables I'm using a macro to get a value out of, by changing the 2 "Page" fields. However, after the macro's done running, the page fields are messed up. (The order changes, one of the options becomes "").

View 9 Replies View Related

Manipulating File Name Strings

Nov 23, 2009

I have a filename in the format XXX-YYY-ZZZ.xls, where all the XXX and YYY and ZZZ are of variable length. Sometimes it is of the format UUU-XXX-YYY-ZZZZ.xls

I wish to delete all the characters to the left of the rightmost "-" character, including that "-" character.

View 9 Replies View Related

Manipulating Links And Formulas

Jun 12, 2006

Is it possible to modify a link so that you can change the link easily by the value of a certain cell.

='[Labour Plan Week 24.xls]Hours'!$E$135 and I want something like ='[Labour Plan Week " &H5& " .xls]Hours'!$E$135. Where H5 contains the week number

View 3 Replies View Related

Manipulating A Firebird Application Using VBA

Aug 7, 2006

Theres a progam created using Firebird as described here: {url}

I want to be able to enter data into the firebird application from an Office application (e.g. Excel) using VBA.

I want to also be able to extract data from the application using VBA.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved