Iím getting data from query. My data has fields: Item No, Customer ID & Sales rep. I need to find out for each Item number which customer put order and who was the sales rep. (Excel 2002). see file attached.
I'm trying to make an order form that is based off of a price list. Basically there will be 200 items or so someone can just enter the quantity they want into a column. I would then like another sheet on the workbook to auto-populate all of the fields available. The thing I'm having trouble with is I don't want the finished form to be as long as the price list with blank rows in-between. I've been reading up to make a macro work for this, but have had no success.
there may even be an excel formula answer to it rather than a macro. Coloumn A has a series of numbers, I want column B to show those numbers in what would be there Descending Sequence.
Thus Col A 100 97 14 87 32 108 21
So with this routine you would then see
COL ACOL B10029731478743251081216 The above I suspect is not too difficult, however if two (or more) numbers are the same I want the sequence to reflect that thus COL A COL B100297 314 797 332 5108 121 6 and no... the data - Col A - cannot be sorted, it must stay in that sequence.
If possible I would want this done automatically when any Col A value changes (any unused cells would be 0 by the way). Oh, there will in fact be 25 cells in column A for this.
I need a way to re-order an excel worksheets columns based on another worksheet.
Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.
How can i reorder the columns without physically having to cut and paste the columns to match?
I have attached a sample spreadsheet.
You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
I am stuck on what should be a simple formula. I have a spreadsheet that is sorted by "target" number in column A, basically 1,2,3,4. I am trying to create a formula that will increase the number in column B by one based on column A. So when the value in column A changes column B will reset to 1. Also I need this sequence number to be preceded by zeros up to 0999. So 1 would be 0001 and 895 would be 0895.
I am trying to group the same values in columns together and i was thinking about having a number count. What's the function that might be able to display this (display whats in Column B in the example below)?
I am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:
And copy down. I then test column B to see if there are any non-blank cells.
If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.
I can do this with a UDF, but the user has rejected this option.
I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.
I have a model built that calculates the daily Profit and loss ('Data for Pivot' tab, column M). I would like to calculate the cumulative profit and loss and am having a difficult time doing this with a simple formula. The Daily P/L column is empty (#NA value is in the cells) up until cell C138 as there is no data before that (which is the way it is supposed to be). However, once data has begun to be populated the daily profit and loss is shown (in this case from M138 onwards). The issue I am having is that I cannot seem to be able to create a formula that will capture the cumulative P/L. My desired result is evident in cells N179 onwards, as it simply captures the cumulative p/l. However, this only works if I change the formula once data is populated (in this case cell N177 is where i put in a new formula; the previous formula can be seen in cells N3-N176). If I attempt to put in the formula that is in cell N177 for all of the cells in column N, it gives me an error. Thus, I think that this may need to be programmed via VBA, but I have not been successful in doing this. The issue is that the data population in this model begins on different cells based on the different inputs that I put in ('Mov_Avg_Chart' tab, cells c6 and c8). Thus, I can't hard-code a formula based on a particular cell. If anyone can help me with this,
I have a list of 20 cities in K1:20 and they can be selected from a drop down menu in column A2:A22. I want then to randomly pick 6 out of 20 and these 6 cities must to appear in a line A1, B1, C1, D1, F1, and G1
On cell A1 I have used the
IF ( A2=K1,K1, IF(A3=K1,K1...................A22=K1,K1,IF(A2=K2,K2......A22=K3,K3)
It seem to be an endless formula is there another way to make it easy ???
I have managed to set up a dynamic range called "Managers" which is held in a worksheet named "Lists" to validate entries in Col D of a worksheet named "PartTimeStaff" -
I also have 2 userforms which either add or delete managers names from the dynamic range, this all works well.
What I need to do when using the Delete form is to check that the managers name is not selected in any cell of Col D in the "PartTimeStaff" worksheet - this is to ensure the user has reassigned the records to another manager before deleting selected manager on ListBox1within this form.
The code which runs from a command button is below
Private Sub cmbDelete_Click()
Dim i As Integer Dim SelectionsIndex() As String Dim ArraySize As Integer Dim RowToDelete As String
Application.ScreenUpdating = False
With Sheet2 .Visible = True .Activate End With
Set ManagerStartRng = Range("A3")
Application.EnableEvents = False
If MsgBox("Are you sure you want to delete this manager?" & vbCr & vbCr & _ "This action cannot be undone!", vbQuestion + vbYesNoCancel, "Confirm Delete") = vbYes Then
I have a column of data I would like to have reversed on the fly (use functions and not vba). I have a function that works for the first cell but I can't seem to get it to work for the rest of the column when I choose edit/Fill Down.
I would like to put a formula (or make a macro) for Cell F7 (and all the other cells too) in sheet "Budget Sheet" that pulls the value $100 from cell G5 from sheet "Input Sheet" by looking through columns A-F for the account ID "5111100"
- I (for the most part) cannot change the format of "Budget Sheet" or "Input Sheet"
- I cannot have the formula (or macro) just look down Column F because the actual file I am working with has the pertinent account IDs scattered through columns D-F (it was exported from another program).
Notes: - I am using Excel 2003 SP3 - If necessary, I can make the reference to "5111100 ∑ Bonus" if that would make things simpler
- Column G will always contain the dollar amount I need.
-I've been hitting the books trying to figure out how to get array formula working on this and cannot for the life of me get it to obey when I press ctrl-shift-enter.
I have Useform1 & Textbox1 & Textbox2 & CommandButton1
*Textbox1 = Identifier where to put the "X" mark when data is found *TextBox2 = The User Data 'It is a multiline textbox *CommandButton1 = Execute the macro *Excel Column "B3" = Where the textbox2 data will be compared. this one has default data. *Excel Row 2 = the identifier where to put the "X" mark when the data is found.
The user will enter data in textbox2, For each TextBox2 Value it will be compared in the Data in Column B3 only If the Textbox1 Value found in Row2 which has the codes. Then when the Value is found. it will mark with "X" in the column where the TextBox1 value found. see my screenshot.