Previous Topic Next Topic

EXCEL HELP

Closed
Paul S Posts 2 Registration date Sunday July 7, 2013 Status Member Last seen July 17, 2013 - Jul 17, 2013 at 05:00 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - Jul 18, 2013 at 10:51 AM
Hello,

Novice needs help

I am trying to extract the contents of cells from several worksheets and summarize on one Header Summary Sheet

So i'm trying to generate on the Header Summary Sheet the contents of

Sheet2014001 cell B7
Sheet2014002 cell B7
Sheet2014003 cell B7
Sheet2014004 cell b7
and so on

I thought if i just went
=Sheet2014001$B$7
=Sheet2014002$B$7
=Sheet2014003$B$7

and then tried to drag the formulae down i would get
= Sheet2014004$B$7
=Sheet2014005$B$7
and the appropriate cell contents

but instead i get
=Sheet2014001$B$7
and the cell contents from that Sheet


Anyone have any idea how i can easily achieve what i want?

Any assistance would be greatly appreciated and thanks in advance
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
Jul 18, 2013 at 10:51 AM
Hi Paul,

I think the easiest way is to use a code: 
Sub Test()
Dim SheetRef, x As Long, rCount As Integer

x = InputBox("Starting from sheet 2014001 to which sheet reference would you like to go?:")

SheetRef = 2014001 - 1
rCount = 0

Do
    rCount = rCount + 1
    SheetRef = SheetRef + 1
    Cells(rCount, "A").Formula = "=Sheet" & SheetRef & "!B7"
Loop Until SheetRef = x

End Sub

Best regards,
Trowa

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses