+ Reply to Thread
Results 1 to 6 of 6

How to do reference particular cell to a page in excel?

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    bhutan
    MS-Off Ver
    Excel 2007 $ 2010
    Posts
    12

    How to do reference particular cell to a page in excel?

    Hi everybody
    Help me to link a particular cell to many pages in excel. In my when i drag the pages, Rollno are not coming sequentially. Here is my worksheet (Result.xls)

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to do reference particular cell to a page in excel?

    Not entirely sure what you're asking for but try

    =ROW()/33+1

    in your Result sheet column B Roll Number cells.

    Then in for example B5 use
    =INDIRECT("'DZO-I'!C"&B1+6)

    However for the very reason that you've found in that it's not straightforward to summarise your results, you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.


    A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


    You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
    You will also throw open the whole wonderful world of the powerful Pivot table functionality.

    If you look at your course sheet they are all essentially the same layout. You should therefore create a single database sheet much like your current course layout but with an additional column which identifies the Course. You then only need to add a persons record where they actually attend the course.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-25-2013
    Location
    bhutan
    MS-Off Ver
    Excel 2007 $ 2010
    Posts
    12

    Re: How to do reference particular cell to a page in excel?

    Hi Richard
    Can you see data on "resultsheet"Result.xls. That result is for Roll No 1 in a class. In similar manner i want to have result for following rollno automatically when i drag it. But when i dragged it, it is not coming automatically rather it is showing result of rollno 32.

    Thank you

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to do reference particular cell to a page in excel?

    Hi,

    This workbook is subtly different to the previous one. The second Roll Number is now on row 32 not row 33 as before
    Your B1 cell still shows
    =Information!A7 which means that you haven't changed it as I suggested.

    Using your original file
    B1 should be
    =Round(Row()/33+1,0)

    B33 should be
    =Row()/33+1

    When you copy B33 to B66 etc.. the roll number will increment by 1

    Then you need to use the B5 formula I gave you and copy it to the other reports. If your sheet names exactly replicated the labels in column A you could use the A column value in the Indirect function instead of the actual sheet name. This would allow you to copy the Indirect formula across the sheet as well as down.


    You have not changed it to

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: How to do reference particular cell to a page in excel?

    Normalizing the data should always be the first priority because the analysis or summary development afterward is MUCH easier, as Richard has described.

  6. #6
    Registered User
    Join Date
    07-25-2013
    Location
    bhutan
    MS-Off Ver
    Excel 2007 $ 2010
    Posts
    12

    Re: How to do reference particular cell to a page in excel?

    Thank You Richard for helping me.. It is working..Great!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can I reference & sort 'chapter-page' in a cell?
    By tedtables in forum Excel General
    Replies: 9
    Last Post: 09-27-2011, 05:17 PM
  2. Reference cell from top row of each page in header
    By mzemble in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-06-2009, 12:45 AM
  3. [SOLVED] Excel: can I refer to cell reference in page setup header
    By nikos in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-21-2006, 02:25 PM
  4. Data in cell to reference a worksheet page
    By eklarsen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-15-2005, 06:52 PM
  5. Print page with cell as footer reference
    By Hellboy in forum Excel General
    Replies: 3
    Last Post: 05-10-2005, 11:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1