Avatar of pritchardl
pritchardl
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Excel linking data question

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??
Microsoft Excel

Avatar of undefined
Last Comment
Tracy

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Saurabh Singh Teotia

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
pritchardl

ASKER
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.
SOLUTION
barry houdini

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
pritchardl

ASKER
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
barry houdini

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
pritchardl

ASKER
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?
pritchardl

ASKER
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....
barry houdini

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

barry
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pritchardl

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

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.