excel query on linking formulas

[bcsyeung]

Hi Mr Excel,

i have a problem and hope you can help me with this.

I used to post an excel file on sharepoint for different users to input the data. Then the data was linked to a the 'Summary' tab. this is a recurring process for every week.

For example:
Sheet1 looks like this:

Week 1 xxx
Week 2 xxx
Week 3 xxxx
total xxxx

A formula on Week 1 was linked to 'summary tab' to show week 1 forecast. then after week1 has passed, and week 2's data becomes actual, i need to go to 'summary tab' to change the formula to link to week 2's data as it was previously linked to week1 and so on.

Is there a smarter way to do this so that i don't have to manually change the formula in 'summary tab' every week to capture the latest forecast?

Appreciate for your help with million thanks.

 

[Jerry Sullivan]

Hello and Welcome,


There are several ways to make your updating process a bit easier. Options would include:

• Using a lookup function like VLOOKUP to find a single row of data for the current week.
• Using SUMIF or SUMIFS to add data from multiple rows of data for the current week.
• Using INDIRECT function in your formula to use a variable for the Sheet1 row number instead of a constant value.

Without seeing your formula(s) it's hard to recommend the best option.
Please post an example formula if you would like some additional help.

Good luck!

 

[bcsyeung]

THanks Jerry. I'm new to this forum. Can u advise how to post the excel file with formulas on this forum?

 

[Aladin Akyurek]

Let the area in A:B on Sheet1 house the week and the total...

Let's first define BigNum (by means Insert|Name|Define or Formulas|Name Manager) as referring to:

=9.99999999999999E+307

If there was no total on Sheet1 closing the numeric data, we could just invoke:

=LOOKUP(BigNum,Sheet1!B:B)

With a total as last value in B:B, try the following to capture the last weekly value:

=LOOKUP(BigNum,INDEX(Sheet1!B:B,MATCH(BigNum,Sheet1!B:B)-1))