Copy formula to reference next worksheet

**MAGICofSeth** · 02-20-2007, 02:13 PM

Hello,

I have my inventory based in excel. Each brand has it's own file. Each file has several worksheets with all of the items, prices, etc. on them. When I get a shipment, I fill out the next worksheet in line. This way- I have a record of my inventory and a worksheet for each shipment. It has been working very well. The first worksheet in each file is a summary page that shows me the grand totals, shipping charge, etc. from each filled in worksheet page. the problem is I have to build that summary page for each worksheet I fill in.

I would like a way to tell excel to give the same information as I requested in the previous row, but the next worksheet down.

Any Ideas?

Thank You!
--Seth

**Bryan Hessey** · 02-20-2007, 06:56 PM

Originally Posted by MAGICofSeth

Hello,

I have my inventory based in excel. Each brand has it's own file. Each file has several worksheets with all of the items, prices, etc. on them. When I get a shipment, I fill out the next worksheet in line. This way- I have a record of my inventory and a worksheet for each shipment. It has been working very well. The first worksheet in each file is a summary page that shows me the grand totals, shipping charge, etc. from each filled in worksheet page. the problem is I have to build that summary page for each worksheet I fill in.

I would like a way to tell excel to give the same information as I requested in the previous row, but the next worksheet down.

Any Ideas?

Thank You!
--Seth

Hi,

a small macro like

Sub BuildIndex()
Dim i As Long
Dim iTo As Long
iTo = Worksheets.Count
For i = 1 To iTo
    Range("A" & i + 5) = i
    Range("B" & i + 5) = Sheets(i).Name
    Next
    Range("C6:C" & iTo + 5).Formula = "=Indirect(B6&""!B5"")"
    Range("D6:D" & iTo + 5).Formula = "=Indirect(B6&""!E7"")"
    Range("E6:E" & iTo + 5).Formula = "=Indirect(B6&""!F12"")"

End Sub

would do that, just amend the B5 E7 and F12 etc as required.

hth
---

**MAGICofSeth** · 02-26-2007, 06:15 PM

This is a file for one brand. You will be able to see how each row's formulas relate to a worksheet.

Each row down relates to the next worksheet in line.

**Bryan Hessey** · 02-26-2007, 07:16 PM

Originally Posted by MAGICofSeth

This is a file for one brand. You will be able to see how each row's formulas relate to a worksheet.

Each row down relates to the next worksheet in line.

Hi,

I presume that you are looking to pickup totals from the sheets relative to the "Total Ret." cell, in which case if you replace the formula in K2,3,4 from

='I-51809'!H120
='I-52357'!H119
=inv3!H117

to

=INDEX('I-51809'!A:L,MATCH("Total Ret.",'I-51809'!H:H,0)+3,8)
=INDEX('I-52357'!A:L,MATCH("Total Ret.",'I-52357'!H:H,0)+3,8)
=INDEX(inv3!A:L,MATCH("Total Ret.",inv3!H:H,0)+3,8)

and if you clean-up B4 you can use

=INDEX(INDIRECT("'"&B2&"'!A:L"),MATCH("Total Ret.",INDIRECT("'"&B2&"'!H:H"),0)+3,8)

and formula fill that down the K column.

Similar would work for other columns also.

hth
---

**MAGICofSeth** · 02-28-2007, 03:44 PM

Thank you very much.

I cleaned up B4 and used the formula fill. It worked GREAT!

Would you mind giving me just one more of the same formula fill but with another column? Then I'll see what to change and be able to make my own formula fills for each column. It will be exactly what I need.

Thank you very, very much again!
--Seth

**Bryan Hessey** · 03-01-2007, 07:33 AM

Originally Posted by MAGICofSeth

Thank you very much.

I cleaned up B4 and used the formula fill. It worked GREAT!

Would you mind giving me just one more of the same formula fill but with another column? Then I'll see what to change and be able to make my own formula fills for each column. It will be exactly what I need.

Thank you very, very much again!
--Seth

Hi,

=INDEX(INDIRECT("'"&B2&"'!A:L"),MATCH("Total Ret.",INDIRECT("'"&B2&"'!H:H"),0)+3,8)

which 'other column'? (I wish I could remember what this formula did)

=INDEX(INDIRECT("'"&B2&"'!A:L"),MATCH("Total Ret.",INDIRECT("'"&B2&"'!H:H"),0)+3,8)

MATCH("Total Ret.",INDIRECT("'"&B2&"'!H:H"),0)

looks for 'Total Ret.' in column H of the sheet noted in cell B2, change B2 for another Sheetname, change H:H to find 'Total Ret/' in another column

=INDEX(INDIRECT("'"&B2&"'!A:L"), row +3,8)

column 8 = H - change to 9 for I etc.

does this help?
---

**MAGICofSeth** · 03-01-2007, 08:59 PM

Hello,

I found a formula to put the name of the current worksheet into a cell. It is as follows:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

It is almost perfect. I would like the name of the next sheet in line to be returned instead of the current sheet.

Any ideas?

Thank you very much,
--Seth

**VBA Noob** · 03-01-2007, 09:02 PM

Hi,

You can use code to do this. This link should help

http://www.cpearson.com/excel/sheetref.htm

VBA Noob

**MAGICofSeth** · 03-02-2007, 02:28 PM

This code works great for returning the next sheet name. Can a modifier be added to show the next, next sheet?

Then the next,next,next sheet?

The code in the cell looks like:

=NextSheetName()

Can I do something like =NextSheetName() +1
=NextSheetName() +2

That is obviously not working, but my ultimate goal is to be able to drag the formula down my column, and as it completes itself, the next sheet name, and then the following sheet name, and so on fills the column.

Thank you very much!!
--Seth

**VBA Noob** · 03-02-2007, 03:02 PM

Why don't you explain what you are trying to achieve.

That way someone might come up with a better solution

VBA Noob

**MAGICofSeth** · 03-02-2007, 04:03 PM

I have attached a file for reference.

I would like to be able to grab the bottom right corner of cell B2, and drag down the B column. The automatic fill would put in the names of the many sheets in the spreadsheet. When I add another sheet, I would be able to automatically fill in the new sheet's name in the next cell down in column B.

This comes from an excellent formula that fills in all the other cells data based on the information from Column B. Now I am looking for a way to automatically fill the cells in Column B.

Please see the attached file for a better explanation.

Thank you all very much for your help!
--Seth