Copying over specific values when a specific value is entered.

Hello all,

Have a 4 sheet work book.

on Sheet 1 there are rows of data, as each row is populated is recieves a specific ID number. Now, what I need to do is on sheet 4 when you enter a ID number, I want specifc fields to populate with specific data from sheet 1.

Thank you for any help, if you require any more information please let me know.

May be the INDEX & MATCH.

INDEX & MATCH

I've attached the sheet to hopefully shed some light on this issue I am having.

So here is my layout:

When you open you will see a user form, this populates sheet 2, sheet 1 then copies over some of the values and adds a unique ID (CCR. Sheet 4 is for follow up, what I need it to enter the unique ID (CCR or select from a drop down the unique ID number (CCR ( these numbers will continue to grow )onto sheet 4 cell B4 from this I need it to pull the correct information from sheet 1 and auto populate the rest of the "bolded" fileds B3 & B5-B11

I hope this helps, many thanks !!

is this what you are looking for here?
CCR2a1a.xlsm

Perfect! Now will this work when sheet 1 contains 1000's of lines of data, or was the formula built around the 2 lines currently on sheet 1?

But many thanks for your help so far, this is wonderful !!

CCR2a1a.xlsm
This one will work for 2500 lines...
all you need to do is up the 2500 in
=IF(B4="","",VLOOKUP(B4,Sheet1!A4:K2499,3,))
To whatever you need...

now, what else do you need this to do?

Fantastic, looking at the formula I figured out the same thing ( it's actually starting to make sense )

I also need the two button I discussed in another post on the forum. all pertaining to sheet 4. If you look for posts from me you'll see a few and they all pertain to this workbook.

Thanks

Can you do me a favour... i dont mind helping, but i am getting a touch confused with all the posts you have...
Can you do a list of everything you still need help on...?
Just reply to this post with a list and then i can knock them off one by one...
Thanks...
P.S. if i have helped so far, click the star below my name...

Star has been clicked my friend.

Sorry for the confusion, imagine mine...

Once again, everything I am asking is based on the same workbook you currently have.

Here are my two current issues.

Need two buttons on sheet 4 (each doing two things)

Button 1 can be call "In Work"
Needs to save sheet 4 as a new (seperate) workbook using the unique ID typed into cell B4 as the name then clear sheet 4

Button 2 can be called "Complete"
Needs to save sheet 4 as a .pdf to a location the user can select, using the unique ID in B4 and then clear sheet 4

Need to insert a hyperlink into a IF formula

Looking at sheet 1, you'll see the unique ID is cell A4, this is populated when a value is in the cell B4, the infomation in B4 to K4 is pulledfrom sheet 2. What I require is that the IF formula in A4 contains a hyperlink to the corresponding row in sheet 2, in this case row 3

And that should tie all my posts together.

Thanks again..

i dont know if hyperlink is the right word...

Originally Posted by Legend Rubber

i dont know if hyperlink is the right word...

In every reference I have been looking at it is called hyperlink, even in the formula. My issue is I do not know how to add it to the formula I have..

what do you want this "hyperlink" to do on sheet2?

Originally Posted by Legend Rubber

what do you want this "hyperlink" to do on sheet2?

Highlight Row 3, or any part of it.

I dont know user forms that well... but for your hyperlink problem... can you add soemthing like

Please Login or Register  to view this content.

???
Attached is the hyperlink manually added...
CCR2a1a.xlsm

Originally Posted by Legend Rubber

I dont know user forms that well... but for your hyperlink problem... can you add soemthing like

Please Login or Register  to view this content.

???
Attached is the hyperlink manually added...
Attachment 199319

Ok, well this is starting to go somewhere, it uses a simple rowcount.

Please Login or Register  to view this content.

unfortunately i am not sure exactly how to tie the two together...
i am sure there is a way to add the hyperlink to the CCR# each time a new one is created... but not sure how...
I will try a few things...

Tell me if this does most of what else you need...
CCR2a1aComplete.xlsm
I still can not figure out the Hyperlink...

To move the macros, just copy and paste the VBA code from Module 2 and Module 3 to Modules in your Master...
Right now, the file saves where ever the Master is located...
You will need to have the named range "FOLDER" present for this code to work...
if you tell me the folder location you want it to save to i can change it for you...

Just copy

Please Login or Register  to view this content.

to somewhere in the Master workbook and name the range it is located in "FOLDER"... this will always update to whereever the file is located...
or tell me if you dont think that will work and i will change it to C:\CCR\

Originally Posted by Legend Rubber

Just copy

Please Login or Register  to view this content.

to somewhere in the Master workbook and name the range it is located in "FOLDER"... this will always update to whereever the file is located...
or tell me if you dont think that will work and i will change it to C:\CCR\

Still getting a error.

I have included my master book, please take a look and tell me what I did wrong. You can also see the hyperlink formula on sheet 1

I do have on last question, how do you take a formula and add it to every cell in the column ?

Simple, you just have not named the range of the =LEFT...etc to "FOLDER"
CCR2a1.xlsm

Thanks for that, I do have another small request. Is there a way then when saving or printing as a .pdf it only saves or prints page one of sheet 4. I do not need the button seen on the .pdf document.

Thanks

CCR2a1.xlsm
Just add , From:=1, To:=1 at the end of the ExportAsFixedFormat in the PDF Code...

Wonderful, Thank you !!

All your help has been great with my little project. If this works I have two other forms to convert in a similar manner.

You had the "FOLDER" naming the wrong range...
You neen to name the cell that contains the formula...

I have done it...
CCR2a1.xlsm

Dang, I'll never get my head around that !?!

Thanks so much for the updated file.