Retrieving Data Using Excel

[ChompGator]

Hey,

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

What kind of formula do I use for this?

- Jeff

 

[ChompGator]

Ok, so (Im trying this out)

And Im up to step two

1) Find a Button on the tool bar. If you don't have it, go to view-tool bars -forms
2) Click on the button and hold your left mouse key down and drag it to the white space of your excel file- you should get an option to record. Try it, hit record


I do step two, and a button box appears and I get a dialouge box with one of the options as being 'record' - I try to record the dialoge box just disappears and there is a big grey area on the cells I high-lighted.

Is this normal?

 

[angiemeh]

When you hit record, it should bring up another dialog box and you hit OK, then you should see your screen as normal and there will be a little tiny window/box on your screen with a square button on it. You click this when you are done recording.

So, you tell Excel to start recording when you hit record and then you do your thing and Excel records it, then you stop recording when your done. Next time you hit the button it will do the same thing.

If you can't get this to work do this:
1) Go to tools- macro- record macro
2) There will be a ctrl+ then a blank. Put a value here like a
Then follow steps 3-8.

Then when you hit ctrl + a it will run the macro.

 

[ChompGator]

Hey,

Whats happening is - its showing a big button, and there is the tools menu you mentioned to click 'Stop Recording' but the excel sheet has a big button on it hah, Im going to play with it this morning, and we can proceed with this project after I figure these initial steps out you gave me...Ill be in touch in a while.

Thanks,

 

[ChompGator]

Ok, I got the recording thing to work,

Im now at the step where, "Im selecting `paste special` and radio button formats"

I dont have that option on my `past special dialouge` - the options are as follows:
All
Formula's
Values
Formats
Comments
Validation
All Except Borders
Column Widths
Formula's and Number Formats
Values And Number Formats

Any other way to do what you suggested?

thanks,
Jeff

 

[angiemeh]

You hit paste special then radio button formats. Read # 4 in previous steps. You should have that option. This option doesn't paste anything other than the formats, so the values won't change, but your format that you copied will be pasted to all other cells you highlighted.

 

[angiemeh]

You just listed you have Formats. Each option you see is a radio button. Then you choose Formats.

 

[ChompGator]

Ok, so that is done, Im framiliar with how to copy formatting...

Now what is the next step to manipulate the code so its always pulling data dynamically (as you mentioned at the end of your step-by-step instructions for me)

- Let me know, thanks again for all the help its proving to be very, very useful

 

[angiemeh]

1st you need to learn how to use Shift + End + Arrows. Practice this and then let me know when you've suceeded and I'll show you how.

We could also do this using VBA, but since your not familar with VBA, I think this will be easier for you.

There are multiple ways to select the data- but we'll use the Shift+ end + arrows. The key is that your data has to be continuous, therefore you can't have 1,2,3, blank, 5 etc.

Go to the leftmost area of your data set. Put your curser on cell A1 (if A1 is in your dataset). Hold the Shift key down, keep holding it down and hit End. Keep holding shift, but take your finger off End and hit the down arrow. Keep your finger on shift take your finger off the down arrow and hit end. Keep holding shift and take your finger off end and hit the right arrow.

THis should select all your data. Let me know when you can do this.

 

[angiemeh]

Actually, this is easier- sorry . Try this and then let me know

go to the left most area of your dataset. Hit Ctrl + Shift + end (when you hit ctrl, keep holing ctrl then hit shift and keep holding ctrl and shift and then hit end)

 

[ChompGator]

Hey there,

All that did was select my whole sheet and take me down to line 565
hmm