Am I able to 'fix' the results of a formula into a cell?

[gklrgibson]

Hello
I was hoping someone far more capable than I could help me with an
Excel 2002 problem.

I am using an IF formula:
=IF('EXPECTED WORKLOAD'!$G$51=$A8,'EXPECTED WORKLOAD'!G$163,"")
to check for a certain number (a week number) on another worksheet
(EXPECTED WORKLOAD), if it is found, a value (the number of hours
spent on a task) is placed into the cell. The next row does the same
thing:
=IF('EXPECTED WORKLOAD'!$G$51=$A9,'EXPECTED WORKLOAD'!G$163,"") but is
looking for the next weeks number. The aim is to have a history of
data that can be used to analyse performance (at the moment this list
is used in a pivot chart).
The problem I am having is that whenever the week changes in the
EXPECTED WORKLOAD sheet the original entry disappears i.e. is replaced
with the "" part of the IF statement.
I have got round this by manually over-typing the formula with the
value but I'm sure there must be a better way of recording the
history.

(I hope this makes some sort of sense to you)

Any help you can give would be greatly appreciated and thanks in
advance.

gklr

 

[Pete_UK]

It is probably a good idea to put an apostrophe in front of the = sign
of your first formula, then you can copy it (as text) to a blank cell
in your sheet, so that you can subsequently copy it back and get rid
of the apostrophe when you want to work on next week's data.

Highlight the cells with the formulae in, click <copy>, then Edit |
Paste Special | Values (check) | OK then <Enter>.

Obviously, your formulae have gone, which explains my comments in the
first paragraph.

Hope this helps.

Pete

 

[gklrgibson]

Pete

Thanks for your input.
I think I may not have given you enough info (or I'm not clear in what
your suggesting!)

I'm looking to build up a week by week history (eventually building up
to a full year) showing the number of hours against certain types of
work.
That info is built up on a seperate worksheet and renewed each week (a
little like a time sheet).
I am trying to send that weeks total hours into anther sheet to be
recorded.
7 days later new data will be used in the timesheet and I want to
record that new data on the next line of my History sheet.

Sorry if I didn't make myself clear (I was at home and it was well
past my bed time...)

Thanks again for any further help.

 

[Pete_UK]

A slight variation, then:

Highlight the cells with the formulae in, click <copy>, then click on
the tab of the destination sheet and on the first cell where you want
the fixed values to appear, then Edit | Paste Special | Values (check)
| OK then <Esc>.

The original sheet remains unchanged, so you can carry on using it as
you have been doing. Next week you will paste into the adjacent column
of the destination sheet, and so on.

Hope this helps.

Pete