Indirect referencing to a different worksheet&Table

**lelliott7** · 04-14-2022, 03:37 PM

Can someone help me write this indirect formula without cell referencing, I can't seem to wrap my head around what I'm doing wrong...

In cells,

B2(file name): 2010_Sold.xlsx
B3(Table Name): January
B4(Column Name): Current Price

And this formula works: =SUM(INDIRECT("'"&B2&"'!"&B3&"["&B4&"]"))

However, Id like to not reference cells in the formula. I've done this within a workbook but cant seem to get it to work when indirectly referencing a different workbook.

So something like this(But I'm obviously not writing it correctly, as im getting a #REF! error):

=SUM(INDIRECT("'"&{2010_Sold.xlsx}&"'!"&January&"["&Current Price&"]"))

How do I write this?

**Richard Buttrey** · 04-14-2022, 03:52 PM

You'd be better advised to upload the two workbooks so that we can can check this in context.
Presumably you are aware that INDIRECT doesn't work unless the file referred to is Open?

It's also confusing since you say the formula that works refers to B2! which I presume means a sheet called whatever is held in B2. But B2 is the name of a file.

**lelliott7** · 04-14-2022, 04:08 PM

My Apologies, One of the workbooks isn't shareable.

I made a couple replica versions.

See attached.

**lelliott7** · 04-14-2022, 04:36 PM

Originally Posted by Richard Buttrey

You'd be better advised to upload the two workbooks so that we can can check this in context.
Presumably you are aware that INDIRECT doesn't work unless the file referred to is Open?

It's also confusing since you say the formula that works refers to B2! which I presume means a sheet called whatever is held in B2. But B2 is the name of a file.

Im all sorts of confused.
But when i enter = and select the whole column in the 2010_Sold workbook I get:

='2010_Sold.xlsx'!January[Current Price]

It should be noted, that the sheet is called January and the Table is also called January.

**Glenn Kennedy** · 04-15-2022, 03:06 AM

The syntax that you need is:

=SUM('2010_Sold.xlsx'!January[Current Price])

**lelliott7** · 04-15-2022, 03:13 PM

Originally Posted by Glenn Kennedy

The syntax that you need is:

=SUM('2010_Sold.xlsx'!January[Current Price])

unfortunately, I need an indirect formula. As I will eventually be including other sheets for a total sumif(indirect())/countif(indirect()) formula. as I need the average of multiple table columns from different sheets.

**ELeGault** · 04-15-2022, 03:22 PM

I am not sure what the issue is with what you have... just replace the "" References to cells with the text in those cells...

=SUM(INDIRECT("'2010_Sold.xlsx'!January[Current Price]"))

**ELeGault** · 04-15-2022, 03:44 PM

You know... I think the problem isnt the indirect its that you are indirectly accessing a closed files... several solutions... but likely quickest way through this is to define the path so that it knows where the reference is... right now its hunting for a workbook that becomes invisible to excel upon close... But if you write in the path you can reference the closed book... I would have to play with it a bit to get it to do math against the closed book.... but if you preset a field within these reports that has the figure you need to call on.... K9=SUM(January[Current Price]) on the 2021_Sold January tab.... =INDEX('C:\Users\Your.Name\Downloads\[2010_Sold.xlsx]January'!K9,1,1) you will get the value to populate even when the sold book is closed

**lelliott7** · 04-15-2022, 03:58 PM

My issue, is that I need an average from different tables from different sheets. For example, I need to do a accurate quarterly average of the prices from january,february & march. the only way I can think to do this is to do a indirect formula to gather the data from the different tables. I'm not so concerned about it being open or closed. I can have them open or closed when needed. the workbook with all the data will remain unchanged. I have many workbooks with data spanning many years. so altering them would take up lots of time.

Maybe im missing a simpler solution.

**Richard Buttrey** · 04-16-2022, 05:03 AM

Originally Posted by lelliott7

Maybe im missing a simpler solution.

Indeed, maybe you are.

As I understand it you have data from different months spread across multiple workbooks. If so this really isn't the best approach.
As far as is practical you should always gather data that is the same layout, like monthly tables of information in the same workbook and in the same sheet, where you have a column that contains a reference to the month if that is not already part of your data.

How are you capturing your monthly data? Is it from some back office system? If so get that system to download all months rather than just one.
Otherwise use a macro to build the single table by looping through all the files and copying and pasting the data.

**Glenn Kennedy** · 04-16-2022, 03:02 AM

You did NOT tell us that you wanted to reference different tables/different sheets until Post 6.

1. Please confirm that you understand that INDIRECT ONLY works when source sheets and destination sheets are open.

2. Upload a properly representative set of sample sheets.

Indirect referencing to a different worksheet&Table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Re: Indirect referencing to a different worksheet&Table

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dynamic Counted Range, Indirect worksheet referencing and WeekNum in one. Getting dizzy!

[SOLVED] Indirect function, referencing another worksheet

[SOLVED] Referencing a vlookup table on another worksheet

Referencing a named table on a different worksheet

Indirect referencing Worksheet with space in the name

Indirect Worksheet Function Referencing Another Worksheet

Referencing with Indirect

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions