Flexible formulas

cpc

New Member
Joined
Mar 14, 2002
Messages
38
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
How about just clearing contents instead of deleting pages/rows/cells. This will keep the formulas relevant.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top