Retrieving Data From Other Excel Workbooks With Various Names (without VB)

May 28, 2014

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:

Trading Summary
Ref Number:
A123456

[Code]....

View 3 Replies


ADVERTISEMENT

Retrieving Names Of Places From Google Maps For Selected Region In Excel Sheet

Nov 2, 2011

Retrieving the names of all places in excel sheet as per Google maps or visible in google maps for a selected District / State / Country.

View 9 Replies View Related

Multiple Workbooks With Same Data Retrieving Info

Oct 17, 2013

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

View 1 Replies View Related

Excel Formula Retrieving Data From Very Large Dataset

Jan 17, 2013

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?

View 8 Replies View Related

Macro For Retrieving File Names

Jan 10, 2008

Here is what i have:

View 13 Replies View Related

Excel 2010 :: VBA / Creating Multiple Workbooks From A Template And List Of Names?

Aug 20, 2013

The easiest way I can describe the scenario I am trying to create, is to use a company list of personnel (my index) and to generate a time-sheet workbook per person based on a pre-populated template. This time-sheet is saved as the persons name and has the persons name entered into cell D:10

I have a workbook which contains two worksheets;

1) An Index sheet which contains a list of names that I wish to use in Q16 downwards (note the length of this list will vary each time I run this)

2) A "template" sheet which I wish to duplicate in new workbooks

3) A second "data" sheet that I wish to copy across in new workbooks

I need a macro that will take the "template" and "data" sheets and copy it into a new workbook, renaming each new workbook to each name in my Index sheet. I also want that same Name to be copied into cell reference D:10 of the "template" each time.

The end result is that I should have a series of new files generated and saved which are named the same as the Index list, with both the "Template" sheet and the "Data" sheet present, with the cell D:10 pre-populated with the Name provided in the "Template" sheet.

I'm using Excel 2010.

View 9 Replies View Related

Excel 2007 :: Retrieving Value Of Corresponding Cell In Same Row

Aug 6, 2014

I am currently struggling with a spreadsheet that has been created in Excel 2007. Essentially, it has a number of items (individually identified by "S code" in the first column) that need to be tested at the specific dates over a one year period (i.e. at "2 weeks", "4 weeks", "8 weeks", etc) as shown in the screenshot below.

A user manually enters "Complete" into the corresponding cell in the "In-testing status" section of the spreadsheet when testing has been completed for a certain item at a particular time point.

Screenshot.jpg

I already have set up conditional formatting that highlights cells with dates older than the current date red. What I need to do now is to check for a particular item and date whether or not the corresponding "In-testing Status" cell reads "COMPLETE". If it does, I need to use a conditional formatting rule to return formatting to normal.

What I am unsure of is how exactly to retrieve the value of the corresponding "In-testing Status" cell.

Unfortunately I can't use a macro-enabled workbook in this environment

View 14 Replies View Related

Excel 2007 :: Retrieving Count Distribution Of Groups Of Cells?

Jan 27, 2013

I have a .csv open in Excel 2007 that looks like the following

Code:
1 566
1 566
1 566
2 341
2 341
3 566
3 566
3 566
3 566
...

There is a one-to-one relationship between Column A and B, but B is not unique (but can only take a small set of valid values). I wish to query how many id's (Column A) contain a particular property (Column B). If the example above ended before the "...", I would like to get as the output:

Code:
566 2
341 1

because the property 566 is owned by two id's (1 and 3) and the property 341 is only owned by the id 2.

View 3 Replies View Related

Excel 2007 :: Find All Names According To Data

Nov 21, 2012

I have a table with names of employees in Column A, the data for each employee is written in columns B, C and D.

I'd like to be able to type in this data in cells E12-E14 and have Excel bring up all of the names in the table that are associated with this data, and preferably separate them with commas.

I'm working with Excel 2007, without VBA/Marcros.

Please see the example file: find_name_example.xlsx

View 7 Replies View Related

Retrieving Data

Dec 31, 2008

I have a spreadsheet with several tabs. I am wanting to lookup data in tab "A" based on data in a list. For example:

I may have:
Tab A
Tab B
Tab C
Tab D
Tab E

I have created a validation list containing A, B C, D, E I would like to use Hlookup to look up data imported into the table in Date order.

View 3 Replies View Related

Using Defined Names From Different Workbooks?

Sep 15, 2014

Is it possible to use defined names from other workbooks? I would like to use this defined name as part of a VBA code.

Example: ActiveChart.SeriesCollection(1).Values = Range(Name1)

where Name1 is a defined name from a different workbook

View 3 Replies View Related

Defined Names In Workbooks

Feb 24, 2009

I am currently overhauling a massive workbook, that previously relied on an absurd amount of named ranges in order to run vlookups. I no longer need these named ranges and would like to delete them to unclog the workbook. Currently I only see a way to delete them one by one. Is there a way to mass delete all of them?

View 9 Replies View Related

Merge Workbooks With Same Names

Feb 3, 2007

Each month I run 2 separate reports and have macros that break each report out by distributor. The end result is each spreadsheet creates a new tab and new workbook for each distributor’s information. Each spreadsheet has information for 30 distributors, so I end up with 60 new tabs and 60 new workbooks.

Example:
Spreadsheet one separates detailed sales information for:
Abc Company
123 Company
Misc. Company

Spreadsheet two separates summary sales information for:
Abc Company
123 Company
Misc. Company

Currently, I save each spreadsheets new workbooks in a different folder because each spreadsheet creates workbooks with the same distributor name. I then go back and open each spreadsheet with the same name and move a sheet from one workbook to the other and resave. Now I am trying to figure out the best way to merge these back together and am struggling with where to start.

I could keep opening each of the 2 new spreadsheets that are alike and copy or move sheets but with 30 new tabs to move to the matching spreadsheet it becomes time consuming. I’m hoping there’s a better way!

I’ve attached the Sales Detail Example and Sales Summary Example to see how the spreadsheets are being broken out but had to remove some data to trim down file sizes.

View 9 Replies View Related

Range Names Between Workbooks

Jun 21, 2007

In one workbook, i have a sheet with date like this:

TITLE
Data......

TITLE2
Data2....

In each case, the Data is a block of cells of varying number of columns and rows. Each of the data blocks is a named range. When I reference the ranges from a separate workbook, the results from calling the range don't work at all (return #VALUE!) UNLESS the layout of the second workbook is the same as the source data! so for example, if range Data is in cells A2...F5, then i can reference that data ONLY if i put my formula in cell A2...F5 on the second workbook. this appears downright ridiculous to me and i'm hoping someone can tell me what i'm doing wrong!

View 5 Replies View Related

Excel 2010 :: VBA - Hide Sheets Using Code Names Not Sheet Names

Oct 15, 2013

Code:

Sheets(Array("Sheet 1", "Sheet 2")).Visible = False

How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?

Want to ensure my code will work if the user changes the sheet name.

View 2 Replies View Related

Retrieving Data From A Website

Jul 31, 2013

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?

View 2 Replies View Related

Retrieving Data From Text?

Sep 22, 2013

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.

View 2 Replies View Related

Retrieving Data Of Certain Cell

Jan 28, 2009

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.

View 5 Replies View Related

Retrieving Data From Table ...

Nov 21, 2008

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...

View 6 Replies View Related

Retrieving Data From A Cell

Nov 24, 2009

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

View 3 Replies View Related

Excel 2010 :: Create Database That Contains Data Taken From Several Workbooks?

Mar 8, 2014

I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.

I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?

View 1 Replies View Related

How To Make Same Macro Run On Workbooks With Different Names

Apr 15, 2013

I tried recording a macro to automate some tasks I perform every day between two workbooks. While in one I want to go get data from the other. The problem I'm having is the name of the workbooks I want to run this on changes every time I download my data into Excel. The files always start with the same name but the date gets appended to it. Is there a way to modify the macro to just work with the two books I've got open?

View 3 Replies View Related

Linking Names To Closed Workbooks

Feb 14, 2007

I've named some cells in the source workbook, and linked these names to the destination workbook. Then I used the names in some formulas in the destination workbook, and it worked great. But when I closed the source workbook it doesnt work. WHen I open the source it works great, but I need for it to be able to link to the workbook when it is closed as well.

View 9 Replies View Related

Excel 2010 :: Copying Data But Not Names Ranges From One Workbook To Another?

Dec 1, 2011

I have two workbooks I am using. Essentially I am copying values out of one workbook, opening a second workbook, manipulating data and pasting it. Everything is working great but one issue! The first workbook has named ranges in it that I don't want in the second workbook (just values). I don't want them because when I try this operation the second time it asks if I want to use the same name or choose a different name. For some reason it is still copying over all the named ranges (all 343 of them!). Is there a way to not allow it to do that or simply just delete the names before I close the second workbook?

Win 7 64, Office 2010

Sub CopytoDB()
Application.ScreenUpdating = False
Worksheets("Setup").Select
Range("A2:A766").Select
Selection.Copy
Workbooks.Open Filename:="D:Server MirrorDatabaseSetup Database.xlsm"
Worksheets("Database").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

[code].....

View 1 Replies View Related

Retrieving / Returning Data From Different Worksheets?

Jun 19, 2014

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

View 6 Replies View Related

Retrieving Data From Closed Workbook

Dec 27, 2013

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?

[Code] ......

View 4 Replies View Related

Formula For Retrieving Data Using Criteria?

Mar 4, 2014

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.

View 5 Replies View Related

Retrieving List Of Data From A Table?

Jan 4, 2014

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".

View 14 Replies View Related

VBA - Retrieving Data From Google Spreadsheet

Nov 8, 2013

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.

View 1 Replies View Related

Retrieving Data And Automatically Gets Updated

Jul 14, 2008

I have a `master` listing that has a list of material on it.
Lets call it test.xls

Each employee has their own work work-book with only one sheet inside the work book, lets call this test1.xls

I want users to be able to add information in test1.xls and the list test.xls automatically gets updated

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved