Help with linked formulas & multiple worksheets

[Matt]

Frustrated here.

I have a workbook containing 3 worksheets.

Sheet1 lets a user enter all leads received.
Sheet2 has links to Sheet1 and provides a running total (based on dates) of
projected closed sales for each month of the year and is based on the info
entered into Sheet1.
Sheet3 is a 'Cancelled' worksheet where the user "moves" all leads entered
on Sheet1 that fall through or are "Cancelled" (cut & paste).

My problem is that when I cut and paste from Sheet1 to Sheet3, the linked
formula in Sheet2 updates itself to use the Sheet3 data instead of the Sheet1
data. (the row that is now blank on Sheet1 doesn't erase Sheet2's data.
Basically I need Sheet2's reference to remain intact so that lead that is no
longer on Sheet1 is 'erased' from Sheet2's projected sales.

Does that make sense? Thanks. This is driving me crazy.

 

[Marcelo]

Matt,

is better if you copy and past (sheet 1 --> sheet 3), and after it del data
on sheet 1

Marcelo - Brazil

"Matt" escreveu:

 

[Matt]

Hi Marcelo,

That really isn't practical since this will be a sales manager using this
after I build it. I need to make it as seamless as possible for him. (user
friendly)

 

[Max]

Perhaps try something along these lines:

In Sheet2,

Suppose A110 is to link/point to Sheet1's A110 (say)

Put in the starting cell A1 (in Sheet2):
=OFFSET(INDIRECT("'Sheet1'!A1"),ROW(A1)-1,COLUMN(A1)-1)
Copy across to D1, fill down to D10
(fill across/down to cover the same range size)

The above will ensure that A110 always links/points to Sheet1's A110,
irregardless

If you have "scattered" link cells,
you could use something like this: =INDIRECT("'Sheet1'!A1")
which returns the same as the simple link: =Sheet1!A1
except that it will always point to A1 in Sheet1, irregardless

And for a neater look, we could suppress the display of extraneous zeros in
the sheet via clicking: Tools > Options > View tab > Uncheck Zero values > OK

 

[Matt]

Not sure I understand. Here is a sample formula for a cell in Sheet2 that
populates based on what is entered in sheet1:

=IF('Lead Data'!$G8<>"", IF(MONTH('Lead Data'!$G8)=3,'Lead Data'!$E8,""), "")

So, it looks at the date in Sheet1 and if the month is a '3' (March), it
pulls the coresponding amount into this cell from E8 of sheet1. If I cut and
paste the above row from sheet1 and move it to Sheet3 named 'Cancelled', the
above formula updates itself and replaces 'Lead Data' with 'Cancelled'.

Does that help?

 

[Max]

=IF('Lead Data'!$G8<>"", IF(MONTH('Lead Data'!$G8)=3,'Lead Data'!$E8,""), "")

Something like this was meant:
=IF(INDIRECT("'Lead Data'!G8")<>"", IF(MONTH(INDIRECT("'Lead
Data'!G8"))=3,INDIRECT("'Lead Data'!E8"),""), "")

 

[Max]

Data'!G8"))=3,INDIRECT("'Lead Data'!E8"),""), "")

And if you are copying down the formula, use this instead:
=IF(INDIRECT("'Lead Data'!G"&ROW(A1)+7)<>"", IF(MONTH(INDIRECT("'Lead
Data'!G"&ROW(A1)+7))=3,INDIRECT("'Lead Data'!E"&ROW(A1)+7),""), "")

 

[Matt]

Thanks Max. I'm real close. That formula works great if I indeed move that
lead over to the 'Cancelled' Sheet but if that lead isn't a 'Cancell' (i.e.
remains on Sheet1 with the reference on Sheet2) that formula returns a #REF
error.

 

[Max]

Thanks Max. I'm real close. That formula works great if I indeed move that
lead over to the 'Cancelled' Sheet but if that lead isn't a 'Cancell' (i.e.
remains on Sheet1 with the reference on Sheet2) that formula returns a #REF
error.

Not really sure what's happening over there (it seems ok when tested here) ..
(perhaps the sheet that the formulas' reside in is also being mangled <g>,
via deletions of cells/rows/columns eg deletion of col A?, subsequent to the
entry of the formulas)

Try instead in the starting cell, copy down:
=IF(INDIRECT("'Lead Data'!G"&ROWS($DS$30000S30000)+7)<>"",
IF(MONTH(INDIRECT("'Lead
Data'!G"&ROWS($DS$30000S30000)+7))=3,INDIRECT("'Lead
Data'!E"&ROWS($DS$30000S30000)+7),""), "")

The incrementer: ROWS($DS$30000S30000)+7
returns the same as the previous: ROW(A1)+7
but it should provide much more "leeway" (we shouldn't get #REF! errors)
should there be any subsequent deletions/insertions of cells/rows/columns in
the sheet
(as Cell DS30000 is roughly in the dead centre of the spreadsheet ..)