[RESOLVED] Query Table Headers - please explain

[doasidont]

When I manually create a query within Excel to retrieve data (let's say four fields) from an Access database and direct that data to a new worksheet, four columns are populated with data and the number of rows populated match the number of records retrieved. The headers on these columns correspond to the field names within the Access table being retrieved. If I have previously created additional columns on the worksheet that contain formulas that manipulate the retrieved query data, the number of rows in these 'extra' formula columns are automatically lessened or increased to match the number of records retrieved by the query. So far so good.

Here's my question (problem). I want to set the column header on one of these 'extra' formula columns to contain a formula that computes values held on another sheet (e.g., "= sheetname.[datefield]"). Whether I enter this formula manually or with VBA into the header of any of these 'extra' columns, the value resulting from the formula is '0', not the date.

How can I, if it's possible, set the header of a user created column on a query table sheet to a formula? Thanks.

[sparbag]

Hello,

I can't fully answer your question but I can offer a link to information that may well assist.
http://www.vbforums.com/showthread.php?t=610571

Kind regards

Steve

[doasidont]

Thanks, but the link talks about 'hasformula'. I need to place a working formula in one of the header cells of the query table.

[sparbag]

Hello,

I understand the issue now. Having just pulled some data from Access it appears to lock the entire sheet. I couldn't even change an empty cell to a formula.
I attempted to unlock the cells, which it apparently did, for it to refuse to allow a formula. I can see why you find it irksome.
Very puzzling.

Kind regards

Steve

[doasidont]

I worked around it by insert a row ahead of the query data. The new row accepts formulas. Thanks.