I am attaching a file that I have just really been struggling with. I have been through multiple forums and ideas but just can not seem to find one that will completely resolve my issues. There are two tabs in the spreadsheet. I want to bring information from the detailed one over to a summary. Please take into consideration that this has been extremely simplified from my original information just so that I could post it. I am not going to use pivot tables, as this is going to be distributed to others at some point so most information will be hidden except the summary. The product and type are normally coming from a drop down list also. The summary format is by request of management so not much choice in changing it.
This was a snap in Lotus. Unfortunately, the VLOOKUP and HLOOKUP formulas don't translate to Excel on conversion. I need to estimate the weight of reinforcing steel in concrete based on 7 different sizes of rebar and spacing of the bar between 1" and 24".
I have created a matrix(lookup table) with the bar spacing listed in decimal equivilents in feet on the top row.Inthe row below, I have numbered the columns from 1 to 24. the next row is blank. Along the left side of the matrix, I have listed the bar sizes 3 thru 10. Within the table I have listed all the bar weights/SF of concrete. IE if the slab has 1 mat of #5 rebar spaced 6" oc. If you look in row 7, under column 6, the weight of weight of the bar in that sf of concrete is found. In the part of the worksheet where the caculations are performed set up as follows:.....
A single macro that I can assign to a button and, when clicked, will compute 3 solver solutions instantly without the solver boxes popping up.
My problems I am having trouble with the "ValueOf:=" part. In my code below, I am trying to tell it to go to either cell b14, b15, or b16, select that value which will be a number, and use that number as the "ValueOf". The ValueOf is the number I am ultimately trying to achieve using Solver. Range("B##").select doesn't work but I need to somehow select that number in that cell and tell solver to use that number when computing.
If I manually type a number for the "ValueOf:="0.952975095" it will compute it correctly but manually typing this number in each time defeats the purpose of creating this macro. The whole purpose of this macro is so I don't have to manually type in numbers.
Also, recording a macro with multiple solver steps hasn't worked. When the macro is ran with multiple Solver solutions, it only computes one solution... the last one. I think the last data is stored in the Solver application but I'm not sure how to erase it, and start a new solver setup. Anyways, you all would know better than I would.
What's important? It needs to compute in a certain order. In this order: Cells D14, then D15, Finally D16. That's probably not a big deal though.
I am trying to use a lookup to find the quantity of a certain fabric. However, more than one style uses the same type of fabric. Is there a way to do a lookup that retrieves multiple values?
I'm trying to solve i have multiple books but each book contains same information e.g
Book1 Sheet 1 Date Name
Sheet 2 Location Postcode
Book 2 Sheet 1 Date Name
Sheet 2 Location Postcode
etc ... there is also multiple sheets in each book all are same layout just each book is different customer.
I'm trying to do is have a Listbox that shows all customer name and link back to His/her file location. Then retrieve certain information and display on blank workbook?
Question: Can this be done without opening workbook Considering i have 20+ workbooks and growing is this even possible Cannot merge workbooks into one as this info is supplied by third party
I have two lists. One is a database (Worksheet 2) with a lot of information, and another is the list (worksheet 1) I need to fill up the data for. What I want to do is to search for the names that are in worksheet 1 in worksheet 2, and if there is a match, retrieve the missing data in those rows, and paste it back into worksheet 1. For example: Worksheet 1: Joe Tim 23 Matt
Worksheet 2: Alex 45 Joe 23 Mark 47 Tim 44 Matt 24
I basically want it to be able to search for the corresponding name, and then pull the number associated with it.
retrieve the multiple rows of data from one specific item to another worksheet inside same workbook. below attachment is my sample workbook.
inside the workbook, eg.. please have a look on Sheet 3 (the record of the item and its description) will store on Sheet 2. From sheet 1, when user choose the item from drop down list, and click the button will direct to the retrieved result in sheet 2. my question is i cant retrieve the multiple rows of data exactly in the sheet 2. it only remains one item for one row of description.
and below attached picture is what i wish to achieve =)Capture.PNG
I am trying to pull data from multiple textboxes on multiple worksheets and compile it into a list on a mastersheet. I have searched multiple forums and I have been unable to piece together anything. I have attached an example workbook of the data that I am trying to compile. I am trying to pull the data for the textbox next to NAME, SS#, and SCHED. I have tried recording a macro for 1 sheet and then modifying the macro to work for on all worksheets but failed miserably.
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.
This is as far as I have got but even this won't work:
I have created multiple nametags. I want to use a button which when pressed will run a loop to extract each individual element from the nametag and assigns a value to cell based on that. I have coded for the other functionality that happens when the cell value is changed but can't figure out a way to run the loop.
i have workbook in which I have two sheets, one of them cotains colums with information and in the other sheet I have to create a Search box to retrieve all from any Contact number, of course it has to be based on contact numbers culumn.
When I run it it returns in A1, Jeffrey.Brown2. I would like to turn this into Brown, Jeffrey but because another user may have a shorter or longer name the Left & Right function does not work for every scenario?
Sub who() Range("A1").Value = Environ("username") End Sub
retrieving data from financial website databases like yahoofinance.com and bloomberg.com. I'm trying to make an automatic stock analysis model to read from the website database and retrieve the data into excel sheets. For example, when opening the excel model the user gets a popup to enter the stock ticker, the user enters the ticker and gets a set of data. Is this do-able in excel?
i have a long list of what were once file names in excel that i need to retrieve data from. i have attached an example file with 2 file names which i recomend to view while reading this request.the file has 2 spreadsheets. the first one is just the file name in the format in which i recieve it. the second one is a table that i need to fill out from the data in those file names.
i have a problem with the following columns in spreadsheet 2:
1. column C: i have the command to copy the site name as it is to this column, but what i need is for the program to read if the site name is ZANUAH or ADORA and then write only Z or A. note that these two site names have a different number of charecters in them.
2. columnd D: similar problem. i need it to read the lab name and write AL if its MAGAMA, BA if its Ben-Ari and SH if its shafir. i have the command for excel to simply copy the word, but how do i make it write the letters that represent the lab name rather than the lab name itself?
3. column E: the report number is the 6 digit number in the file name. i have the command which retrieves it, but it has trouble when the length of the number changes. its important to note that sometimes the number might contain non-numerical characters like 219641-1.
4. column O:i have the command to get the data from the parentheses next to PSD in the file name into a box. what i need is to get it to copy just the letters C or NC from the file name into this column, without the number.
5. column P: same as column O, but here i need just the number, without the C or NC.
6. column T: all i need is for it to copy the last 2 letters from the file name, which i know how to do. the problem is that since the file names come with a .pdf at the end, all i get is df. so in fact i need it to copy th 6th and the 5th letters from the end of the file name, which is above my abilities.
I am trying to get the data out of a cell and put it in a textbox in my userform. What I have is a Worksheet that has autofilter on. After the user clicks certain objectbuttons, there is only one row, that has data in it, displayed. The cell I'm after will always be in column A and be the second visible row.
I have a fairly simple matrix table that im needing help in which way to retrieve the information correctly.
Starting in row 10... My Column A has length that i want to return
My Column B has a minimum value that i need A2 to be larger than, say 20 My Column C has a maximum value that i need A2 to be smaller than. My Columns C & D, E & F, G & H and so on then have different sizes also available... but thats for me to work out later.
Is the best way to achieve this with a series of If statements as Vlookup only returns values to the right, and i want the value on the left, or what is my best way of tackling this problem.
I can attach a sheet that i started if the above makes no sense but my formula is useless so far...
=IFERROR(SEARCH("[",string,1),MID(string position,start of char,length of char))
Hi I wish to pull out the characters from a sentence. Once it detects the "[" from the sentence it should pull the string that follow limiting to the length of the character.
It looks like I did something wrong and the results shows only the position of "[" only.
I want to error check because it to return nuthing if there's no value. IF statement would process errors in this case.
I have the following in Column A. 01. Artist - Title
I manage to put 01 in Column B and Title in Column D I am struggling to split between the "." and the "-" to show Artist in Column C I am using in Column B
I am facing the some issue while tried to display the data from array. Below is code I have to tried to display the data from array.
Scenario: I have assigned the few values in my excel to an array and tried to display the data stored in array
Sub Main() Dim Rows_Array() As Variant Dim i As Integer, Sheets("EmployeeWise").Select Rows_Array = Range("C6:C" & Range("C65536").End(xlUp).Row).Value For i = 1 To UBound(Rows_Array()) MsgBox Rows_Array(i) Next End Sub
I am looking to find the rider names of the first top 3 position for each stage in Results Page. The data comes from Stage Results. Initially I used Index and Match but to no avail.
I need to retrieve 6 digits (It's really the date in "ddmmyy" format) from a string. If the strings were to follow a standardized format it would be a simple matter of using left, right or mid. However, I have a couple of different scenarios.
The "ddmmyy" I'm looking for may be sandwiched between other characters, separated by a space or remain at the end of the string. Is there a way to sniff out the 6 digits in the string regardless of it's position?
Here are some examples:
1) ABC COMPANY 3.6% 100111-BLAH 2) MCCAIN JOHN4.9%120509 3) ROYAL BK SCOTLAND 4.03% 160309 4) IBRD 8% 090210 BNPHFN00002 HKD5) AUST & NZ BANKING 4.23%161109-EMTN
I'm using this code to input values into the next available row of columns of A and B.
Range("A65536").End(xlUp).Offset(1).Select ActiveCell.FormulaR1C1 = Textbox1 Range("B65536").End(xlUp).Offset(1).Select ActiveCell.FormulaR1C1 = Textbox2 I have this piece of code right under it:
I have tried to find how to retrieve a worksheet from a closed workbook and I am almost sure that there was an answer how to do this in FAQ but I cannot seem to find it, does anyone know where this thread is?
