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

 

[angiemeh]

Did it select the whole sheet or just the area where there is data - which is your dataset.

If it just highlighted the area with the dataset then the mission was successful and we can move on. Let me know.

 

[ChompGator]

Hey there,

Nope it selected the whole sheet

 

[angiemeh]

Are you on Excel 2003 or what version do you have?
If you hit ctrl + shift + end you should only select your dataset. So it selected all the columns? Do you have something in the columns to the right?

Did you try the shift+ end + arrow option? Try that one and let me know what it does. (I listed this before the ctrl + shift + end option.

 

[ChompGator]

Hey,

Yep I tried the arrows as well, and nothing...Excel Version is 2003.

If its just a matter of selecting the data, I can select it just by highlighting it cant I? Because the two suggested ways are selecting the dataset, but 500 additional blank lines as well.

 

[angiemeh]

Well... that is what I was trying to get around. The whole Idea behind doing this is to create a macro that automates the steps. You Don't want to manually have to go in there and select the cells every single time. If you had 10 rows and then the user adds a row, you want the formats to go to row 11 now because there is an extra row.

We were going to record this process in the macro when copying the formats. The only other options are to do this with VBA code or have the formats go all the way to 500...

You can try deleting all the rows under the data and try again to see if that would work. Otherwise, let me know if your OK with having the formats go down that far.

 

[ChompGator]

Hmm, I tried that - and now Ive gotten it to select the dataset...

So now we can move on

 

[angiemeh]

YAY

Before we go any further.
Where do your row numbers start? Will there or should there always be data exactly to the right of the row number.

So if your row number 1 is in cell A2, will there be data in Cell B2?

 

[ChompGator]

Absolutely, the data will always be to the right of the row number.

- Jeff

 

[angiemeh]

Follow these steps:

With your row numbers (they are numbered 1,2,3,,,, etc) Replace the #2 with a formula:
=if(b3="","",a2+1)

This Assumes that Row 1 is the header row, row 2 is the 1st set of data and row 3 is the 2nd set of data. If it is differnt, adjust the formula accordingly, but put the formula in the cell where the # 2 should appear.

-When you number your rows have 1,2,3 and don't put a # sign or anything else by it.

1) Create your button or macro and start recording.
2) After you hit record, do the following steps.
3) Select the cell that you just put the formula in (wherever the number 2 appears)
4) Hit Ctrl + C to copy
5) Select the cell directly below the cell that you copied
6) Hit Shift (keep holding Shift) then Page Down (keep holding shift & take finger off page down) then hit end (keep holding shift and take finger off end) then hit up arrow. This should select the entire column that has numbers in it (nothing else)
7) Right click and select Paste-Special then the radio button 'Formulas'
8) then Select the entire row that has the formatting on it (this should be row 2, assuming the 1st row is headers). Hit Ctrl + C to copy the entire row
9) Then select the cell to the left most right below the row you copied. So if you copied row 2, you would select A3.
10) Hold the Ctrl key down and then keep holding and hit shift and keep holding and hit End. This will select all your data
11) Right click and choose Paste-Special then the radio button 'Formats'
12) Hit escape key and then select cell A1
13) Hit stop record.

Now run your macro (either by pressing button or the ctrl key you created).
Try adding a row and putting a value in column B (but no row number ) and try hitting the button.

 

[ChompGator]

a few questions about this formula so I dont screw it up:

=if(b3="","",a2+1)

Do I put anything between the quotation marks?
And after inserting the formula (Number 1 starts at A6) Excel did not want to accept it...I changed it to:
=if(a6="","",a6+1)

Ill keep playing, let me know your thoughts