|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is there a formula that can "copy" data in one cell and then make it "static"?
I apologize if this has been answered elsewhere already, but I must be using the wrong words as I cannot seem to find a solution.
I have been creating a "Budget Calendar". In one tab I have a calendar with space in each day for items and their amounts (it is an adaption of an expenses calendar template I found online). In a second tab, I have listed all my regular incomes and expenses. Every time an income or expenses is due on the calendar, it references its appropriate value in the tab with the expected income and expenses. I currently use the INDEX formula to do this. This works fine for now. My soon-to-be problem is when the expected incomes or costs change (as they might from month to month), if I change the values in the Expected income and costs tab, it updates for all the previous times it has been referenced in the calendar tab. This then ruins my budget, as I have monthly rolling calculations that are then thrown out of whack. I need my formula to copy this data over and make it static and not reference the cell again, sort of how you would in a database that deals with invoices. (As your price for an item changes, you don't want the previous issued invoices to update to new prices as that isn't what was charged at the time). How might I go about this? Feedback much appreciated in advance. |
#2
|
||||
|
||||
Hi and welcome
please post a sample sheet (no pics please) with some data and manually mocked up results. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
On fly. I'd have in Incomes table a column, where you indicate from which date this entry for this income article is valid (the entry is valid from this date to next entry for same income article (by date), or to current time when there is no more entries for this article. And probably a column, where all table rows are numbered (a formula like =ROW()-X). So every income article can have many rows in Incomes table.
In calendary, you have to use SUMIFS instead of INDEX to get searched values, or to get the row number of income article entry in Incomes table, and then use it in INDEX function. |
#4
|
|||
|
|||
This requires either the use of keyboard shortcuts ... or using the mouse and clicking menu selections. You can also create a macro that, once the affected cells are selected, will convert all those cells at once.
Not tested here : Code:
Sub ConvertToValues() Dim MyRange As Range Dim MyCell As Range Select Case MsgBox("You Can't Undo This Action. " & _ "Save Workbook First?", _ vbYesNoCancel, "Alert") Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select Set MyRange = Selection For Each MyCell In MyRange If MyCell.HasFormula Then MyCell.Formula = MyCell.Value End If Next MyCell End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mailto Hyperlink Formula contains too large of "Body" receiving "#Value" need to find workaround | MCamera | Excel | 1 | 03-02-2022 07:52 PM |
Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro | Matt C | Excel Programming | 2 | 01-08-2022 06:03 AM |
Conditional function to operate a formula based on "Yes" or "No" Condition | Des ONeill | Excel | 1 | 07-03-2021 12:17 AM |
Condtional function to operate a formula based on "Yes" or "No" | Des ONeill | Excel | 3 | 07-03-2021 12:16 AM |
'Linking' entered information to other "cells" from an original "cell" in MS Word | Wade | Word | 6 | 09-03-2012 05:22 PM |