Flexible formulas
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Flexible formulas

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Original question was the need for a formula that when pages or rows are deleted the formulas will not return an error message.

    Aladin, dave and scott replied thanks. Still
    a problem though. Dave there are not multiple sheets but pages. any more thoughts?

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about just clearing contents instead of deleting pages/rows/cells. This will keep the formulas relevant.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This will not work as clearing contents still keeps all pages. The idea is to be able to shorten the form being saved as... .
    the form is 100 pages and say I want to save 30 pages and the last page with the totals.
    This is my hopes.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I assume that the formulas are sub/grand totals of data pasted in from other sources.

    I try to get the data the same for all downloads and paste the data into a pre-fab "template". However, it sounds as though your new data is not even close to the same as the prior data.

    Can we get more data on the design of your data? Say row headers for vlookup?

    If you can have row headers in column A for your data to be pasted next to, or the data actually has row headers, then set your totals up with vlookup formulas.

    ie... =vlookup(widget a,range,2)+vlookup(widget b,range,2)+vlookup(widget c,range,2)+etc...

    I haven't tested to see if there is a limit on the number of vlookup formulas as there is on If statements.
    Let me know if this helps or if we are headed in the right direction.

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can change your print areas before you print.

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    amendment to earlier post...

    you will need the following additions for the vlookup formulas:

    =if(isna(vlookup),0,(vlookup)),+if(isna(vlookup),0,(vlookup))+etc...

    where vlookup is your vlookup formula. The limit here may be excel's formula lenght limit. as putting each vlookup formula in twice of course doubles the size of your total formula.

    I just thought of another solution...
    If all else fails, I would highlight the entire tab do a find/replace (hit - control+h) and replace +#ref with nothing. That will atleast eliminate the errors.
    The vlookup will prevent you from having to add links whenever you add to the data. The replace solution will not.

    Still in search of a better solution.
    -Dave

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave,

    The replace function sounds like it might be good. I was able to hide all of the extra pages and thus only print out the ones I need
    and the formulas work, but I still have huge files to send. I would still like to be able to eliminate teh pages all together. If so using erplace with might be an easy fix. But in trying this I get an error message that the formula is not correct. avoid using symbols etc.

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Sorry for taking so long to reply...

    I highlighted the sheet and was able to delete the error using the replace function.

    Make sure that you replace #ref!
    rather than #ref

    Let me know how it goes!
    -Dave

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com