Range Name Question 2

[luceze]

I have a worksheet that imports data from our accounting system. The range is automatically named "GeneralLedger" and it grows ro shrinks as it is updated which is what I want.

However, on a separate sheet I am using formulas with references to the "GeneralLedger" range and I would like the formula references to grow or shrink along with the "GeneralLedger" range.
Is there any way to do this?

 

[lespaul]

Depends on how the range is "automatically named" and how it shrinks and grows. If there is VBA code behind the spreadsheet that controls the import, then you could conceivably control the formulas on the second sheet.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[KathyD]

If your formulas are referring to the named range then they should be updating as well. I've seen instances where a range is named identically to the sheet it resides in (i.e. sheet name "HourlyData" and the range of data on the sheet is named "HourlyData&quot. It might help if I knew what the formula was.

KathyD

 

[SkipVought]

luceze,

If your GeneralLedger table as headings, AND if you select any of thos headings in the Name Box and the DATA under the heading is selected, then you're in link Flynn! You can use the heading names on any other sheet to reference the table. Depending on how you want to do that will determine how to use these references.

Let me know.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[luceze]

Thanks for the fast replies.
To try and answer all the questions.

The data is imported using Microsoft Query so Excel names the range.

The formula that I am using is:
{=SUM(IF(GeneralLedger!$B$9:$B$4403<200,IF(GeneralLedger!$D$9:$D$4403=BalanceSheet!B9,GeneralLedger!$Q$9:$Q$4403)))*-1}

Skip,
So what you are saying is that if I name my ranges with the same name as the column headings in my table the ranges will grow or shrink with the table.

 

[KathyD]

The formula is referring to the sheet and not the named range. I've seen people use VBA to assign a name to the range and then adjust their formula to refer to the range. i.e.
GeneralLedger!$B$9:$B$4800 named as 'ABC',
GeneralLedger!$D$9:$D$4800 named as 'DEF',
GeneralLedger!$Q$9:$Q$4800 as 'GHI'

The formula would read '=SUM(IF(ABC<200,IF(DEF=BalanceSheet!B9,GHI)))*-1'

I've also seen people adjust the range in the formula to take in every possible row (i.e. $Q$9:$Q$65536).

hth,

KathyD

 

[SkipVought]

Thanx for the info.

Here's what you can do.

1. right click the GeneralLedger tab and select &quot;View Code&quot; -- this takes you to the Visual BASIC Editor. The Code Window has 2 dropdowns: Object & Procedure

2. Select Worksheet from the Object box and the select Change from the Procedure box. We are going to use the Worksheet Change event to trigger the renaming of ranges when MS Query results are received.

3. Insert the following code in Worksheet_Change...

    Application.DisplayAlerts = False
    [GeneralLedger].CurrentRegion.CreateNames _
        Top:=True, _
        Left:=False, _
        Bottom:=False, _
        Right:=False

Now, you can use the headings to do stuff like this...

Assuming that the headings are -- Jan, Feb, Mar etc

=SUM(Jan)+SUM(Feb)

If you are on ANY SHEET within the same row range as GeneralLedger, you can do stuff like this...

=Jan+Feb

and the result will be the sum of Jan and Feb FOR THE ROW that your formula is on.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[luceze]

Thanks Skip!

That goes straigt into my VB code archive.