multiple workbooks...

  • Thread starter Thread starter Barney Quinn
  • Start date Start date
B

Barney Quinn

I am doing a rather large database (well large for me). There are about
2000 clients in total. I tried to have an Access database written, but the
programmer couldn't speak english (or more importantly... .he couldn't
listen in it) and I couldn't speak VBA! The result is I am out a thousand
bucks for a database that is useless .... so... I'm back to excel. I am
learning the wonders of Excel. It is truly a great program for my needs.

I have one workbook that I store all the client CORE data. Names... numbers
etc.... I have a series of other workbooks that draw from that core data.
The individual departments use the core data and then have each their own
other data that they put into their sheets. This is non essential data...
but data that helps that particular department monitor client activity.

My problem is that since the database is always growing, I referenced about
3000 rows from the core data sheet to the other workbooks. Since not all
3000 rows are populated yet, the empty ones report "0"'s in those empty
rows. Is there any way to prevent that? Like if the cell in the core data
sheet is empty, then report nothing?

B.
 
One way:

=IF('[Book1.xls]Sheet1'!A1="", "", '[Book1.xls]Sheet1'!A1)

You can't literally report *nothing*, but you can return the null string
("").
 
Hi Barney

It's not possibly for a cell housing a formula to be truely empty. You
can of course return "" (empty text) but the cell is not truely empty. I
would leave the zeros in place (they ae much more formula friendly than
"") and go to Tools>Options-View-Zero Values. Or use a custom format
like

General;-General;

Posted via: http://www.ozgrid.com
Excel Templates Training Add-ins.
Free Excel Forum & Business Software
 
Of course... that makes sense. It's like the conditional formatting that I
do.... I should have considered this. Thanks so much.

B

JE McGimpsey said:
One way:

=IF('[Book1.xls]Sheet1'!A1="", "", '[Book1.xls]Sheet1'!A1)

You can't literally report *nothing*, but you can return the null string
("").

Barney Quinn said:
I am doing a rather large database (well large for me). There are about
2000 clients in total. I tried to have an Access database written, but the
programmer couldn't speak english (or more importantly... .he couldn't
listen in it) and I couldn't speak VBA! The result is I am out a thousand
bucks for a database that is useless .... so... I'm back to excel. I am
learning the wonders of Excel. It is truly a great program for my needs.

I have one workbook that I store all the client CORE data. Names... numbers
etc.... I have a series of other workbooks that draw from that core data.
The individual departments use the core data and then have each their own
other data that they put into their sheets. This is non essential data...
but data that helps that particular department monitor client activity.

My problem is that since the database is always growing, I referenced about
3000 rows from the core data sheet to the other workbooks. Since not all
3000 rows are populated yet, the empty ones report "0"'s in those empty
rows. Is there any way to prevent that? Like if the cell in the core data
sheet is empty, then report nothing?

B.
Click to expand...
Click to expand...
 
That did the trick exactly!!
Wow... That saves so much headache. I have this workbook that is about ten
worksheets deep and 50 columns wide on the first sheet. I would like to
break it up into just the cord data on one sheet in one book. then have the
departments draw from that data into their own uniques sheets etc. I had
done it successfully, but the blank rows always showed "0"... across 50
columns. Now it will look great!! Thanks for your help.

B
 
Barney, you should probably take a look at Pivot tables, especially if you would
like to create tables of data for individual departments. Having ALL the source
data on a single sheet will make this so easy, and will still allow you to
create individual sheets for each dept.

If the workbook isn't too big, or can be zipped down, then assuming the data
lends itself to Pivot Analysis, I'll happily knock up a couple of dummy reports
for you to give you an example. The analysis options you will get out of being
able to use a Pivot table, I believe are truly second to none. You can create
amazing reports in *literally* seconds, simply by dragging a few fields around,
and I'll happily spend a few minutes trying to gain another convert :-)

Not every type of data works well for a Pivot table, though it can often be
normalised quite easily, but without seeing what you have it's kinda hard to
tell.

Some good intros:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm
 
Back
Top