I'm looking to set up a table in Excel (or possibly Access that is then referenced through Excel). But end goal would be to be able to type in an Item Number and then Excel automatically fills in the Description, weight, price and other information about that item number. That way, I don't have to type in "11108.1" and "Computer Mouse" and "$15.98". I want to only type in "11108.1" and it knows to fill in the other information. Is this possible in Excel? I've searched through many functions and haven't found it so I'm thinking it may require a combination of formulas.
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
| ID Y | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria: >= First Day of the Month <= Last Day of the Month = ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
What I would like to be able to do is highlight the four 50's and fill down, but I would like for it to increment and paste the next four filled like this:
51 51 51 51
Then below that the next four filled like this:
52 52 52 52
If its a macro that'll be required I'd like to to be able to change the number of rows to be repeated (4 in this case, but could change).
I have a workbook that has a few data sheets and a summary sheet. On this summary sheet I have to grab data from the data sheets which is in the same row number but different column (always 5 to the right of the previous one).
Eg: Person's Name is in Row 10, Col E (E10), the next name is in J10, then O10, etc. This carries on past Column Z. For each person there is a bunch of other data which is in the same format (set row but 5 over for column).
Since row stays the same i thought i could absolute reference that and then leave column as relative and drag down. This works but only if I drag to the right (across columns) and only if i need to increment by 1.
I have been frustrated trying figure out what I would assume is actually a pretty simple solution. All I am trying to do is grab a set of cells and repeat them down the column a set number of times. The 2 forms of this are discribed below.
A: I have a set of numbers from A1 to A51 I want to repeate those numbers down the columns 84 times.
B: In addition to this I need to have the cells from B1 to B51 Repeat 84 times but with an increment of 200 each time. So I would have say 1 in B1 - B51 and in B52 - B102 it would turn into 201 and so on and so forth.
Line (A1) asdfasdf Line (A2) adsfadsf Line (A3) asdfasdf
What I would like to be able to do is highlight the three columns and fill down, but I would like for it to increment and paste the next three lines filled like this:
Line (A2) asdfasdf Line (A3) adsfadsf Line (A4) asdfasdf
Then below that the next three lines filled like this:
Line (A3) asdfasdf Line (A4) adsfadsf Line (A5) asdfasdf
The (A1, A2, etc.) are cell ranges and already contain the concatenate function that pulls data from a different location. The problem I am having is when I
highlight and fill down, it doesn't increment the cell by +1 (Ex. A1 beomes A2) but instead by +3 (A1 becomes A4) the number of lines highlighted.
I wrote my program with several user forms and private subs. All the results was going to my Report sheet. However, now, I want to be able to run those userforms in another sheet. All the results are coming up in this selected sheet. Is there a way that I can program to for all the results to go to the report sheet without going back and fixing every private sub?
If I enter 1 in a cell, 1.1 below, select both and drag down, I should get a vector: 1, 1.1, 1.2 etc.
However, in the most recent instance of seeing this problem, at 6.5 I get 6.50000000000001! 6.6 onwards is then correct, at 7.2 the value is again incorrect in the 14th decimal place, and the errors continue intermittently through the sequence.
Is there a fix for this? It's extremely time consuming to have to check each auto-incremented array like this.
There is likely an easy fix for this, but I have an auto increment with the following code and would like the letter R to precede the auto increment number everytime.
My current code is this:
Private Sub Workbook_Open() Sheets("Receipt").Range("F11").Value = Sheets("Receipt").Range("F11").Value + 1 End Sub
can i link a spin button to a toggle button such that when i click my togglebutton On then the Spin Button causes the Value in a cell to increment from 0.1.2.3.4.5 and when i click the toggle button off then value decrements from 5.4.3.2.1.0
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. A B Name Count Sateesh JSK-SW-1 Rajesh Raj- SE-1 Sateesh JSK-SW-2 Rajesh Raj-SE-2 Anil JAK-DE-1
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.
Is there anyway to automatically fill the empty added cell after inserting a row without using the fill handle? For example, for a series of numbers: [URL]
2. Drag the fill handle Selected cell with fill handle across the range that you want to fill.
Or running balance: [URL]
2. Extend the running balance formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle.
For example, on column X, if I type 20, bob or bobafett it type 2.0 instead but on column I if I type bob, it will type 3.0
they would all be edited manually
or even better, no matter where I type it on excel, it would go in the right column, like bob X, then it would change to 2.0 and go directly in the X column
adjust the following code so that my images are just a bit lower than "top aligned"? The code centers my images perfectly within the cells, but when you top align an image, it's just a hair too high and border formatting does not come out.
1 is a Master sheets with all prices of products etc etc. 2nd sheet is an update of all products that have changed in price.
There is a unique code for each product.
What i was hoping for is a way where on the second sheet the product (using the unique codes assigned to the product) is looked up on the master sheet and the NEW price on the 2nd sheet replaces the OLD price on the Master sheet?
I have created a simple UserForm to enter new customer details to the Customer List in the spreadsheet, form works fine except for one little thing, which is New Customer ID, which works, but only with numeric values like 1, 2, 3 and so on.
Basically what I need this for to do is once form is opened/called new customer ID need to be created, which could be and Alfa numerical set of characters like AA-01234, AA-01235, AA-01236 and so on.
Also, is there a way of posting newly added Customer ID in the MsgBox along with MsgBox "One record added to Customers List. New Customer ID is "
All of my attempts to create this are failing and causing errors.Here is my code, Customer ID is TextBox1.
I have made a custom list in Excel 2010 so it auto-increments the alphabet using the auto increment pull down/copy square on the bottom right of a cell in Excel. My question is I have some data in a sheet such as this
600 600 600 600 601 601 601 601 602 602 602
What I want to do is add a letter of the alphabet to the end of these numbers and have the alphabet auto increment based on the data above like this:
I'm working on a time sheet, and to make it easier to enter time i created a macro that sets the auto correct to correct a "." to a ":", but when the workbook closes i want to run a macro that deletes the auto correct rule.
I have built a model which aims to calculate various information/statistics based upon 2 inputs which I can change (see 'Mov_Avg_Chart' tab cells C6 and C8). In cell C6 I have identified the moving average period that I would like, and in cell C8 the period for moving average of the gradient/standard deviation of the gradient.
I have created 4 data tables (one for annualized return, sharpe ratio,max drawdown, and annualized risk/reward). The first data table can be seen in cells AE15:AH18. In cells AE16:AE18, there are the moving average period inputs (as in cell C6) and in cells AF 16,AG16 and AH 16 are the moving average gradient inputs (as in cell C8). What I am trying to do is create a table that will allow me to state a minimum moving average period (as in C6) and a maximum moving average period (as in C6), and an increment which I would like to look at the data in. Thus, in this example, the minimum moving average period would be 20, the maximum would be 40 and the increment would be 10. Also, I want to create a minimum moving average gradient (as in C8) and a maximum moving average gradient (as in C8) and an increment figure. Thus, in this example, it would be minimum of 75, maximum of 125 and an increment of 25. The point in the example is to see which outcomes based on the inputs are the most favorable.
I am having difficulty with setting up a minimum and maximum range and the incremental period. The trouble is, I don't know how to put these as additional inputs and have them drop into the data table (in place of cells AE16 to AE18 for example). Would anyone have any ideas? Also, the automated recalculation of the data table is needlessly slowing down the spreadsheet; is there a way that the data table can be recalculated only when there are changes to the input functions? I have set the calculation method to 'automatic except data tables' because otherwise it takes 15 minutes for the file to open. Thus, the data table isn't calculating automatically. That is, cells C6, C8 and the minimum and maximum range cells that I am seeking to create? Would someone be able to help me with this?
Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.
The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..
The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.
I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?
Above formula, in cell E8 is the number 3, however excel does not recognise this and returns 1. is there a way i can get E8 to see that it is a 3 in that cell and return the correct number of rows downwards in my table?
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
