'Pulling through' part of a formula to use in another sheet

[raehippychick]

I am not a power user of excel; although I do know the basics I have ye
to encounter LOOKUPS, VB, etc... and I am blonde!!!

I have a spreadsheet with 45 sheets - one for each of a "Job" that ha
a number of "Tasks" (up to 30 in any one "Job") to complete it

One on sheet I have a table of numbers that will change as time goes o
as they will affect the relationships of tasks, time, cost and ma
hours

On the same sheet is a table of formulae that work with these number
which indicates how much time each person will spend on each "Task" b
using a 'Key' number (which will differ for each task on each Jo
sheet) in one cell. The cells in this second table multiply/divide et
with the key number and the numbers from the first (figures only) tabl


What I would like to be able to do is to 'pull through' these formula
and use them in each of the 45 sheets where the key number will b
different for each task

This is so that should these formulae change they would only need to b
updated on the one sheet and then roll through all the other sheets

Is it possible to do this please?

I have tried copying & pasting in every way I can think of includin
paste link, but no matter what I do when I change the cell that has th
Key number in it the rest of the cells do not alter

I think basically what I would like to do be able to use th
relationship of the formulae I the data entry sheet against the ke
number in the Job sheet for each sheet

e.g. on the data entry sheet a cell may have :

=(B30*(DATAENTRYSHEET!$B$3))I am not a power user of excel; although
do know the basics I have yet to encounter LOOKUPS, VB, etc...

with B30 being the cell containing the key number. But when I copy i
to the Job sheet it still looks for B30 on DATAENTRYSHEET - but I wan
it to look for =(JOBSHEET!B30+JOBSHEET!B30)*(DATAENTRYSHEET!$B$3))

If I paste link I just get =DATAENTRYSHEET!$B$29

I hope this makes sense to somebody as I am finding it really hard t
describe!

[The reason that I need to do this on all 45 sheets is so that the cos
and time of each and every "Job" can seen with all the others]

Any help at all would very, very appreciated

 

[Jim Rech]

Well, you confused me.<g> But we probably can work it through.

If a formula has no sheet reference in it, like =$A$1, then it refers to the
sheet it is on. If you copy it to another sheet, it will reference A1 on
that new sheet.

If the formula has a sheet reference, like =Sheet1!$A$1, then it will
reference the A1 on Sheet1 no matter where it is copied. The sheet
reference is always "hard wired".

I'm not sure that was your question or problem but for what it's worth...

Now, as to "pulling through part of a formula". A formula always returns a
"value". No matter what is in the cells that the formula references
(values, complex formulas, etc.) the formula itself just returns a value.
No part of what is in the referenced cells remains. Again, not sure this is
helpful either...

--
Jim Rech
Excel MVP

message |I am not a power user of excel; although I do know the basics I have yet
| to encounter LOOKUPS, VB, etc... and I am blonde!!!
|
| I have a spreadsheet with 45 sheets - one for each of a "Job" that has
| a number of "Tasks" (up to 30 in any one "Job") to complete it
|
| One on sheet I have a table of numbers that will change as time goes on
| as they will affect the relationships of tasks, time, cost and man
| hours
|
| On the same sheet is a table of formulae that work with these numbers
| which indicates how much time each person will spend on each "Task" by
| using a 'Key' number (which will differ for each task on each Job
| sheet) in one cell. The cells in this second table multiply/divide etc
| with the key number and the numbers from the first (figures only) table
|
|
| What I would like to be able to do is to 'pull through' these formulae
| and use them in each of the 45 sheets where the key number will be
| different for each task
|
| This is so that should these formulae change they would only need to be
| updated on the one sheet and then roll through all the other sheets
|
| Is it possible to do this please?
|
| I have tried copying & pasting in every way I can think of including
| paste link, but no matter what I do when I change the cell that has the
| Key number in it the rest of the cells do not alter
|
| I think basically what I would like to do be able to use the
| relationship of the formulae I the data entry sheet against the key
| number in the Job sheet for each sheet
|
| e.g. on the data entry sheet a cell may have :
|
| =(B30*(DATAENTRYSHEET!$B$3))I am not a power user of excel; although I
| do know the basics I have yet to encounter LOOKUPS, VB, etc...
|
| with B30 being the cell containing the key number. But when I copy it
| to the Job sheet it still looks for B30 on DATAENTRYSHEET - but I want
| it to look for =(JOBSHEET!B30+JOBSHEET!B30)*(DATAENTRYSHEET!$B$3))
|
| If I paste link I just get =DATAENTRYSHEET!$B$29
|
| I hope this makes sense to somebody as I am finding it really hard to
| describe!
|
| [The reason that I need to do this on all 45 sheets is so that the cost
| and time of each and every "Job" can seen with all the others]
|
| Any help at all would very, very appreciated!
|
|
| ---
| Message posted
|

 

[raehippychick]

Oh - I thought it might be confusing, but thank you for your reply!

I have these 45 sheets each with up to 30 tasks and each task has 1
people/work units

Basically for each task (and there potentially 1,350 of them!) I have
table 25 columns wide and 10 rows deep one cell of which is a ke
number (which is pulled from the list of tasks on a separate sheet)

The remaining cells in the sheet are calculations based on this ke
number and figures that I keep in a table the same size and shape a
these figures change regularly - this is kept on a separate shee
called 'data entry'

So a cell in the formulae table could be something like add the thre
cells above and multiple by the key number and then multiply the resul
by the figure from he table above

So one formula would be =(SUM(C15:C17 )*M5)*C7 (M5 being the ke
number)

If I copy the table into each sheet (up to 30 times per sheet) thi
works fine when I have updated the key number cell to find the righ
number in the task list sheet.

This way if the key number changes the user accesses one sheet and i
the figures in the numbers table change they only access one sheet fo
that

The C7 bit is fine as that goes looking for the number on the 'dat
entry' sheet and the key number is fine too. However if they want t
SUM from C14 or divide by the key number it means they have to updat
45 sheets and up to 30 task in each sheet

What I would like to be able to is for them to change the C15 bit t
C14 (or the multiply to a divide) in the one 'data entry' sheet and fo
all the sheets to update their formulae

I have a horrible feeling the only way would be with a macro - which
don't know enough about yet to do!

*Well, you confused me.<g> But we probably can work it through.

If a formula has no sheet reference in it, like =$A$1, then it refer
to the
sheet it is on. If you copy it to another sheet, it will referenc
A1 on
that new sheet.

If the formula has a sheet reference, like =Sheet1!$A$1, then i
will
reference the A1 on Sheet1 no matter where it is copied. The sheet
reference is always "hard wired".

I'm not sure that was your question or problem but for what it'
worth...

Now, as to "pulling through part of a formula". A formula alway
returns a
"value". No matter what is in the cells that the formula references
(values, complex formulas, etc.) the formula itself just returns
value.
No part of what is in the referenced cells remains. Again, not sur
this is
helpful either...

--
Jim Rech
Excel MVP

"raehippychick >" <<[email protected]> wrot
in
message |I am not a power user of excel; although I do know the basics I hav
yet
| to encounter LOOKUPS, VB, etc... and I am blonde!!!
|
| I have a spreadsheet with 45 sheets - one for each of a "Job" tha
has
| a number of "Tasks" (up to 30 in any one "Job") to complete it
|
| One on sheet I have a table of numbers that will change as tim
goes on
| as they will affect the relationships of tasks, time, cost and man
| hours
|
| On the same sheet is a table of formulae that work with thes
numbers
| which indicates how much time each person will spend on each "Task
by
| using a 'Key' number (which will differ for each task on each Job
| sheet) in one cell. The cells in this second table multiply/divid
etc
| with the key number and the numbers from the first (figures only
table
|
|
| What I would like to be able to do is to 'pull through' thes
formulae
| and use them in each of the 45 sheets where the key number will be
| different for each task
|
| This is so that should these formulae change they would only nee
to be
| updated on the one sheet and then roll through all the othe
sheets
|
| Is it possible to do this please?
|
| I have tried copying & pasting in every way I can think o
including
| paste link, but no matter what I do when I change the cell that ha
the
| Key number in it the rest of the cells do not alter
|
| I think basically what I would like to do be able to use the
| relationship of the formulae I the data entry sheet against the
key
| number in the Job sheet for each sheet
|
| e.g. on the data entry sheet a cell may have :
|
| =(B30*(DATAENTRYSHEET!$B$3))I am not a power user of excel;
although I
| do know the basics I have yet to encounter LOOKUPS, VB, etc...
|
| with B30 being the cell containing the key number. But when I copy
it
| to the Job sheet it still looks for B30 on DATAENTRYSHEET - but I
want
| it to look for =(JOBSHEET!B30+JOBSHEET!B30)*(DATAENTRYSHEET!$B$3))
|
| If I paste link I just get =DATAENTRYSHEET!$B$29
|
| I hope this makes sense to somebody as I am finding it really hard
to
| describe!
|
| [The reason that I need to do this on all 45 sheets is so that the
cost
| and time of each and every "Job" can seen with all the others]
|
| Any help at all would very, very appreciated!
|
|
| ---
| Message posted
| *

 

[otium]

Assuming that your basic problem is that youe don't want to repeat
formula change 45 times !!!

A simple way of getting around this is by using groups.

Click on the first sheet tab at the bottom and then hold the Shift ke
and select the other 44 - it is actually VERY quick to do once you ge
the hang of it because once you click on the sheet that is furthes
right (of the ones in view) you can just keep clicking in the sam
location because Excel automaticaaly moves the tabs along by one.

Once you have selected all the sheets, make the change in the firs
sheet - it will make the same change in ALL selected sheets.

WARNING - make sure you select an individual sheet without the Shif
key before you make any data changes.

Hope this help

 

[raehippychick]

Oh thank you - that will help

It isn't qquite what Iwas after, but way better than the old way the
were doing it before they gave it all to me!

My main problem will still be having to update a table 25 cols by 1
rows 30 times!

I wanted to just update the table (25 wide * 10 deep) of formulae an
that to be sucked automaticalluy into all 30 instances of it in tha
are in each of the 45 sheets

But this is definitely better than the enitirely manual way of havin
to do it possibly 1,350 times!

Thanks

 

[Jim Rech]

I'm picturing a block of cells 10 rows by 25 columns, repeated up to 30
times on a sheet. And 45 such sheets. Are the formulas in each block the
same? I mean, if you copy any block on top of any other block, will it
work?

If so, why is it necessary to have all of these blocks present at the same
time? Can you not have one block that does the right calculation depending
on the "key number" input?

--
Jim Rech
Excel MVP
message | Oh - I thought it might be confusing, but thank you for your reply!
|
| I have these 45 sheets each with up to 30 tasks and each task has 10
| people/work units
|
| Basically for each task (and there potentially 1,350 of them!) I have a
| table 25 columns wide and 10 rows deep one cell of which is a key
| number (which is pulled from the list of tasks on a separate sheet)
|
| The remaining cells in the sheet are calculations based on this key
| number and figures that I keep in a table the same size and shape as
| these figures change regularly - this is kept on a separate sheet
| called 'data entry'
|
| So a cell in the formulae table could be something like add the three
| cells above and multiple by the key number and then multiply the result
| by the figure from he table above
|
| So one formula would be =(SUM(C15:C17 )*M5)*C7 (M5 being the key
| number)
|
| If I copy the table into each sheet (up to 30 times per sheet) this
| works fine when I have updated the key number cell to find the right
| number in the task list sheet.
|
| This way if the key number changes the user accesses one sheet and if
| the figures in the numbers table change they only access one sheet for
| that
|
| The C7 bit is fine as that goes looking for the number on the 'data
| entry' sheet and the key number is fine too. However if they want to
| SUM from C14 or divide by the key number it means they have to update
| 45 sheets and up to 30 task in each sheet
|
| What I would like to be able to is for them to change the C15 bit to
| C14 (or the multiply to a divide) in the one 'data entry' sheet and for
| all the sheets to update their formulae
|
| I have a horrible feeling the only way would be with a macro - which I
| don't know enough about yet to do!
|
| Jim Rech wrote:
| > *Well, you confused me.<g> But we probably can work it through.
| >
| > If a formula has no sheet reference in it, like =$A$1, then it refers
| > to the
| > sheet it is on. If you copy it to another sheet, it will reference
| > A1 on
| > that new sheet.
| >
| > If the formula has a sheet reference, like =Sheet1!$A$1, then it
| > will
| > reference the A1 on Sheet1 no matter where it is copied. The sheet
| > reference is always "hard wired".
| >
| > I'm not sure that was your question or problem but for what it's
| > worth...
| >
| > Now, as to "pulling through part of a formula". A formula always
| > returns a
| > "value". No matter what is in the cells that the formula references
| > (values, complex formulas, etc.) the formula itself just returns a
| > value.
| > No part of what is in the referenced cells remains. Again, not sure
| > this is
| > helpful either...
| >
| > --
| > Jim Rech
| > Excel MVP
| >
| > in
| > message | > |I am not a power user of excel; although I do know the basics I have
| > yet
| > | to encounter LOOKUPS, VB, etc... and I am blonde!!!
| > |
| > | I have a spreadsheet with 45 sheets - one for each of a "Job" that
| > has
| > | a number of "Tasks" (up to 30 in any one "Job") to complete it
| > |
| > | One on sheet I have a table of numbers that will change as time
| > goes on
| > | as they will affect the relationships of tasks, time, cost and man
| > | hours
| > |
| > | On the same sheet is a table of formulae that work with these
| > numbers
| > | which indicates how much time each person will spend on each "Task"
| > by
| > | using a 'Key' number (which will differ for each task on each Job
| > | sheet) in one cell. The cells in this second table multiply/divide
| > etc
| > | with the key number and the numbers from the first (figures only)
| > table
| > |
| > |
| > | What I would like to be able to do is to 'pull through' these
| > formulae
| > | and use them in each of the 45 sheets where the key number will be
| > | different for each task
| > |
| > | This is so that should these formulae change they would only need
| > to be
| > | updated on the one sheet and then roll through all the other
| > sheets
| > |
| > | Is it possible to do this please?
| > |
| > | I have tried copying & pasting in every way I can think of
| > including
| > | paste link, but no matter what I do when I change the cell that has
| > the
| > | Key number in it the rest of the cells do not alter
| > |
| > | I think basically what I would like to do be able to use the
| > | relationship of the formulae I the data entry sheet against the
| > key
| > | number in the Job sheet for each sheet
| > |
| > | e.g. on the data entry sheet a cell may have :
| > |
| > | =(B30*(DATAENTRYSHEET!$B$3))I am not a power user of excel;
| > although I
| > | do know the basics I have yet to encounter LOOKUPS, VB, etc...
| > |
| > | with B30 being the cell containing the key number. But when I copy
| > it
| > | to the Job sheet it still looks for B30 on DATAENTRYSHEET - but I
| > want
| > | it to look for =(JOBSHEET!B30+JOBSHEET!B30)*(DATAENTRYSHEET!$B$3))
| > |
| > | If I paste link I just get =DATAENTRYSHEET!$B$29
| > |
| > | I hope this makes sense to somebody as I am finding it really hard
| > to
| > | describe!
| > |
| > | [The reason that I need to do this on all 45 sheets is so that the
| > cost
| > | and time of each and every "Job" can seen with all the others]
| > |
| > | Any help at all would very, very appreciated!
| > |
| > |
| > | ---
| > | Message posted
| > | *
|
|
| ---
| Message posted
|

 

[raehippychick]

Hi

each 'block' relates to a task (up to 30 per job) and each block has a
different key number - hence the need for 45 pages of 30 blocks!

I need to keep 30 blocks as the jobs change and more tasks may be added
to the list and the sheets need to be printed off for meetings

I think I may need to go back to the people who need it and ask more
questions and suggest a complete re-deisgn from scratch!

R