locking vlookup formulas

[Guest]

I know how to lock cells... my question is:

I have vlookup formulas in a document that I emailed from work to my home
email address. At hoem, the formula now includes the temp email file
location.

I need to be able to send files home and then back to work without having
the vlookup formulas change.

help please... jane

 

[Guest]

I'm not sure what process you're using to email and open the files.

I usually send my files to myself via email by first starting an email and
then using Insert | File and attach the .xls file. When I get the email at
the other end, I simply open the email and use Save Attachments to save them
to a location on the computer at the receiving end. No formulas get changed
for me.

Ever consider just getting a little USB thumb drive to haul them back and
forth on?

 

[Guest]

I email as you do. I do have a jump drive but I access my work files on my
laptop at home using VPN. The odd thing is that if I insert a jump drive in
my laptop, I can not access that drive since I am accessing my work's system.
any ieas on that angle?

 

[Guest]

Sorry, no help there - I don't use VPN and am totally unfamiliar with its
limitations. But since, in effect, you are using the system at your office I
can kind of see why it wouldn't see a USB device on your home system. I'm
wondering if you couldn't try mapping your local machine in by adding it as a
network location -- but perhaps not. Just spit-balling, as I said, I'm
pretty much totally ignorant of VPN use/features and work-arounds.

I'm wondering, on the email thing, what format you send your email as; HTML,
RTF or plain text. I do all of mine as plain text to keep from embedding
files within the body of the email and so they are always just an attachment,
not any part of the email itself.

If the problem continues to be a problem, you might ask about it over in the
Outlook group - they may be more knowledgable about that strange behavior
than we generally are here in the Excel groups?

 

[Guest]

Mine is set to send dusing HTML - maybe I will try plain text also. While I
have you for a moment - any chance you coul help me with this other problem?
I posted Friday night but haven't gotten a response. Thinking I shoudl post
again since the weekend is over but knwo that sort of breaks the rules.
Desparate for a solution tho'. MInd taing a look?

Data:
A B C D E F G H
Season
Ratio
Jan. Feb. Mar. Apr. May Jun avg. to Ssn avg.
182.83 804.25 423.68 263.66 388.32 319.83 2944.66 x
210.09 427.43 380.02 1941.79 x
0 0 518.18 411.43 415.57 251.04 3343.16 x

Facts:
- Columns A - F = store sales
- Column G row 1 = COMPANY average of store's averages below
- Columns G row 2-4 = STORE averages of months in columns A - F
- Each store is coded "NC" and they are among other stores that are coded
"COMP" and not shown here.
- Column H needs to = ratio of STORE'S sales average to COMPANY average -
with a catch


I need a formula that says
if the store is coded NC, calculate the ratio to COMPANY average - teh
COMPANY average must be an average that 1) sums the Company averages for
months in which the STORE's monthly data is >0 and 2) starts at the 2nd month
in which there is STORE data.

ie
For store 1, the Company average would include Feb - Jun
Fro store 2, the Company average would include May - Jun
For store 3, the Company average would include Apr - Jun

thank you in advance! Jane

 

[Guest]

I've looked at that original post a couple of times (wondered if it was
yours). Is there any chance you could send me a copy of the workbook? Try
(attached to plain text email) to HelpFrom @ JLathamsite.com (remove spaces).
I'm not understanding how the "NC" and "COMP" figure into this. Is that
info on a sheet name, in a cell somewhere or what? But over all, I'm
thinking that seeing the book would really help with it. Most difficult part
(I think) will be determining 2nd month with values and I think I've got
something laying around here that kind of does that, just have to find it.
Also, will you continue to insert months as the year progresses? Or is it
always 6 months worth of entries Jan-Jun, Jul-Dec?

JLatham

 

[Guest]

I will send and appreciate you looking at it.

The NC means the stores have limited sales history because of the month they
opened in the previous year and that is reflected in the data shown in the
example.

COMP means the stores have sales for the previous year- also shown in the
example

This indicator is provided for the user.

The user may pull a year's worth of data or a Season (6 fiscal months).
Once it is pulled, it is not added on to.

hope that helps to clarify.... sending spreadsheet in the next few minutes.

 

[Guest]

Jane - file arrived.

I think the missing piece has been provided - that "COMP", "NC" is out there
in column P. I'll get back with you on it.