+ Reply to Thread
Results 1 to 5 of 5

how to write formulas in same worksheet where db queries are loaded

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    how to write formulas in same worksheet where db queries are loaded

    I've attached part of a workbook which queries a database and loads the last 288 rows of data into tables in my workbook.

    I'm trying to write some formulas based on the data in those rows, and that formula is placed below these tables.

    However when I do write a formula, instead of giving me the answer, it just copies the formula label. (You'll see this in the workbook if you scroll down below row 300). I've placed some graphics there to point out the cells with the problem.

    So... how do I "break" the table so that rows beneath it become regular, empty Excel-style cells?

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: how to write formulas in same worksheet where db queries are loaded

    What range are you trying to refer to with this?

    $I289:OFFSET(I289,0,0,-C$297)

    The OFFSET part of that evaluates as $I$275:$I$1289 so the whole thing evalutes to $I289:$I$275:$I$1289 which isn't a valid range reference.
    If posting code please use code tags, see here.

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: how to write formulas in same worksheet where db queries are loaded

    Hello jrtaylor,

    There are no negative values in the Range specified, that is why you won't see any such values returned. May there be something else that I am missing?

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: how to write formulas in same worksheet where db queries are loaded

    Thanks for catching that. I had copied the formula from another workbook, only vaguely remembering how the formula worked. The range I wanted to get the average of is I275-I290.
    The #15 is a variable so that I can change the average period if needed.

    So it averages "up" from 290 rather than down from I3. Row 290 always represents the most recent data.

    I think I can fix it now. Thanks for your help!

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: how to write formulas in same worksheet where db queries are loaded

    Hi jrtaylor,

    Thank you for the quick response. You are welcome, glad we could help. Also thank you for the Rep!

    Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Text queries within nested IF formulas to provide required output
    By marky385 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-14-2015, 05:06 AM
  2. [SOLVED] VBA loaded DDE formulas not updating until macro has finished
    By Philb1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-12-2014, 06:44 PM
  3. Multiple Worksheet Queries
    By Terry_c007 in forum Excel General
    Replies: 3
    Last Post: 06-12-2011, 08:48 PM
  4. Replies: 2
    Last Post: 01-27-2011, 03:11 PM
  5. Open worksheet loaded into combo box
    By mariela1327 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-26-2010, 09:35 AM
  6. formulas used as if queries
    By twilson148 in forum Excel General
    Replies: 10
    Last Post: 03-21-2010, 06:39 PM
  7. Multiple worksheet queries
    By liam in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2005, 07:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1