I am trying to create a spreadsheet for all the different Myers Briggs personality types. I want to pull down on a menu say ISFJ and in the next column will produce how to communicate to a person who has an ISFJ personality.
View 9 RepliesI have a bunch of parts each with a list of packages sizes, prices, and additional piece prices. For example:
Part #305213
1000 pieces - $7.99/Pkg - $.05/add'l piece
600 pieces - $4.99/Pkg - $.06/add'l piece
200 pieces - $3.00/Pkg - $.07/add'l piece
0 pieces - $0.00/Pkg - $.15/add'l piece
I need to determine what is the most efficient way to order these items given various amounts. For example:
If I need 1200 pieces, it's cheaper to order 2 of the 600 pkgs than a 1000 and a 200.
If I need 400 pieces, it's cheaper to order 600 at $4.99 than to buy 2x200 at $3.00/pkg.
If I need 1250 pieces, it's cheaper to buy 2x600 @ $4.99/pkg plus 50 pieces at $0.06/piece......
I have a Master List with 3 colums The first column labeled location has a pull down menu with about 20 different options. The middle column labeled issues is just for text entry. The third column labeled contractor is another pull down menu with 18 options on it. I also have 18 other sheets within the workbook that correspond to the third column pulldown menu.
Heres what I need.
I would like to be able to enter info from columns one and two and be able to choose an option from column three and the info from columns one and two go to the corresponding sheet.
is it possible to do the following with a drop down list, without requiring VBA? I would like to have 20 products to choose from in a drop down box. When the user selects one of the products, a more detailed product description will appear in the same cell where the drop down list is. For ex: If the user chose "chair" from the drop down list, what I would like to appear in the same cell is "chair - model T654".
View 2 Replies View RelatedIs there any way for excel to automatically choose the next best spelling option for many rows of data containing mispelled words? For example, is there a way to automate pressing "Change" on every mispelling window when pressing F7?
View 8 Replies View Relatedan "Option Box"?
When I click a button, a row is copied and inserted. I think this is asking what to do about named ranges connected with the added row. Unfortunately, I have several named ranges, and several boxes pop up.
I would like to know how to add some code to the button to hide/ignore this barrage of boxes!
I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
Excel 2013
see the below link...
http://www.mrexcel.com/forum/showthread.php?t=350701. So far this is the closest I got, but it only pulls the values for 01 Nov. =NDEX(NS_Tally!W1082:W1360,MATCH($J$32,NS_Tally!V1082:V1360,0))
I've got a working macro that pulls a Userform. Everything works correctly but I'd like to have the form displayed on the far right instead of center screen. I've tried all the options on the form properties list to no avail.
Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMaximized
Worksheets(1).Select
Range("A1").Select
UserForm_1.Show
End Sub
How would the formula look that pulls/puts the right number in E19 and F19 respectively, taking input from the list above?
View 2 Replies View RelatedI am trying to put together multiple userforms that perform separate tasks. I have been using code found on this forum, and it's helped out a lot, but i'm still having trouble on one of the userforms. the one that i'm having the biggest trouble with called frmEditData. What it is designed to do is pull data from different fields and display them in the textboxes on the different sheets. then, the user has the option of updating those tabs and saving the changes. For the life of me, i cannot get the data to change. it always reverts back to the original information in the cells.
i have to say again that I don't really know how to read the code too well just yet, and i've been copying code that appears to work on other sheets, but it might not necessarily work here. I have attached a rough copy of what i've been working on.
Is it possible to get Vlookup to pull the format of text across to the new cell?
For example, if the source data is bold, can you get it to show up as bold in the new cell? Or worse, if there are multiple formats in the original cell (like some text is bold, some is red font etc.) can that be pulled across through Vlookup?
I am trying to create a drop down box that pulls its selection from another sheet in the workbook and I'm not sure how to go about doing it. I figure VBA must have some way of doing it but I am not sure how the coding would go. I've attached a very basic example of what I am trying to accomplish.
Drop Down Box Ex..xlsm
I have a VBScript that pulls SNMP data from some devices. It uses two external EXE's called "SNMPGET.EXE" & "SNMPWALK.EXE". When the script runs, it pulls specific information from our SNMP devices (in this case, I am pulling temperature values from Netboz Devices). The script looks like this :
View 2 Replies View Relatedi have created a Vlookup that pulls data from another work sheet, it all works fine apart from this one thing....
On a work sheet i have a lot of hyperlinks that work fine, but as soon as they get pulled over using a Vlookup, the name of the hyperlink comes accross for example "link 1" but you can not click on it to take you to the link like you can on the original one.
I have a crosstab query in Access that groups by month and year. In Excel 2003, I have a query that pulls in the crosstab query.
At the time, I couldn't just pull in the Access table. For the other Access tables and queries, I could go to Data >> Import External Data >> Import Data >> select the database as a source >> select the table name.
However, to get the crosstab query, I had to go to Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select the data I wanted.
Since I wanted all the data, there did not seem to be a difference. But now that new dates are being added to the database, there *is* a problem. When I wrote the query, I only included the months available -- up to June 08. Now that there is data in July 08, that month exists as a heading in the crosstab, but Excel's query doesn't know to pull it! I have to manually edit the query to add the new month.
So my question is... is there a way to do this automatically? I have two crosstabs in five different spreadsheets, making a total of ten manual updates I have to remember to do each month.
I'm creating a master spreadsheet and I want it to pull live data from other spreadsheets depending on a variable.
I've a cell that has a variable date. I've files saved that have this variable attached to it in the name format: 'filename variable'.
I want to pull data, hopefully live, from workbooks based on this variable.
For example, I have files: generic 01212014, generic 01222014, generic 01232014.
The date is stored in the master workbook say in A1 that I can manipulate by entering a different date to pull data from a different workbook.
='[generic 01212014.xlsm]Database'!$L$3
='[generic 01222014.xlsm]Database'!$L$3
='[generic 01232014.xlsm]Database'!$L$3
There has to be away to make the bolded portions of the file name variable, right?
I am having problems with the function vlookup. I attached an excel file of my problem. It seems to pull the correct lookup, then it pulls the wrong data until a correct one occurs. My excel attachment should make more since of my problem. It will also pull data that is invalid. For example, the name "S5" is not a in my original data so it should not be able to find that?? Sorry, this is hard to explain - let me know if the file is not clear enough.
View 3 Replies View RelatedI need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
View 8 Replies View RelatedI have a script that pulls my stock prices every minute and update an excel page I've created...basically an excel stock portfolio...the problem I'm running into is when a stock is unchanged...instead of being $0.00 it returns "unch"...as you can imagine this throws my calculations into chaos...I have the following formula but it doesn't seem to be working =IF(J18<0,R18*J18,(IF(J18>0,R18*J18,0)))
Basically J18 is the returned change in stock price...R18 is the number of shares held...how can I get it to view "unch" as $0.00?
New user with basic knowledge faced with a multi-part challenge.
I have a list of 10 items (multiple words) in a drop down
Based on what is selected in the drop-down, I am trying to figure out the best way for it to pull the corresponding text paragraph. This would then be concatenated with a numerical value inputed b the user in another cell. The final output would be displayed in a final cell.
I have figured out how to concatenate between one cell and the other cell with the paragraph but I am at a loss for how to make it more dynamic in the sense that the drop down selection drives the paragraph being selected and then taking the value in cell A1 to concatenate within that paragraph and outputting the final text to be copied out from excel.
I tried looking at posts for dependent data verification and I felt like I was on the right track but hit a wall.
Excel 2003
Our spreadsheet pulls values from a column when a month is inserted in a certain cell:
OCT is typed in B2 and in the column below it, from B10 down to B286, data (numbers) are pulled from the October column K10 through K286 using =IF($B$2="Oct",K10,IF(and so on for each month). IF Nov is typed in B2 then the same happens except data is pulled from the November column, L. The monthly columns from K through V are tied to and updated
from other sheets. As you can see the problem is that nested functions allow only 7 and I need 12, one for each month. I have looked at the VLOOPUP and the HLOOKUP but our data is not set up that way (tables) since they need to be exact numbers pulled from the monthly columns that are tied to other sheets.
OCT (B2)
OCT NOV DEC
Complaint 3 (B10) 3
6
Inspection 10 (B11)
10 2
NOV 11 (etc)
11 5
Door Notice 1 and
so on
Recheck 32
Citation 2
Work Order0
Demolition 0
I need a formula for the highlighted yellow area that pulls and sums from a reference table. Not a traditional Vlookup because the reference within the report can appear in multiple columns within multiple rows.
ReportABCDEF1AccountJanFebMarApr2Overtime(L7)Overtime300 300 300 300 3
Salaries(L8)Salaries1,170 1,170 1,170 1,170
Expected ResultsReference PageABCDEF1
Account 1Account 2JanFebMarApr2
Additional PayOvertime(L7)1251251251253
Additional PayOvertime(L7)1751751751754
Salaries(L8)Ex Staff2252252252255Salaries(L8)
Ex Staff3253253253256Salaries(L8)Ex Staff4104104104107Salaries(L8)
NE Saff1101101101108Salaries(L8)NE Saff100100100100
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................
The weather data I receive from a web query pulls data in oddly. For example, some days it pulls the weather for today into cell A1, and the forecasts for the following 3 days are in A4, A9, then A19. The cells in between are blank, and the problem is that it pulls it in randomly otherwise I could just have the cells reference it (ex. some days it pulls it into A2, A8, etc) What I need to do is arrange it, in order, without the blank rows, but I want to do this without deleting the rows.
View 4 Replies View RelatedI have an Excel spreadsheet with a query to an Access table. The query pulls data from 2 columns in a table and pastes it to the spreadsheet. I am trying to update the VBA a little on one sheet and now I get the attached error whenever the code tries to run in Excel. Here is the code with the offending portion separated at the top.
View 2 Replies View RelatedI have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code:
Option Explicit
'Form level variables - used in more than one event
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables
Dim intCount As Integer
Dim i As Integer
Dim intAdult As Integer
Dim intStudentSenior As Integer
[Code] ......
see the attached spreadsheet including the data. In Column AA I have created a formula that looks at Column B, and pulls each unique value from that column. add to this formula, or propose a new one, that pulls unique values into column AA if at least one of the rows has an "Actual Finalization Date" in column D that is in 2014?
View 1 Replies View RelatedI have a two identical worksheets in the same workbook with two tabs (Search, Database). The Search worksheet contains a search engine at the top which searches by column, and pulls rows from the Database tab, which match the search queries. However, the rows that are pulled up in the Search tab cannot be edited as they are all MATCH formulas. Only the rows in the Database worksheet can be edited.
This can be quite problematic if I am searching a row to edit it. Although the search engine is convenient enough and faster than filtering (as I have over 20 columns), when the row comes up, I cannot edit it. I have to go to the Database sheet and filter through until I find that row, and THEN edit it. What I require is that when I search for something and the matching rows get pulled up, I want to be able to click a button which will take me to those rows in the Database sheet.