Excel skipping referenced rows 1

[itprsn]

First sheet is SUMMARY1, summary information pulled in w/MS Query:

Press No.
1 (cell A2)
2 (cell A3)
3 (cell A4)
4 (cell A5)
5 (cell A6)
6 (cell A7)
7 (cell A8)
8 (cell A9)

Second sheet references first sheets info as follows:

=IF(summary!A2<>"",summary!A2,"")
=IF(summary!A3<>"",summary!A3,"")
=IF(summary!A4<>"",summary!A4,"")
=IF(summary!A5<>"",summary!A5,"")
=IF(summary!A6<>"",summary!A6,"")
=IF(summary!A7<>"",summary!A7,"")
=IF(summary!A8<>"",summary!A8,"")
=IF(summary!A9<>"",summary!A9,"")

The data returned should be:

Press No.
1
2
3
4
5
6
7
8

Instead the data returned is

Press No.
1
2
3
4
8

For the data returned the logic for press 4 says =IF(summary!A5<>"",summary!A5,""). The logic for the very next row shows to be =IF(summary!A9<>"",summary!A9,"") - it completely skips =IF(summary!A6<>"",summary!A6,""),
=IF(summary!A7<>"",summary!A7,""),=IF(summary!A8<>"",summary!A8,"")rows altogether.

Any ideas what could cause this??

Many thanks!

 

[ck1999]

How can the second sheet have those references and not give you the correct answer?

Are these the formulas before and after the query is ran?

Did you delete any rows in sheet2

ck1999

 

[itprsn]

Doesn't seem logical but that is what's happening. Yes, those are the formulas before and after. No rows were deleted...

 

[ck1999]

So you only have 5 formulas instead of 8 in sheet2

Have you tried using summary!$A$7 to see if this matters?

Are you using a macros for this query?

 

[itprsn]

Yes, it returns only the five formulas. Yes, tried using summary!$A$7, same thing. No macros...

 

[GlennUK]

That's the problem with using MS Query, the changing returned range screws up references sometimes ( I think you can alter the settings to prevent this but here is a workaround anyway ).

To stop any referencing formula from moving use the INDIRECT function, like:

=INDIRECT("summary!A"&ROW())

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

This is dependant on an option in MS Query

Right click on the data set returned and choose "Data Range Properties"

Ensure that the option for "Overwrite exisiting cells with new data, clear unused cells" is chosen

The other options will insert and or delete rows which is causing your formula reference problems

Not sure why you are referencing the data on a seperate sheet though - you could avoid this altogether by placing the formulae next to the data that is returned and ticking the "Fill down formulas in columsn adjacent to data" option...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

thanks Geoff ( xlbo ):

This is dependant on an option in MS Query

Right click on the data set returned and choose "Data Range Properties"

Ensure that the option for "Overwrite exisiting cells with new data, clear unused cells" is chosen

The other options will insert and or delete rows which is causing your formula reference problems
{snip}

I don't know much about MS Query, as most of my life I've been downloading data from mainframes instead. I thought there was something like that ... now I know ===> *


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[itprsn]

Excellent, I changed the option in MS Query - works perfect!

Regarding referencing the data on another sheet, I actually just showed one column of the data but there are many more. My second sheet actually pulls the data from several sheets and formats it...

Thanks again!