Hi, I need to run a macro to filter the data from a query based on the value
of a cell. It is hard to explain so I have put a print screen picture at:

Http://www.nws-web.co.uk

If anyone has any ideas how I can make this work it would be great, I can't
copy the data manually as the worksheet will never actually be open it will
just run calculations in the background.

Many Thanks

Windows XP Professional
Office 2003

Bob1866 wrote:
Hi, I need to run a macro to filter the data from a query based on the value
of a cell. It is hard to explain so I have put a print screen picture at:

Http://www.nws-web.co.uk

If anyone has any ideas how I can make this work it would be great, I can't
copy the data manually as the worksheet will never actually be open it will
just run calculations in the background.

Many Thanks

Windows XP Professional
Office 2003

Maybe this. In cell I7, fill right:

=VLOOKUP($E$9,$I$11:$M$9999,1+COLUMN()-COLUMN($I:$I),FALSE)

Hi many thanks for your reply, I have entered the formula suggested into I7
which provided cell E9 shows 1234567891 then cell I7 also shows 1234567891,
however, If I put a different number in cell E9 i get an error in cell I7 of
#N/A. I am also not quite sure how to do the "fill right" to copy the rest of
the data in the row.

Many Thanks


"smartin" wrote:

Bob1866 wrote:
Hi, I need to run a macro to filter the data from a query based on the value
of a cell. It is hard to explain so I have put a print screen picture at:

Http://www.nws-web.co.uk

If anyone has any ideas how I can make this work it would be great, I can't
copy the data manually as the worksheet will never actually be open it will
just run calculations in the background.

Many Thanks

Windows XP Professional
Office 2003

Maybe this. In cell I7, fill right:

=VLOOKUP($E$9,$I$11:$M$9999,1+COLUMN()-COLUMN($I:$I),FALSE)

VLOOKUP will return #N/A if the value being sought (E9) is not in the
first column of the search range (column I).

"Fill right" is a shortcut for copying a formula to multiple cells. Here
are a few ways to do it:

- Click the cell with the formula (I7), then click the fill handle
(small square in the lower right corner of the cell), hold the left
mouse button down as you drag to select cells to the right. This works
for "fill down" as well.

- Click the cell with the formula (I7), then drag to select cells to the
right. Press Ctrl+R.

- Click the cell with the formula (I7), then drag to select cells to the
right. Navigate the menu to Edit | Fill | Right.

You can also copy cell I7, then paste in J7, K7, etc.

Hope this helps!


Bob1866 wrote:
Hi many thanks for your reply, I have entered the formula suggested into I7
which provided cell E9 shows 1234567891 then cell I7 also shows 1234567891,
however, If I put a different number in cell E9 i get an error in cell I7 of
#N/A. I am also not quite sure how to do the "fill right" to copy the rest of
the data in the row.

Many Thanks


"smartin" wrote:

Bob1866 wrote:
Hi, I need to run a macro to filter the data from a query based on the value
of a cell. It is hard to explain so I have put a print screen picture at:

Http://www.nws-web.co.uk

If anyone has any ideas how I can make this work it would be great, I can't
copy the data manually as the worksheet will never actually be open it will
just run calculations in the background.

Many Thanks

Windows XP Professional
Office 2003
Maybe this. In cell I7, fill right:

=VLOOKUP($E$9,$I$11:$M$9999,1+COLUMN()-COLUMN($I:$I),FALSE)

Hi, many many thanks for the formula, I have just applied the fill right and
that works perfectly, thanks for that, I do still have the same problem with
the #N/A though, I have manually entered one of the numbers from column I
into E9 but the only one I get a result wtih is 1234567891 the other numbers
all result in #N/A.

Many Thanks.

"smartin" wrote:

VLOOKUP will return #N/A if the value being sought (E9) is not in the
first column of the search range (column I).

"Fill right" is a shortcut for copying a formula to multiple cells. Here
are a few ways to do it:

- Click the cell with the formula (I7), then click the fill handle
(small square in the lower right corner of the cell), hold the left
mouse button down as you drag to select cells to the right. This works
for "fill down" as well.

- Click the cell with the formula (I7), then drag to select cells to the
right. Press Ctrl+R.

- Click the cell with the formula (I7), then drag to select cells to the
right. Navigate the menu to Edit | Fill | Right.

You can also copy cell I7, then paste in J7, K7, etc.

Hope this helps!


Bob1866 wrote:
Hi many thanks for your reply, I have entered the formula suggested into I7
which provided cell E9 shows 1234567891 then cell I7 also shows 1234567891,
however, If I put a different number in cell E9 i get an error in cell I7 of
#N/A. I am also not quite sure how to do the "fill right" to copy the rest of
the data in the row.

Many Thanks


"smartin" wrote:

Bob1866 wrote:
Hi, I need to run a macro to filter the data from a query based on the value
of a cell. It is hard to explain so I have put a print screen picture at:

Http://www.nws-web.co.uk

If anyone has any ideas how I can make this work it would be great, I can't
copy the data manually as the worksheet will never actually be open it will
just run calculations in the background.

Many Thanks

Windows XP Professional
Office 2003
Maybe this. In cell I7, fill right:

=VLOOKUP($E$9,$I$11:$M$9999,1+COLUMN()-COLUMN($I:$I),FALSE)

Hi again, the reason I am getting #N/A as the result is something to do with
text formatting I believe, if I manually type the number into E9 it returns
with #N/A but if I copy and paste the number into cell E9 from one of the
lookup cells in column I it works perfectly every time. Does anybody have any
ideas why this might be happening and if there is a solution for the problem.

Many Thanks

"Bob1866" wrote:

Hi, many many thanks for the formula, I have just applied the fill right and
that works perfectly, thanks for that, I do still have the same problem with
the #N/A though, I have manually entered one of the numbers from column I
into E9 but the only one I get a result wtih is 1234567891 the other numbers
all result in #N/A.

Many Thanks.

"smartin" wrote:

VLOOKUP will return #N/A if the value being sought (E9) is not in the
first column of the search range (column I).

"Fill right" is a shortcut for copying a formula to multiple cells. Here
are a few ways to do it:

- Click the cell with the formula (I7), then click the fill handle
(small square in the lower right corner of the cell), hold the left
mouse button down as you drag to select cells to the right. This works
for "fill down" as well.

- Click the cell with the formula (I7), then drag to select cells to the
right. Press Ctrl+R.

- Click the cell with the formula (I7), then drag to select cells to the
right. Navigate the menu to Edit | Fill | Right.

You can also copy cell I7, then paste in J7, K7, etc.

Hope this helps!


Bob1866 wrote:
Hi many thanks for your reply, I have entered the formula suggested into I7
which provided cell E9 shows 1234567891 then cell I7 also shows 1234567891,
however, If I put a different number in cell E9 i get an error in cell I7 of
#N/A. I am also not quite sure how to do the "fill right" to copy the rest of
the data in the row.

Many Thanks


"smartin" wrote:

Bob1866 wrote:
Hi, I need to run a macro to filter the data from a query based on the value
of a cell. It is hard to explain so I have put a print screen picture at:

Http://www.nws-web.co.uk

If anyone has any ideas how I can make this work it would be great, I can't
copy the data manually as the worksheet will never actually be open it will
just run calculations in the background.

Many Thanks

Windows XP Professional
Office 2003
Maybe this. In cell I7, fill right:

=VLOOKUP($E$9,$I$11:$M$9999,1+COLUMN()-COLUMN($I:$I),FALSE)