Making lookup data fixed/static 1

[leewisener]

I have one more question in relation to a spreadsheet I am working on.

Basically I have a number of sheets called Jan..Dec 12 of them obviously.

I have added another sheet to the spreadsheet which contains data relating to the person who should deal with the account and I needed to populate all entries in sheets Jan..Dec.

So what I have done is something like this.

=VLOOKUP(A5,account!A:B,2,FALSE)

So now that all my fields are up to date in the Jan..Dec fields I dont need the sheet called account, but of course if I delete it then I will loose all the updates in the Jan..Dec sheets.

I am sure there must be a way of fixing the data in the cells I have just updated so I can delete the account sheet, can someone tell me how to do it.

Thanks

Lee

 

[SkipVought]

If I understand you correctly,

SELECT the data with the formula references

Edit/COPY

Edit/Paste Special - VALUES

Now you have VALUES instead of FORMULAS.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[AnotherHiggins]

Copy, Paste Special > Values.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

John,

A day late and a dollar short AGAIN!

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

Simply copy your formulae and then Edit>Paste Special>Values over the top

On a related note though, you are going to give yourself all kinds of headaches if you insist on chopping up your data by month. You would be far better off keeping all the data on 1 sheet with an extra column to indicate the month - Data stored in this way is far far far far far far (you get the idea) easier to aggregate and report on than data that is kept in seperate worksheets.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Darn you and your nimble fingers, Skip.

But for the record, I wasn't a day late - it was more like 2 seconds. *sigh*

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Lee,

DITTO for Geoff's "headache" comments. I was about to add my BTW, but Geoff has stated it well.

echo -- far, far, far, far.........

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

....and if I hadn't bothered with that, I would've been 1st out of 3 instead of 3rd... ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

riiiiiiight

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

However, Geoff, you posted the MOST HELPFUL comment in the long run, and as a good egg....

but then, who likes long runny eggs? ;-)

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[leewisener]

Thanks for you comments guys, it's done the job perfectly.

I understand the comments about the layout of the data, however the report is in the format one of my Presidents wants it!

Anyway, wont say anymore than that, he might be about

 

[SkipVought]

UNDERSTAND that...

there is almost ALWAYS a big difference between the way that data ought to be STORED (as Geoff has explained) and the way that data is REPORTED.

If data is STORED properly, it makes ANALYSIS and REPORTING (for instance, in the format requested by your President) far, far, far, far easier.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

2nd that - Mr President doesn't need to know how the data is stored - he just wants an output. As long as you can gife hime a monthly sheet, what difference does it make if it is all stored on 1 sheet or broken up into lots of sheets? The only difference is how much quicker you will be able to give him the Year To Date figures and any extra reporting requirements etc if you DON'T chop up all the data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question