Need to speed up process...

[y34r1ght]

Im fairly new to Excel but can't seem to figure out how to approach this.

Here's what I'm dealing with:

My company has a remote excel doc I work with about once a week..
Since it's remote its painfully slow using PC anywhere...

Anyhow,

Column A (labeled 'Cartridges') consists of numbers.. approx 1400 - 5000.
(Even though Column A begins with #1400 or so, there are some numbers that are missing that have been removed from the list.)

Column D (labeled 'Empty date') is what I fill in with the date I reported
them empty... 95% the same date of all of them.

I most of the time wait until I have about 100 or so numbers to do and then sit down for an hour and Ctrl+V the date into each one..

How can I simplify this? I was thinking of a way to type in all my Column A numbers, specify what date for Column D, and click change. (without changing any of the other data, of course.)

Any ideas to save time would be greatly appreciated!

Thanks,

will.

[Carim]

Hi,

To enter same data into multiple cells :
1. highlight your range
2. type your input
3. control+shift+enter

HTH
Carim

[y34r1ght]

Problem is, I have no range ... they are random numbers.

1435, 1752, 1201, and so on and so on ..

[Carim]

Hi again ...

In order to select you range ...
1. Keep Control key pressed
2. Click on each cell

HTH
Carim

[y34r1ght]

Yea, I could do that --- but it would still take the same amount of time, since once Im at the correct number it would only take half a second to jump over to Column D and Ctrl+V in the date....

Im trying to eliminate the need to scroll thru the entire list back and forth for my numbers.


Thanks tho!

[Carim]

Hi again,

Have you tried F5 Special blanks ...?

HTH
Carim

[y34r1ght]

We're getting warmer with the F5 idea, although I only do about 100-150 numbers each week, so there are still many numbers (couple thousand) that will still have blanks for later dates.. not to mention Columns B, C, and E will normally have blanks until I input the date on Column D.

[Dean England]

I also have to deal with pcanywhere and I agree that it is frightfully slow. What I do is not an excel solution, but it works for me. I download the file from the remote computer using file transfer, do whatever it is that I have to do and then transfer the updated file back to the remote computer. Don't know if it will work for you, but, as I said, it's just a thought.

Have a nice day,

Dean

[y34r1ght]

Possible solution there i'll admit...... Although problems could occur if someome @ home office tries to make changes while I have a local copy then upload it, overwriting their changes.

sorry if i'm being difficult

[y34r1ght]

edit >>

Idea

Im thinking for some reason (and again I remind you, im an excel newb) ..
that if some way I could make an additional sheet, input all the numbers im working with and the dates and have that sheet make the changes for me .. ?

Maybe add an "If blank" "then enter" date formula, otherwise leave alone (because 2% of the time my numbers already have dates on them from other changes).

[tuph]

I was thinking of a way to type in all my Column A numbers, specify what date for Column D, and click change. (without changing any of the other data, of course.)

Would this work for you? Add a helper worksheet where you type the cartidge numbers into column A and the date reported into column B. Then, put a VLOOKUP formula in column D of your primary worksheet to look up the date reported for each cartridge number.

(In the helper worksheet you would insert each new set of dates at the top of the list. Why the top? Because VLOOKUP returns the first instance of a match, and you would want it to return the latest data in your primary worksheet.)

Hope this helps!

[y34r1ght]

Ok ... I'm not sure if I completely follow what youre saying .. and cant come remotely close to making something like that, lol.

Now one thing i wanted to make sure you were aware of ---

On the remote document, the Cartridge #s are already there, I do not enter them.

[tuph]

On the remote document, the Cartridge #s are already there, I do not enter them.

I got that.

Attached is a sample file. It contains an "actual data" page which simulates the existing worksheet in your file. The second worksheet is named "Date Lookup" and is intended as the data entry page. On the second page you would enter each of the cartridge numbers you need to report as empty in column A and the report date in column B.

In column D of the actual data worksheet you will find this formula:

=IF(ISNA(VLOOKUP($A5,'Date Lookup'!$A$2:$B$34,2,FALSE)),"",VLOOKUP($A5,'Date Lookup'!$A$2:$B$34,2,FALSE))

VLOOKUP($A5,'Date Lookup'!$A$2:$B$34,2,FALSE) looks up the cartridge number in cell A5 and matches it with the contents of column A in the data entry worksheet, then returns the contents of the second column in the lookup array (A2:B34) which is where you have entered the report date.

The additional function of IF(ISNA checks the first instance of the VLOOKUP function and if there is no match will leave the cell in the actual data page blank. If a match is found, the second instance of the VLOOKUP formula will be executed.

If you don't want your users to see the data entry worksheet it can be hidden.

P.S. I strongly suggest you take a copy of the workbook and save it locally, so you can work without being intimidated by the thought of screwing things up for other users.

[mudraker]

If you keep a record of the items to be updated in another workbook until you update them then a macro could be used to make the amendments.

You will need both books open and this macro in the workbook with your list of items to be updated

You will need to change workbook and worksheet names as required

Please Login or Register  to view this content.