I'm trying to create a automated calculator that i can customize and adjust. start and what functions should i be using. I've looked into Vlookup/Hlookup and it does not seem the function i need to make the workbook work. Basically what I need is when I enter a item on the A column excel would retrieve the list of Ingredients to the calculator worksheet and multiply it by the amount of variable entered. Then product will be printed and added together to the "sum 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...
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 be able to pick a word from a drop down list and for related information to appear in the next cell. I just don't know the function to do it.
Here's an example: I have a list of first names and second names in adjacent columns. Excel question 1.jpg
In the 2nd sheet I have a drop down list of the first names:
Excel question 2.jpg
Is there a function to make the corresponding 2nd name appear in B1 when a first name is selected. I've seen examples where INDIRECT is used to create a dependent drop down list in B1 but I'd like the information to automatically appear.
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 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 want to be able to choose a country from a drop-down list, and then have the spreadsheet retrieve/return values from that country, whose input data is located in a different worksheet. E.g. I want to be able to change the input according to what country I am looking at. I was able to do this with simple IF functions,but I'm realizing that this is not good enough. I want to eventually expand the number of countries that I want to return data from, and then the formula is not dynamic enough.
Example spreadsheet attached.
To make it simple: I want to be able to return the correct values for "page views" and "high" and "low" according to what country I choose from the drop-down menu in cell C3 on the "value calculator" sheet.
Also; in cell N15 and N16 I want the alternatives to be yes and no, and they cannot be yes at the same time. H
I'm trying to retrieve data from a workbook that is currently closed. The formula below works only if the workbook containing the data is open. Is there a way to use the following formula to retrieve the data from a closed workbook?
I am trying to work out a forumla that will bring through data onto a worksheet for teacher analysis. The data is being extracted from our MIS and put into the attached template. When I change the class on the analysis sheet I want to be able to the pull through the relevant learners attached to the class along with their data.
I have a googlespreadsheet with 4 columns. date, id, name, positive/negative.
What I would like to be able to do is to call a procedure from an excel document to copy/retrieve all of the information between specified dates on the google spreadsheet and place into another worksheet in the excel document.
The spreadsheet is hosted in my googledrive and is confidential (i.e. I cannot publish it), so I am assuming I will need to add password credentials into the code, which will be an added complication.
I need to create a dynamic list from a table of data.
I have performance data for 110 different pumps. Data points are generated every hour, and the table is updated with new data periodically. I want to automate the population of a list of 6 different pumps, and specify the date range populated. To put it another way: I want to place data from Pumps 1 through 6 for all of November into Columns A through F. Then I want to clear that data, and show data for Pumps 105-110 for last week in those same Columns.
One of the main goals of doing this -- other than quickly narrowing a field of data -- is being able to quickly chart this data on a scatter plot with a custom format. The pumps are grouped together by region, and individual pumps have specific purposes. So I need to quickly generate graphs with a series' color scheme or formatting that is consistent and logical between different pumping regions.
I haven't been able to make pivot table work because of the graphing issue, and also because of the way it handles data points and presents data. I would like to make this work with excel functions and maybe some filtering, but I'm not opposed to figuring out a VB script if you think that's what I should use.
I am working on a new budget spreadsheet that I am creating. I want to accurately calculate credit card expenses. As everyone who has a credit card knows, charges made this month are not due to be paid until next month. To make matters more complex, charges made from (for example) January 20 until February 19 are not due to be paid until March 20. With that in mind, I created a spreadsheet with a different tab for each month. Each tab has a place to put in all expenses. Here is an example:
date Category Expenses Remarks
[Code].....
Obviously, The above example is from January. IN the February tab, I want to calculate everything in the "Expense" column that has a date in the "Date" Column before Jan 20 and where it says "Kohls - Frank" in the Category column. Of course, in the March tab, I would need to grab all of the "Kohls - Frank" expenses after Jan 20 from the January tab, and everything before Feb 20 from the February tab.
I figured out how to add up everything with the following formula: =SUMIF(Jan!A:A,("<="&DATE(2013,1,20)&E32),Jan!C:C) How can I filter on the Category tab?
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.
I would like to create a summary for the ordering history of each customers. The IT department will facilitate us to generate some raw data and I want to retrieve the data to the summary excel when I type the Ref No of the customer.
For example, I have the following raw data generated, in which the file name is "A123456":
Ref No Name Address
[Code]....
And I want to extract the data to the following summary. When I type "A123456" in the field "Ref Number" in this summary, it will automatically retrieve data from the corresponding raw file:
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've been unsuccessful in trying to write a formula that retrieves a single result based on two criteria (from a large set of data on a separate worksheet). I've tried various INDEX MATCH combinations but no luck.
A B C D E
1 DATE TEAM PITCHERS RESULT
2 4/1 nyy Sabathia ???????
[Code] ........
So this is a very simplified version of my real data set which is about 20 times this size. The first worksheet is where I want to store my retrieved results (lets say D2 for example). I want to retrieve data from the second worksheet that matches two criteria (exactly) originating from my first worksheet. The two criteria to be matched from the first worksheet are, for example, A1 (sabathia) and F2 (the date 4/8). The complicated part is the desired result should be from the corresponding K/9 column in the second sheet, which in this case (based on sabathia and 4/8 criteria) is I2 (result would be 3). It's complicated since I can't just tell the formula to look down a specific K/9 column, I need to search ALL the K/9 columns in the sheet (of which there are many). Is this even possible with some sort of nested INDEX MATCH? Any possibilities outside of VBA programming, or is that the only way?
My data consist of multiple entries of same id(s) at various point of time. I need to extract the latest data of a particular ID. I was using vlookup but it extract the first entry of that ID but not the latest. and the data is entered into the database using userform and VBA macros.
I have data in a list of sales people and dates of their sales calls. What I want to do is transform the list into a table as shown in the attachment. Then I want to calculate the number of days between the first and last sales call. This part is easy using date formatting and min/max I guess. What I can't do is get the table arranged. I have used a pivot table to count the number of calls per sales person but pivot table wont just display the dates in a table format, it will only sum or count etc.
I want to put a drop down list in Cell D3 with the list of names from column B. There are 14 different names, but when i try and create the list it gives everyname from Column B.
Also once the drop down list works i would like the table to be populated with the managers supplier, so if Dan was chosen in D5, Suppliers 1 to 9 would appear below D3.
I have a client list/table that gets new data each day. (ALL) - see file
I need to create separate lists/tables on new sheets from the main data sheet (ALL) base on certain conditions/criteria...
On the (ID) sheet I need all the clients that have ID numbers to display, on the (Reg no) sheet I need all the clients with Reg numbers to display... etc.
Like I said that the main data sheet grows each day and the lists/tables on the other sheets needs to automatically include all the new clients that's entered.
Is there a way to do this using formulas or do I need code to do this?
I have created a monitoring spreadsheet for a public School system. It pulls data from 5 seperate excell files. however when a lookup value that is further down the list on the largest table it returns no data. other cells can lookup that same number and return correct information but from a different table.
The table that has issues is A1 - Y12430.. if the lookup value is low in the first few thosand rows it works.. but if thye value is in say row 9,000 it can not locate it..
I have a couple of drop down lists of data created from lists as well as fields next to it.
What I want it to do is take that data, which is name, start time, end time, days worked, queue (or work type), and fill the table next to it which is just a 30 minute by 30 minute schedule sheet with days of week 1 and week 2 on top.
I figure it would be easier to have a GO button to do this.
I will attach an example of what I mean, but please, if you guys find that there is an easier way to do this, just let me know, I feel this might involve some code/macros instead of excel functions
I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:
Column A Column B Office Employee Hamilton Emp 1 Hamilton Emp 2 Hamilton Emp 3 Toronto Emp 4 Toronto Emp 5 Toronto Emp 6 Toronto Emp 7 Toronto Emp 8 Waterloo Emp 9 Waterloo Emp 10
This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection
ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.
If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.
Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.
So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).
I would be ok with something like:
Column A Column B Office Employee Hamilton Emp 1, Emp2, Emp 3 Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8 Waterloo Emp 9, Emp 10
and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.
