Prev: Count between two dates using sumproduct
Next: Using Excell , have ######### showing up in the cells. Why
From: mhmyers40241 on 25 Feb 2010 17:43
I am designing an invoice in Excel 2007 that draws data from a query in
Access 2007. I have constructed a 2-worksheet workbook; Sheet 1 is the
formatted invoice, and Sheet 2 is linked to the Access query results. Sheet 2
is doing its job perfectly. Part of Sheet 1 references the Sheet 2 data in
the same list format. (Sheet 2 is named "Account Data".) Each cell in Sheet
1's range has a formula like this:

=IF('Account Data'!C3<>"",'Account Data'!C3,"")

Only the cell references change. These formulas have been filled down
through 28 rows. As each invoice will be different, the number of rows of
data will change as well; 28 rows available for data seemed more than enough
for our purposes. The template I'm trying to create refreshes the Access data
it's importing each time a new spreadsheet is created, and that works fine.

So here's the hitch in Sheet 1's giddyup: the list in Sheet 1 shows the
first few rows accurately--the number of rows shown correctly varies with
each new import--then the next and final row displayed is actually the last
row in Sheet 2's list, omitting all the rows in-between. I have tried
everything I can think of to get Sheet 1's list match Sheet 2's list, but to
no avail. Any suggestions?
From: Max on 25 Feb 2010 18:18
One guess, the process that's happening in Sheet2 ("Account Data"?) may be
one that's destroying/messing up your link formulas each time it occurs. And
it's beyond your control. You could try using "fixed" pointing link formulas
via INDIRECT eg:
=OFFSET(INDIRECT("'Account Data'!C3"),ROWS($1:1)-1,COLUMNS($A:A)-1)
Above returns the same as: ='Account Data'!C3
with the difference that it won't be affected by any mutilation occuring in
'Account Data', and you can then copy it across/fill down to link complete
areas just as easily as the simple link. If you need to suppress extraneous
zeros resulting from empty cells in the source, frame it like this:
=IF(OFFSET(...)=0,"",OFFSET(...))
Any joy? hit YES below
--
Max
Singapore
---
"mhmyers40241" wrote:
> I am designing an invoice in Excel 2007 that draws data from a query in
> Access 2007. I have constructed a 2-worksheet workbook; Sheet 1 is the
> formatted invoice, and Sheet 2 is linked to the Access query results. Sheet 2
> is doing its job perfectly. Part of Sheet 1 references the Sheet 2 data in
> the same list format. (Sheet 2 is named "Account Data".) Each cell in Sheet
> 1's range has a formula like this:
>
> =IF('Account Data'!C3<>"",'Account Data'!C3,"")
>
> Only the cell references change. These formulas have been filled down
> through 28 rows. As each invoice will be different, the number of rows of
> data will change as well; 28 rows available for data seemed more than enough
> for our purposes. The template I'm trying to create refreshes the Access data
> it's importing each time a new spreadsheet is created, and that works fine.
>
> So here's the hitch in Sheet 1's giddyup: the list in Sheet 1 shows the
> first few rows accurately--the number of rows shown correctly varies with
> each new import--then the next and final row displayed is actually the last
> row in Sheet 2's list, omitting all the rows in-between. I have tried
> everything I can think of to get Sheet 1's list match Sheet 2's list, but to
> no avail. Any suggestions?
 | 
Pages: 1
Prev: Count between two dates using sumproduct
Next: Using Excell , have ######### showing up in the cells. Why