Query combined with formula

[Jesper Lützen]

Hi,

I have made a query, that gets the parameter from my cell A11. There
is always only one result, which is written i cell B11.

Now i wish to copy that formula from A11 to the area A12:A3000, so it
picks up the parameter from the row, where the formula is placed. The
result should be, that a query request data based on A11 and spit it
out in B11; another query uses A12 and spits out in B12 (...)

Now I need it for now in 3000 rows, and that might expande drasticly
over time, so how do I easy copy it down?

Regular copy/paste only copy the formula or value of the cells, not
the queries...


Kind regards

Jesper Lützen

 

[KC Rippstein]

I am not skilled in VBA, but I believe you could use a worksheet change
event macro that would kick in whenever a change is made to a cell in the
range A11:A65536. Then it would pick up that changed cell as the "input"
cell, apply the macro formula you want, and then you can use the offset
function to set the result in the same row in column B.
This is all just a guess, but since no one had responded yet, thought I'd
take a shot at this suggestion.

Hi,

I have made a query, that gets the parameter from my cell A11. There
is always only one result, which is written i cell B11.

Now i wish to copy that formula from A11 to the area A12:A3000, so it
picks up the parameter from the row, where the formula is placed. The
result should be, that a query request data based on A11 and spit it
out in B11; another query uses A12 and spits out in B12 (...)

Now I need it for now in 3000 rows, and that might expande drasticly
over time, so how do I easy copy it down?

Regular copy/paste only copy the formula or value of the cells, not
the queries...


Kind regards

Jesper Lützen

 

[Jesper Lützen]

Hi KC,

Thanx for the response. It sounds like an idea worth looking into.

Kind Regards

Jesper