Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
Matt,
is better if you copy and past (sheet 1 -- sheet 3), and after it del data on sheet 1 Marcelo - Brazil "Matt" escreveu: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
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) "Marcelo" wrote: Matt, is better if you copy and past (sheet 1 -- sheet 3), and after it del data on sheet 1 Marcelo - Brazil "Matt" escreveu: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
Perhaps try something along these lines:
In Sheet2, Suppose A1:D10 is to link/point to Sheet1's A1:D10 (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 A1:D10 always links/points to Sheet1's A1:D10, 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
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" wrote: Perhaps try something along these lines: In Sheet2, Suppose A1:D10 is to link/point to Sheet1's A1:D10 (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 A1:D10 always links/points to Sheet1's A1:D10, 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
=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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Matt" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
=IF(INDIRECT("'Lead Data'!G8")<"", IF(MONTH(INDIRECT("'Lead
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),""), "") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
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" wrote: =IF(INDIRECT("'Lead Data'!G8")<"", IF(MONTH(INDIRECT("'Lead 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),""), "") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with linked formulas & multiple worksheets
"Matt" wrote:
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$30000:DS30000)+7)<"", IF(MONTH(INDIRECT("'Lead Data'!G"&ROWS($DS$30000:DS30000)+7))=3,INDIRECT("' Lead Data'!E"&ROWS($DS$30000:DS30000)+7),""), "") The incrementer: ROWS($DS$30000:DS30000)+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 ..) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding and Naming Multiple Worksheets | Excel Worksheet Functions | |||
Functions across multiple worksheets | Excel Worksheet Functions | |||
Sumif with multiple worksheets | Excel Worksheet Functions | |||
Update multiple worksheets | Excel Discussion (Misc queries) | |||
fax multiple worksheets | Excel Worksheet Functions |