Excel 2003 :: Dropdown Pulls String Value (paragraph) And Concatenated
Jan 5, 2014
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.
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.
I'm creating some userforms for a spreadsheet on Excel 2003 and was wondering if it is possible to have the date field and time field as drop down boxes?
I would ideally like the date field to have 3 drop down boxes (date, month and year) and then the time field to have 2 drop down boxes (hours (in 24 hour time) and minutes).
The date and time would then populate one cell each on the spreadsheet.
I would like to be able to limit a drop down list's contents based on a user's selection in a different drop down. I am using Excel 2003, and I know how to do this in Access, but I can't figure it out in Excel. Basically, I have a list of themes in one drop down. The user picks a theme, and goes to a second drop down which is a list of sub-themes attributable to that one theme and no other theme.
A user inputs a string of letters/symbols all meaning different things. These would typically for my case be L, R, +, -, F. These items correspond to certain behaviors. Essentially I'm working on a L-System solver for those of you who are well versed in math. For those of you who aren't, I'll explain a bit.
An L-system starts with a string, for example:
LFL+F+LFL
and then has one or more generators:
L:-RF+LFL+FR- R:+LF-RFR-FL+
then at each iteration of the system, every L and R in the previous iteration gets replaced with that string. The +/- are incremental angles.
I need to be able to take each iteration and convert it into a series of rows with one character in each row. I need it to do this automatically, and automatically adjust to the proper length of rows (these strings can get thousands of characters long because each iteration of the system converts the Ls and Rs into longer strings containing more of all of those letters.) Once each of those rows are constructed, I need it to run some mathematical operations on each of these rows automatically.
For example, for each + or -, the value in an angle column should change by an increment given elsewhere in the sheet. For each F, the x and y coordinates should change from the previous iteration based on the angle (just a basic d+dcos(ang) or d+dsin(ang) where d is a distance multiplied by a scaling factor).
I have no experience with Macros or any type of scripting, so if I can keep this in an Excel formula, that would be ideal. I know a minimal amount of C++ and Python, but that's the extent of my programming knowledge.
Date: 17/09/2013 KO: 19:45 Ref: B Malone Att: 7,574 extraction of text from the above text string which is say in A1. What l need is for Date to go in A2, KO in B2, Ref in C2, & Att in D2. I'm using Excel 2003
I have a data copied from pdf to excel and need to segregate the data page wise so i need a macro to set the pages in specific cells example page 1 ranging from A1 to A60
Page 2 ranging from A61 to A120 and so on
Example of pdf data
PAGE 1 Should be in cell(A1 to A60) - Data can be less than cell A60 but page 1 should be within this range
A 1 CHURCH 6 HOUSE 5
[Code] ......
PAGE 2 Should be in cell(A61 to A120) - Data can be less than cell A120 but page 2 should be within this range
I'm looking for a formula to say if the contents of cell A1 are this and the contents of cell L1 are this, then enter value 1 in cell N19. For example if A1=Text and B1=Diff Text then A14=1, but if A1 doesn't =Text or B1 doesn't = Diff Text then A14=0.
I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001 q9002 q9003 q9004 q9005 q9006 etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output A | B Z 1| 10 | a 10,a,b,c 2| 10 | b 11,a 3| 10 | c 12,a,b 4| 11 | a 5| 12 | a 6| 12 | b
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
VB:
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String StringReturn = Str1 & Str2 & Str3 End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
I have an Excel 2003 file that contains hyperlinks to OneNote notebooks on a Sharepoint site. An Excel macro looks for these links and determines the full hyperlink address which is then assigned to a variable. An Outlook message is generated which includes the hyperlinks.
The hyperlinks work in the Excel file. I can also copy them from OneNote and manually paste them into an Outlook message and they work. However, when I obtain their full address and transfer that to the email through code, the links do not work. The hyperlink address from OneNote starts with "onenote:http" which is not recognized as a link.
If I can do this manually, there must be a way to do this with vba. Are there characters I need to include in the OneNote hyperlink address to make this work? Is there another way to transfer the working link from Excel to Outlook?
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
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.
I 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 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 :
i 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 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.
I 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.
I 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?
struggling with the syntax of this one. Lets say we have a variable called MYWEEK with a value of WEEK 9 and cells A1 and A2 have values of 9 and 10 respectively. I need to be able to compare the variable against cells A1 or A2 but with a prefix of WEEK .
I've tried the following:
if MYWEEK = "WEEK " & A1 then ... if MYWEEK = "WEEK " + A1 then ... if MYWEEK = ("WEEK " & A1) then ...
I know the answer is staring me in the face but I cant see it. Any use of the ampersand presumably invalidates the IF statement by introducing multiple conditions rather than a concatenated value. How do you compare a value against a concatenated set of criteria in VBA?
