Trouble referencing dynamic cell data from another worksheet

**AlexMetric** · 03-21-2015, 04:23 PM

Hi everyone,

I hope someone can help me with this problem...

I have learned how to create a cell reference from another worksheet in the same workbook, which is helpful. I am doing this because I am creating automated Google Analytics based reports. The automation of this is crucial.

E.g.:

='Last Month'!A1

...where I want my cell in the current worksheet to display the value in cell A1 of the specified other work sheet
called Last Month.

Basic stuff and very useful for most of my reports...BUT...I want my cell in the current worksheet to display the value from a cell address that will change dynamically because of the variation in days of the month.

So in reference to February's data (when February is the last month) the cell I want to reference is C44, in March it will be C47, in February during a leap year it will be C45...etc...

I can create a formula in another cell in my current worksheet that dynamically gives the cell address as the data in the Last Month worksheet automatically changes and updates from my Google Analytics API. If necessary I could use this same formula within this cell that references the Last Month worksheet....I've tried lots of ways of doing this and nothing works.

E.g.:

='Last Month'![dynamically changing cell address]

...where the [dynamically changing cell address] is calculated in another cell in this worksheet or in this formula.

I hope you're still following this! :-)

I have tried this for example:

='Last Month'!(=J7)

...where cell J7 in my current worksheet has a formula that has resulted in C44 (just what I need for February).

I have tried all sorts of formulas using CONCATENATE and INDIRECT (see links below) as well but I can't get them to work. It seems like I can have a formula where the worksheet can change dynamically but not a cell within another worksheet.

http://spreadsheetpro.net/how-to-mak...-spreadsheets/

http://www.bettersolutions.com/excel...M125511311.htm

I'm desperate to make this work (hence I'm still trying obsessively instead of chilling out on Saturday night).

Please help me if a solution exists!

Many many thanks,

Alex

**6StringJazzer** · 03-21-2015, 04:46 PM

You haven't give complete information but my best guess is

Formula:

Please Login or Register  to view this content.

However, I also think that there is a better way to do this involving OFFSET or INDEX but I can't tell you what it is unless you tell me how you are calculating the cell reference (i.e., what is the formula in J7). This doesn't give any insight as to the logic you are using to determine what cell to use based on the month: "So in reference to February's data (when February is the last month) the cell I want to reference is C44, in March it will be C47, in February during a leap year it will be C45...etc..."

The best option, of course, is to attach your file. To attach a file:

Under the text box where you type your reply click the Go Advanced button.
In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
In the pop-up, click Add then Select to select a file from your local drive
Click the Upload button to upload the file
Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

Here are step-by-step instructions with screen shots

**AlexMetric** · 03-22-2015, 08:56 AM

Hi Jeff,

You're a legend!

That solved it for me, thank you so much!

Best wishes,

Alex

**6StringJazzer** · 03-22-2015, 09:22 AM

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved.

Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."