Make Dynamic (Automatically Updating) References in Formulas and Functions in Excel

Hi guys, quick question.

Basically I have a sheet that has 5 other sheets feed into it and display specific cell references inside specific cells. Whenever I open the "Master" sheet it asks me do I wish to update the cell references.

My question is, is there a way that I can do this without closing the master and while I have all of the sheets open? I'm manipulating the child sheets and really need to know that what I'm changing is going to work.

Thanks,

Brian

I often run into this issue and have not found a good solution but manually going through every formula, finding and changing the reference value. I wish I could just find all Sheet4 and replace it with Sheet5.

Here is an example. I have a worksheet that has all kinds of different formulas (sometimes it's three but sometimes it's fifty). A lot of the formulas use this refrence pointing to for example Sheet4!A1 whereas it should point to Sheet5!C5.

Lets also assume that Sheet4 and Sheet5 have very descriptive names and I would not like to change them. Can I do some type of find and replace?

Thanks for your help,
panter.8

Hello!

I use an Excel 2007 book to calculate the sales of a company. The book contains 31 sheets with the same structure of table for each day of the month , and a final page to sum the final situation of sales. On the bottom of each table is a daily sum of sales for each sales agent of firm, not for all range of products, but only for the most demanded ones of that month. The problem is that I have monthly to add or eliminate different new or old products from list, depending on the rythm they are sold. And to re-sort alphabetically the products for a new available list, being changed the previous cell references of those products (and the next cell of each product, with its price) with some new ones. In this case, in the final sum formula of sales from each column of every agent, they are included still the old cell references of those products, and are not not automatically updated with their new cell positions / references configured after re-sorting the list. I need a solution for the updating problem, in order to avoid re-changing manually every month the structure for every daily table in the final sum formula for each agent (10 agents daily * 31 days = 310 operations of formula!!!).
Thank you in advance!

I have a lot of very large files that pull data from a number of different spreadsheets each month to do analysis on them, and I want to know how to make the references to those spreadsheets dynamic. For instance:

In October I need a value from this chart at this reference:

='C:\Reports\Sales\Oct2010\[MonthlySales.xls]Sheet1'!B9

Each month I need the same value from the same place in a new sheet of the same format:

='C:\Reports\Sales\ Nov2010 \[MonthlySales.xls]Sheet1'!B9

How do I make this middle part with the date dynamic so all I have to do is update a cell somewhere from Oct2010 to Nov2010?

Hi, this question is related to my post #7 in thread: Help needed! by gavinjw.

I was just wondering if it is possible to make a Range in a formula dynamic.
If you look at this formula:


Code:

=INDEX($A$2:$F$10,MATCH (A14,$A$2:$A$10,0) , (MATCH ("*", B2:F2 ,-1) +1)  )

the part marked red doesn't really make any sense - I am looking for the first non-empty Column in a range that is 1 row high.

Problem is, the row is actually determined through the first match function - as you can see. What would need to happen is that the formula finds the first non-empty cell (Column number) in the row found through the first match for the formula to be more flexible. It just happens to work for the example I constucted for the OP, because my criteria range for the Row Match happens to be sorted the same way as the data table.

Therefore the question is how to make that range dynamic. Any hints would be greatly appreciated.

i'm working on a spreadsheet that allows a user to select a state, and then does a vlookup on another worksheet to return the cell. for example, if you type in AK, it will return C3. C3 is the upper left cell of 6x7 table.

i want my chart to automatically update based on that cell reference C3, so i have attempted to create named ranges to reference in the chart source data. the chart also needs to be automatically updated when new columns are added to the report, which makes it a little trickier. Data is the name of the worksheet that contains the data tables.

here is the named range that seems to be working for the data labels, with fixed rows C & D):
=OFFSET(Data!$C$1,,,2,COUNTA(Data!$C$1:$IV$1)+1)

here is the named range for the values, which works when you start at a fixed value such as C3:
=OFFSET(Data!$C$3,,,6,COUNTA(Data!$C$1:$IV$1)+1)

in the formula immediately above, i need to somehow replace the "$C$3" with the cell that is referenced to begin with using the vlookup. so that any time the user changes the state, the range begins from the updated cell.

can you help me???

I'd love to find an elegant way to do something.

I've got weekly information appended below each other in one sheet, and I'm
pulling data from it in another by using VLOOKUP. Right now I get the
beginning and ending rows for the lookup range, but have to manually input
them into the formula -- is there a way to build the array automatically for
the formula? I tried building and assembling text to represent the wanted
rows, but got errors. The trick is that the number of rows is not constant
from one week to the next.

Here's what I'm using now (manually setting rows in the third calc based on
values returned from the first two calcs):

beginning row=+VLOOKUP(W$36,'0740'!$A4:$D750,4,FALSE)
(look for the start date of the week, say 372 in this case)

ending row=+VLOOKUP(W$36+7,'0740'!$A4:$D750,4,FALSE)
(look for the start date of the next week, say 396 in this case)

the value lookup=+VLOOKUP($A40,'0740'!$A$372:$D$396,3,0)
(look for the value within the row range)

Any help to not have to manually update rows for future weeks will be
appreciated!

Hello all,

Below is my problem. My spreadsheet contains two sheets. One sheet has 3 columns with "name" "number" and "product" in columns A,B,C respectively. Sheet2 contains "name" and "amount in" columns A,B respectively. I have formula in sheet1 of column c to multiply the value in sheet1!B with Sheet2!B. The problem is the column amount in sheet2 dosn't always have to be in column B. next time the data is refreshed it can be in column C. The only common thing will be that column "name" and "amount" will be next to each other and the column names will always stay the same. How can i acheive this using excel formulas. Macros will be my last option. I have attached a sample spreadsheet.

Any help is greatly appreciated.

I have a file (see enclosed) where i am trying to dynamically plot the last 12 rows of data.

As more data is added to the file only the last 12 rows of data are plotted.

Problem:If you open the file you will see that as you type entries into col A the graph does update automataically. However, if you add values to col's B,C and D these additions do not show up on the graph.

Any ideas why?