Small business need one question An

March 17, 2009 at 10:15:16
Specs: Windows XP
Hi, I need to be able to Fill in a seiries of sheets with the same cell#. Is there a way to do this with excel? example:
Sheet3!B7 and so on is there a way to fill for this?

I am running web queries from my website and putting the individual products on each sheet, then using a summary sheet be able to work with the data and see what I have got for inventory, sales, ect.
All of the HTML Table information seem to line up just fine in regular places on each sheet, example Sheet1!B7, Sheet2!B7, I would just like to not have to type the whole series from Sheet1!B7 to Sheet500!B7 on the summary sheet. Any Ideas?
Thank you very much sincerely
Caralyn Kintz

See More: Small business need one question An

Report •

March 17, 2009 at 10:57:00
One way would be to put this in A1 and drag down:


The INDIRECT function looks at the text inside the parenthesis and uses it as a cell reference. The Row() function will pick up the Row number for current Row as you drag it down.

If you aren't starting in Row 1, but need the first formula to reference Sheet1, just do some simple math. Let's say you are starting in Row 5:


will also reference Sheet1.

The main drawback of this method is that when you (or worse yet, somebody else) looks at this formula next year, they're going to have to think about what it's doing since it's not as simple as =Sheet1!B7, but returns the same result.

Another option is a macro...

To fill Column A:

Sub InsertFormula()
 For NxtRow = 1 To 500
  Cells(NxtRow, 1).Formula = "=Sheet" & NxtRow & "!B7"
End Sub

This will put the simple formula (e.g. =Sheet1!B7) in the cells so that there will be no confusion next year.

Report •
Related Solutions

Ask Question