Inserting formula

  • Thread starter Thread starter Reggie
  • Start date Start date
R

Reggie

Hi and TIA. I have a query that I'm exporting from Access to excel. I want
to insert a formula (Sum) at the bottom of columns B thru F. The problem I
have is that the number of rows changes each time I export the query so I
can't simply code it as =Sum(B1:B14) because there may be only 10 rows or
more rows. I've tried using variables, but haven't had any luck. Any
advice is highly appreciated. Thanks!

--
Reggie

"Half this game is 90% mental."

----------
 
Thanks Arvi. Good idea. I think I will actually place these named ranges
out to the right of my worksheet. Once the sheet is populated I will locate
the last row, move to the next row and set a reference to those cells to the
named range's as you described. I'll give it a shot. Thanks for your time
and quick response.

--
Reggie

"Half this game is 90% mental."

----------
Arvi Laanemets said:
Hi


Have some empty rows at top of worksheet (above query table), and place the
sum there.

An ecample:
Your query results table (on sheet 'Sheet1') header is at row 4, and you are
sure that last row never exeeds 100'th row
Define the named range p.e.
ColBResults=OFFSET(Sheet1!$B$5,,,COUNTIF(Sheet1!$B$5:$B$100,"<>")
Into some cell at top of worksheet (p.e. B2) enter the formula:
=SUM(ColBResults)


Arvi Laanemets


Reggie said:
Hi and TIA. I have a query that I'm exporting from Access to excel. I want
to insert a formula (Sum) at the bottom of columns B thru F. The
Click to expand...
problem
Click to expand...
 
Ooops!.. My method wont work. I'll stick to Yours!

--
Reggie

"Half this game is 90% mental."

----------
Reggie said:
Thanks Arvi. Good idea. I think I will actually place these named ranges
out to the right of my worksheet. Once the sheet is populated I will locate
the last row, move to the next row and set a reference to those cells to the
named range's as you described. I'll give it a shot. Thanks for your time
and quick response.

--
Reggie

"Half this game is 90% mental."

----------
Arvi Laanemets said:
Hi


Have some empty rows at top of worksheet (above query table), and place the
sum there.

An ecample:
Your query results table (on sheet 'Sheet1') header is at row 4, and you are
sure that last row never exeeds 100'th row
Define the named range p.e.
ColBResults=OFFSET(Sheet1!$B$5,,,COUNTIF(Sheet1!$B$5:$B$100,"<>")
Into some cell at top of worksheet (p.e. B2) enter the formula:
=SUM(ColBResults)


Arvi Laanemets


I
want problem
Click to expand...
so
Click to expand...
 
Hi


Have some empty rows at top of worksheet (above query table), and place the
sum there.

An ecample:
Your query results table (on sheet 'Sheet1') header is at row 4, and you are
sure that last row never exeeds 100'th row
Define the named range p.e.
ColBResults=OFFSET(Sheet1!$B$5,,,COUNTIF(Sheet1!$B$5:$B$100,"<>")
Into some cell at top of worksheet (p.e. B2) enter the formula:
=SUM(ColBResults)


Arvi Laanemets
 
Back
Top