|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Does Excel have a way of using variables to easily update reference formulas?
I am using several spreadsheets at work. One is a list of activity for each day, and the other is a set of statements for each customer. The latter references the activity spreadsheet, along with one or two other pricing spreadsheets.
When I create a new spreadsheet for each month, I have to update the formulas to now reference the proper source for the formulas. I have to remember every location that is using a formula that needs updated in this manner. I'm wondering if there's a way to do it using some kind of variable. I'm not terribly familiar with Excel's Name system, so maybe that would work? For example, here is one formula that would need updated: =VLOOKUP($F30, 'G:\Accounting\_Acct\Facility\Allocations\2016 Allocations\10-2016\[10-2016 Monthly Allocation & Fee Calculation.xlsx]Fee Summary'!$A$70:$H$78, 6, 0) When I create November's, I will have to do Find and Replace 10-2016 with 11-2016 (twice), and when I create January's in a few months I'll have to replace the 2016 with 2017. Is there a way to set these formulas so that I can replace the file name I'm referencing with a variable that I could simply make one change to? Something like this: =VLOOKUP($F30, 'G:\Accounting\_Acct\Facility\Allocations\"YYYY" Allocations\"MM-YYYY"\["MM-YYYY" Monthly Allocation & Fee Calculation.xlsx]Fee Summary'!$A$70:$H$78, 6, 0) |
#2
|
||||
|
||||
You can replace the reference with
INDIRECT("'G:\Accounting\_Acct\Facility\Allocation s\"&year(A1)&" Allocations\" &text(a1,"m-yyy")&"\["&text(a1,"m-yyy")& "Monthly Allocation & Fee Calculation.xlsx]Fee Summary'!$A$70:$H$78" where A1 contains the date ( I hope I got that one right, but you've got the idea) PS disregard the space in the first occurrence of " allocations", it's a forum bug
__________________
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Array to iterate through variables and trap blank variables | Marrick13 | Word VBA | 5 | 08-04-2015 06:19 AM |
Formulas/Scripts That Cross Reference & Import Data | ekeithjohnson | Excel Programming | 3 | 08-23-2014 11:26 PM |
Retrieve page numbers in Word doc from set of variables in Excel file | smn | Word VBA | 6 | 06-29-2014 06:32 PM |
Cross-reference function to update bullet header and number simultaneously | dljenks | Word | 1 | 01-03-2014 01:38 PM |
Update bookmarks and variables in document | webharvest | Word VBA | 5 | 06-21-2011 03:22 PM |