Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
UB UB is offline
external usenet poster
 
Posts: 120
Default Cell Value to remain constant

Hi
I have a sheet -A in which the data comes from the Master sheet through
linked cell.
My sheet A has multiple rows based on the months like JAN FEB etc.
For the month of JAN (SHEET A) the data comes from the MASTER after doing
calculations.
The problem I have is that every month the MASTER sheets gets updated So if
I have value in SHEET A in row JAN and now my MASTER gets updated in FEB, my
FEB data populates correct, but my JAN data is not correct because it is now
being calculated with new MASTER sheet.
What can I do to keep the values in row JAN intact, when refernce MASTER
sheet is updated.
I am willing to write VBA code also

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Cell Value to remain constant

At the end of January copy and Paste SpecialValues


Gord Dibben MS Excel MVP

On Tue, 10 Mar 2009 11:54:06 -0700, ub wrote:

Hi
I have a sheet -A in which the data comes from the Master sheet through
linked cell.
My sheet A has multiple rows based on the months like JAN FEB etc.
For the month of JAN (SHEET A) the data comes from the MASTER after doing
calculations.
The problem I have is that every month the MASTER sheets gets updated So if
I have value in SHEET A in row JAN and now my MASTER gets updated in FEB, my
FEB data populates correct, but my JAN data is not correct because it is now
being calculated with new MASTER sheet.
What can I do to keep the values in row JAN intact, when refernce MASTER
sheet is updated.
I am willing to write VBA code also


  #3   Report Post  
Posted to microsoft.public.excel.misc
UB UB is offline
external usenet poster
 
Posts: 120
Default Cell Value to remain constant

Hi Gord
Thanks for the reply
But I have the January data is in the Master Sheet and my formula's are in
the Sheet -A. So the SHEET -A formuls links to MASTER workbook Sheet -1. The
master workbook has another sheet called Master that gets updated every month.
SO basically in the master workbook , data in the sheet 1 gets calculated
from the master sheet in the same workbook and then the SHEET-A links to the
sheet1 of the master workbook.
SO if I update my master, it updates the SHEET1 calculation, that effects
the linked sheet-A


"Gord Dibben" wrote:

At the end of January copy and Paste SpecialValues


Gord Dibben MS Excel MVP

On Tue, 10 Mar 2009 11:54:06 -0700, ub wrote:

Hi
I have a sheet -A in which the data comes from the Master sheet through
linked cell.
My sheet A has multiple rows based on the months like JAN FEB etc.
For the month of JAN (SHEET A) the data comes from the MASTER after doing
calculations.
The problem I have is that every month the MASTER sheets gets updated So if
I have value in SHEET A in row JAN and now my MASTER gets updated in FEB, my
FEB data populates correct, but my JAN data is not correct because it is now
being calculated with new MASTER sheet.
What can I do to keep the values in row JAN intact, when refernce MASTER
sheet is updated.
I am willing to write VBA code also



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Cell Value to remain constant

I can't suss out your configuration of workbooks and sheets.

You have two workbooks, one called MASTER with two sheets......Sheet -1 and
Master?

The other workbook is named ?? and contains another sheet named Master and
one named Sheet -A?

Somewhere in there you will have to break some links so the values for JAN
data do not update.


Gord

On Tue, 10 Mar 2009 13:28:01 -0700, ub wrote:

Hi Gord
Thanks for the reply
But I have the January data is in the Master Sheet and my formula's are in
the Sheet -A. So the SHEET -A formuls links to MASTER workbook Sheet -1. The
master workbook has another sheet called Master that gets updated every month.
SO basically in the master workbook , data in the sheet 1 gets calculated
from the master sheet in the same workbook and then the SHEET-A links to the
sheet1 of the master workbook.
SO if I update my master, it updates the SHEET1 calculation, that effects
the linked sheet-A


"Gord Dibben" wrote:

At the end of January copy and Paste SpecialValues


Gord Dibben MS Excel MVP

On Tue, 10 Mar 2009 11:54:06 -0700, ub wrote:

Hi
I have a sheet -A in which the data comes from the Master sheet through
linked cell.
My sheet A has multiple rows based on the months like JAN FEB etc.
For the month of JAN (SHEET A) the data comes from the MASTER after doing
calculations.
The problem I have is that every month the MASTER sheets gets updated So if
I have value in SHEET A in row JAN and now my MASTER gets updated in FEB, my
FEB data populates correct, but my JAN data is not correct because it is now
being calculated with new MASTER sheet.
What can I do to keep the values in row JAN intact, when refernce MASTER
sheet is updated.
I am willing to write VBA code also




  #5   Report Post  
Posted to microsoft.public.excel.misc
UB UB is offline
external usenet poster
 
Posts: 120
Default Cell Value to remain constant

Hi Gord

I am sorry , if I am not able to explain properly.
To make it simple.
If I have a workbook with 2 sheets. Sheet1 and Master(sheet2). Sheet1 does
all the calculation based on months and gets the data from Master sheet
The problem that I face is that my Master sheets gets updated every month.
So for example, I have the data calculated in my sheet 1 for the month of Jan
and now when my Master sheet gets updated in Feb, my Jan data also gets
changed because Master has new results.
I want to keep the JAN data in Sheet1 as if, even if I update my Master.
The Formula that I have in Sheet 1 is like :
Countif(a2:a34="P", b2:b34)*Countif(c2:c34 = "JAN", b2:b34)


"Gord Dibben" wrote:

I can't suss out your configuration of workbooks and sheets.

You have two workbooks, one called MASTER with two sheets......Sheet -1 and
Master?

The other workbook is named ?? and contains another sheet named Master and
one named Sheet -A?

Somewhere in there you will have to break some links so the values for JAN
data do not update.


Gord

On Tue, 10 Mar 2009 13:28:01 -0700, ub wrote:

Hi Gord
Thanks for the reply
But I have the January data is in the Master Sheet and my formula's are in
the Sheet -A. So the SHEET -A formuls links to MASTER workbook Sheet -1. The
master workbook has another sheet called Master that gets updated every month.
SO basically in the master workbook , data in the sheet 1 gets calculated
from the master sheet in the same workbook and then the SHEET-A links to the
sheet1 of the master workbook.
SO if I update my master, it updates the SHEET1 calculation, that effects
the linked sheet-A


"Gord Dibben" wrote:

At the end of January copy and Paste SpecialValues


Gord Dibben MS Excel MVP

On Tue, 10 Mar 2009 11:54:06 -0700, ub wrote:

Hi
I have a sheet -A in which the data comes from the Master sheet through
linked cell.
My sheet A has multiple rows based on the months like JAN FEB etc.
For the month of JAN (SHEET A) the data comes from the MASTER after doing
calculations.
The problem I have is that every month the MASTER sheets gets updated So if
I have value in SHEET A in row JAN and now my MASTER gets updated in FEB, my
FEB data populates correct, but my JAN data is not correct because it is now
being calculated with new MASTER sheet.
What can I do to keep the values in row JAN intact, when refernce MASTER
sheet is updated.
I am willing to write VBA code also




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Value used in a Paste Link to remain constant during a sort LawTrainer Excel Discussion (Misc queries) 1 February 25th 09 12:29 AM
Cell reference value to remain constant in a sort using Paste Link LawTrainer Excel Discussion (Misc queries) 2 February 24th 09 11:37 PM
How to make a cell address remain constant when copying a formula Deana Excel Worksheet Functions 1 September 7th 06 08:41 PM
remain constant in a copy & paste Viviank New Users to Excel 5 March 18th 06 11:33 PM
data source remain constant maryj Charts and Charting in Excel 1 October 28th 05 12:50 PM


All times are GMT +1. The time now is 10:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"