I have a document that has a sheet for each project, 3 at the moment. On this sheet are various text & currency fields, 24 in total.
I want to create an overview document for all of the projects so it will display lines from the individual project sheets that are currently showing as NOT complete.
To clarify I currently have 4 categories for status at the moment: Planning, In Progress, Awaiting Inspection Report, Complete. So I want my overview to show anything that is not complete.
I also have no need for all of the 24 columns in the overview either, I've highlighted about 9 at the moment.
I think maybe a lookup and If combination is my answer but I'm not altogether sure if there are other functions out there that I just haven't come across yet, or how to combine an if and lookup.
Also I would like the data to be sorted firstly by Inspection Location and then in date order if possible. I know I could use the auto filter option once the data is pulled across but it was be easier for the other users of this document whose skills range greatly.
I have turned off show zeros on a sheet. This is good in general, but for certain cells I would like excel to show zeros if the formula output for that cell is zero.
Background information: I am working with a very large workbook, and the final sheet is my output file. This sheet is then exported to a CSV file and imported to another program in my workflow process. I need most blank cells to remain blank, so that they are interpreted as blank in the output program. Some of my cells are actually zeros which I intentionally inputted as zeros...those should be interpreted as zero and not blank.
I need the 9th to the 15th character in my cell to be my Sheet name.
This is what I have got but it is coming up an error. It will always be Cell A4 and will always be characters 9 to 15 (These are Numerical the rest of the cell is Letters)
using VBA, I created a simple problem that I can't figure out how to solve in worksheet 2, I have 3 worksheets.
1. Players - Complete info of players
2. Members - When I select the team name, I'd like to display the info First Name, Last Name and Team only for the team name I've chosen. This should automatically change whenever I select a team name.
3. Team Codes - Tables I used for my vlookup and drop down list.
The worksheet perfectly performs as expected, big thanks to JBeaucaire again. Now, as am looking around of the file and doing some exploration on the array, as I was trying to make this applicable to real life application, I noticed the following mysteries...
1. When I tried to delete some rows, The Members Sheet output went wrong and displaying #N/A. I noticed it's because the formula range have change. But it would be hard for a new user to re-arrange it over and over again everytime when a row is deleted.
2. I've been trying to solve this problem to no avail. I found this link that gives me an idea how my worksheet should work, this was already solved but I can't apply it on my worksheet...
3. I would like to add another column on A to put the 0 and 1 on, but I don't need to delete this column after the resort so that next time if I need to delete another row existing row i'll just replace the existing 0 with 1 on column A.
The sheet shown, Complaints, lists on each row information on a single complaint. This information extends from Col A to Col Y (though not that way here, the data actually begins in row 3). I want to highlight a Customer cell, say H227 for Customer A, then activate a macro that (1) recognizes the active customer (here A), searches the list, identifies each row where A is the customer and then writes each row into sheet "Event" beginning in row 4. I should then see the same A to Y cells of information, beginning in row 4, filled in sheet Event for the six customer A complaints. Note, the actual data in sheet Complaints involves 300 rows of data, and over 100 customers can be on the list, some up to 15 times. I know that I could do this by filtering the data, copying and pasting, but a simple macro would be quicker for the group of individuals involved. Note, I then use this information to populate a chart.
Here is an example of my spreadsheet: Excel example.xlsx
I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.
I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.
I have a workbook with a Cost Analysis sheet, a master menu sheet, and then all the sub sheets. I have coded command buttons on the master sheet, so once a category is selected, it goes to the appropriate sub sheet. On the subsheet is a list of items of varying sizes, and command buttons beside that which will allow the user to "Select" that particular size. Once the size is selected, the verbage in cell B5 plus the verbage (size) beside the selected amount, and the prices (labor and materials) in the next two columns, need to be transferred to the first available blank line, starting with line C5, to the Cost Analysis sheet. I have never done coding before for a Loop (i.e. For i = ???) and am not sure how to get it to do what I want. After they select that, they can pick a command button that takes them back to the master menu so they can continue picking various items until they are through. Then the Cost Analysis sheet will show all the items they picked, (name of item, material cost and labor cost: Columns B, C, & D). Not everything is coded yet (such as selecting a command button to go back to the main menu, because I wanted to make sure I could solve this problem before doing the easy stuff).
I read over quite a few of the previous threads and am not sure how to change them to suit my circumstance. I know some code (VB), but not much. I do not understand the concept of DIM, and how the loop works to make it happen myself, and all the websites I could find were not suited to my situation of copying three cells of data to the first blank cell on another sheet.
I have a list box that I'm using to pull customer information from my "Customer" sheet to my "Invoice" sheet. I can actually open, select, and close the userform. However, I can't get the macro to put the information into the specified location on my "Invoice" sheet.
Here's my code so far:
Private Sub Cancel_Click() Application.ScreenUpdating = False
Unload Me
End Sub
Private Sub customer_Change()
End Sub
Here is the "Customer" sheet I'm pulling the info from:
And here is the location on the "Invoice" sheet that I'm trying to direct the userform to:
And here's my userform just for reference:
The userform works perfectly until I press the OK button. That's when I get the error.
I have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.
I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).
To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.
how to use vba in excell. First question. 1) I have a spreadsheet with names in column one and other info in columns B-I. I would like to make excell do the following. If I enter the name a second time and so on, then can excel take the info out of certain cells from the first entry? Say column C, D, and G and auto enter them for this new row. 2) can I copy parts of a sheet to a new sheet and then when I make changes or new entries then they will update the copied sheets info?
I have about a 100 sheet workbook for a project my company has upcoming. All the sheets are numbered 1-100. I also have another sheet that has the description of each pay item on ROW B of the sheet. Well here's my question. I'm trying to get a function that i can just copy and paste in each sheet instead of changing the number on each sheet.
For instance...
I would be using =Descriptions!B2 for sheet 2, and then =Descriptions!B3 for sheet 3 and so on. My question is does anyone know how i can get the sheet name so then i could just copy and paste one function that would be like =Descriptions!B(SheetName)
So that way i can just copy and paste instead of having to change it each time. I found this function to get the sheet name but can't seem to incorporate it into the =Descriptions!B
I need to make a macro to get sales information from sheet one to sheet 2 in a different order with a few other things that need to be added in everything I need is in sheet 2.
Sheet 1 is what I work with and I need it to look like sheet 2 using a macro I wrote what I need in sheet 2A13-...
how to move info from one sheet to another in the same workbook? Sheet one has several columns, including company name. Sheet two has company name and phone number. Not all the companies on sheet two are on sheet one (1500 on sheet 2, 200 on sheet one), so a sort-cut-paste won't do it.
If the company name is on sheet one, I need for it to get the corresponding phone number from sheet two and put it in the phone number column on sheet one.
I have sheets Sheet1, Sheet2, Sheet3 and Master in a workbook.
In sheet Master, I list Sheet1, Sheet2 and Sheet3 in range A1:A3. In range B1:B3 I want to return the date in which the sheet in A1:A3 was last updated. So everytime Sheet1 has changes, I want the date to be logged in B1. Same with Sheet2 (B2) and Sheet3 (B3).
I have tried writing the code but it returns errors.
I am creating a spreadsheet and I need it to recall info from another sheet. I dont know how to do this. The way I am designing it is so that sheet 1 has a spot to input a date, on the second page of the spreadsheet, will have a list of customers and due dates, if the date inputted on sheet 1 is past the date of their due date on sheet 2, I want their info to come up on sheet 1.
I have two sheets, the first is "Form" & the second is "Tracking". The "Form" sheet contains one button that, when clicked, brings up a userform. The userform has 11 checkboxes & two buttons ("View Next" & "View Previous"). The "Tracking" sheet has 11 columns (A - K), which will contain either "True" or "False".
I need the checkboxes in the userform to pull the values from the "Tracking" sheet. For example, the "Tracking" sheet has the following information listed: ....
I created a userform with 3 textboxes and 1 combobox. I want the user to be able to open the userform, fill it, save the information on a worksheet and email the userform information according to the combobox information. I've managed to do the email part. It's working great. The saving to the sheet part I just can't figure it out. I would like to textbox1 info in column A row 2, textbox 2 in column B row 2 and Textbox 3 in column C row 2 and comboboix info in column D row 2 . After, if the user opens the userform again, I want the same thing to happen but instead of row 2, row 3 and so on.
I would like to use the combo box to perform a search function. I have 4 columns of data and 2 sheets, The first column in sheet 1 is the "Name". By selecting the person name in the combo box, the data of that person would be shown in another sheet (sheet 2). And now I have a combo box in sheet 2. How can I do this using the combo box?
I have 3 commission workbooks all with about 20-30 worksheets in them with each one listing the sales rep and a history of commissions owed/paid. I want to create a single workbook/worksheet where I have listed all the sales rep and the amount of commission they are currently owed without have to copy/paste or type it all in one at a time. How would I go about this??
So i have two sheets with user info and so on, my boss asked me if i can transfer person names from sheet1 to sheet2 but each name must be in correct place acordingly to username column
Capture6.jpgCapture7.PNG
in capture6: in F18 you see 139401arle and G18 is supposed to be his name but it's in other shhet as you can see
in capture7 his name "Ar Sveinung Leira" is in E5
is there any way to transfer names so that they go in right place?
I've started building a macro that loops the sheets and collects the information onto the first worksheet. I've been using Activesheet and activecell references but i'm afraid looping will change these references.
I have to worksheets, the first is filled out and at the end Ive place a button that I would like to open the second sheet and fill in cells that have duplicate info from the first, so just copy & paste but kind of automated for speed.
I have only got as far as opening the second document with the macro, after that im stuck!
The Info I would like to be copied across from the "service Diary" to the "Service Report" would be As follows: *CUSTOMER *CONTACT *MACHINE TYPE *SERIAL NUMBER *QUOTE NUMBER *ORDER NUMBER
I have a blank log template on one sheet, On this I need to be able to enter two variables that tell other cells to pick info from the next sheet. The two variables being 1) Date and 2) DAY SHIFT or NIGHT SHIFT.
On the next sheet is an archive of data running in columns with the dates in row1 and the denoted shift (day or night) in row 2. The rest of the relevant info is in the columns below each date and shift
What formula would I use so that the log template cells fill with data from the archive when the date cell and shift cell data is entered.
I'm not the best at explaining, but I think that the excel sheet is pretty straight forward.
In Sheet2, cells C3 and D3 are manually entered by the user. I am looking for a formula for B3 that will take the information entered into C3 and D3, find the row in Sheet1 in which column C=Sheet2!C3 and column D=Sheet2!D3, and give the unique corresponding result from Sheet 1 for that row in column B .
So, for example, if C3=A and D3=102, then the result would be 444 if C3=A and D3=104, then the result would be 111 if C3=B and D3=102, then the result would be 111
The combination of C3 and D3 is always unique and will never have more than one possible result in column B of Sheet1.
I have a workbook which serves as a master database at work. it contains two sheets: current residents of our facility (let's call this Sheet1), and those that have left/been discharged (Sheet2). It contains 87 rows and 34 columns of info.
I'm looking for a macro that will do the following:
When I click on a cell in any row (i.e. I need relative references) on Sheet1, the macro will (once activated):
1. Select the info between column D and column AH (inclusive) on the row where the selected cell is... i.e if I click on cell F4 before the macro is started, it will select the info from D4:AH4.
2. It will COPY this info
3. It will paste the info into the first empty row on Sheet2, starting from column C (i.e if the first empty row is 200, it will paste the info from C200:AG200)
4. It will then have a popup asking for: a. 'Date Left' and b. 'Reason', with two empty fields to input the info into. 'Date Left' is (obviously) a date value and 'Reason' is a text string. Once OK is hit on this popup, the Date Left will be pasted in column A of the same row (in our example, A200), and 'Reason' will be pasted into column B (again, in our example B200).
5. The macro will then go back to Sheet1 and delete the info that was selected between Columns D and AH inclusive (in our example, D4:AH4)
I am wanting to transfer data from one sheet to another depending on cell input
Example:
If I enter "2" in sheet1 cell B1, then I want a macro to take A1 to sheet2 but place it in A1 & A2. Then say I enter "1" in sheet1 cell B2 goto sheet2 and put A2 into A3 and so on.
Depending on the number entered on sheet1 column B, the Macro duplicates the info from Column A in that row into column A on sheet2. ??
