Solved

Excel linking data question

Posted on 2011-03-04
11
247 Views
Last Modified: 2012-06-21
Hi, i have an excel spreadsheet (SHEET1) which our customers use to track sales etc. I want to create and maintain a MASTER spreadsheet which would hold things like budgets, Float amounts etc which would feed data to SHEET1, so everytime we need to change something for a customer (like a waste budget), we would change the MASTER spreadsheet which would then change the customers spreadsheet.
The problem with this idea is that i want to use EXACTLY the same SHEET1 spreadsheet for all our customers. Obviously this wouldn't work as Sheet1 would need to get SPECIFIC data for the SPECIFIC customer.
Is there a way that i could enter the customer number in SHEET1 (in cell A1), then have multiple worksheets in the MASTER sheet (named as each customer no.) which would contain details for each customer. Then SHEET1 linking to MASTER / [CUSTOMER NO.] TAB (getting the customer no from cell A1)
Can anyone tell me if this is possible please? maybe using a VBScript??
0
Comment
Question by:pritchardl
11 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 250 total points
ID: 35035718

You can use vlookup to do what you are looking for...

Enclosed is the workbook for your reference which tells you more about how this formula is used....

Saurabh...
vlookup.xlsx
0
 

Author Comment

by:pritchardl
ID: 35036009
Thanks for your reply.
How can i use VLookup to use the customers no in cell A1?
Remembering that i would want the VLookup command to be identical in every customers spreadsheet, but the customer no. in cell A1 be specific to the customer.
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 35036451
You can use INDIRECT function.

Say you have "Bob Jones" (without quotes) in A1 then you can refer to a cell in Bob Jones worksheet in Master workbook with a formula like this

=INDIRECT("'[Master.xls]"&A1&"'!C10")

You need to have the Master workbook open as INDIRECT doesn't work with closed workbooks

regards, barry
0
 

Author Comment

by:pritchardl
ID: 35036545
Thanks Barry. Is there no way of updating while the MASTER spreadsheet is closed?
our customers wouldn't have access to the MASTER spreadsheet, we just wanted the SHEET1 to update from MASTER on open.
Thanks
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35036846
The only other way I know is to use INDIRECT.EXT function - that's part of Morefunc add-in, you'd have to install the add-in to use that function, though so I don't know if that would be feasible for you.

Morefunc is free if you want to try it - download from here

regards, barry
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:pritchardl
ID: 35037118
Thanks, I am still confused on how i would get it to recognise the Customer No.of the SHEET1 being used. Does anyone know what formula i would use to get it to look for the [customer No.] on SHEET1 then find the correct tab in the MASTER spreadsheet?
0
 

Author Comment

by:pritchardl
ID: 35037400
I have got the INDIRECT function working, but i cannot get the workbook to update values when the MASTER worksheet is closed using the INDIRECT.EXT function....
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35037817
Have you installed Morefunc? I don't have access to it at the moment - I can try later and get back to you......

barry
0
 

Author Comment

by:pritchardl
ID: 35043045
Hi barry. Yes i installed it on  my home PC (as i need to check with work that i can install the Morefunc on my work PC). It worked great after i figured out i had to put the full path of the MASTER spreadsheet in the formula.
I now have to check if we are allowed to install Morefunc into all of our customers PC's if this is the only way to achieve what we are trying to do..
Thanks for your help, it is very much appreciated.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35356996
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now