Flexible formulas

cpc · Apr 15th, 2002, 12:42 PM

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?

Al Chara · Apr 15th, 2002, 12:47 PM

How about just clearing contents instead of deleting pages/rows/cells. This will keep the formulas relevant.

cpc · Apr 15th, 2002, 12:58 PM

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.

croweld89 · Apr 15th, 2002, 01:40 PM

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.

Al Chara · Apr 15th, 2002, 01:40 PM

You can change your print areas before you print.

croweld89 · Apr 15th, 2002, 01:54 PM

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

cpc · Apr 15th, 2002, 03:31 PM

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.

croweld89 · Apr 16th, 2002, 01:24 PM

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

Apr 15th, 2002, 12:47 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	How about just clearing contents instead of deleting pages/rows/cells. This will keep the formulas relevant. __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 12:42 PM	#1
cpc New Member Join Date: Mar 2002 Posts: 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?

Apr 15th, 2002, 12:58 PM	#3
cpc New Member Join Date: Mar 2002 Posts: 38	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.

Apr 15th, 2002, 01:40 PM	#4
croweld89 New Member Join Date: Mar 2002 Posts: 34	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.

Apr 15th, 2002, 01:54 PM	#6
croweld89 New Member Join Date: Mar 2002 Posts: 34	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

Apr 15th, 2002, 03:31 PM	#7
cpc New Member Join Date: Mar 2002 Posts: 38	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.

Apr 16th, 2002, 01:24 PM	#8
croweld89 New Member Join Date: Mar 2002 Posts: 34	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