Questions

Can I do the following in Excel

Tags:
+
0 Votes
Locked

Can I do the following in Excel

bursar
I am working on a Excel workbook which has a number of identically laid out worksheets. The worksheets contain info for different schools and each worksheet is named according to the school the info is related to. I want to organise the information of these worksheets onto an overview page. Currently I am using a 'VLOOKUP' function (e.g. VLOOKUP($A4,'Betty Layward Primary'!$B$13:$G$107,6,FALSE) to do this (where Betty Layward Primary is one of the schools in question - each time I move onto a new line I have to physically type out the name of the new school in this part of the function) but I wondered if there was a more 'automated' way.

Is there a way in which I can rig the function/formula in such a way that the formula automatically works its way from worksheet to worksheet automatically inserting the 'right name' in this part of the function and returning values in the overview page.

I would be grateful for any pointers

Many thanks
  • +
    0 Votes
    ThumbsUp2

    However, you can speed it up by using the mouse instead of typing the name of the sheet.

    After you've typed the beginning of the VLOOKUP formula and when it's time to put in the name of the worksheet, just use the mouse to select the sheet and then either continue typing the formula or use the mouse to highlight the cell range and THEN continue typing, pressing enter when you're done.

    Sorry, but that's as close to automation that you're going to get, short of learning VB and writing a subroutine (macro) to do it for you.

    +
    0 Votes
    seanferd

    can you set up the formula to pull up the appropriate info so that you can copy it and paste it to a set of cells like you can do with formatting? Forgive me if I'm way below your experience level here.

    Say that Betty Layward Primary is text in a cell Sheet1 A1, then: =(Sheet1!A1) as a formula in any cell will give you "Betty Layward Primary". I'm not sure what you'd need to adjust in the rest of your original function, but any operations between relative cells within the worksheet are accounted for by copy/paste function. For instance, D2-C2 can be copied, pasted to an entire column E, and the next line would automatically be D3-C3, and so on. You would only need to do this for a bit of the column, and Excel will continue to propagate this don the column as new entries are added.

    Again, I apologize if you are already way beyond this.

  • +
    0 Votes
    ThumbsUp2

    However, you can speed it up by using the mouse instead of typing the name of the sheet.

    After you've typed the beginning of the VLOOKUP formula and when it's time to put in the name of the worksheet, just use the mouse to select the sheet and then either continue typing the formula or use the mouse to highlight the cell range and THEN continue typing, pressing enter when you're done.

    Sorry, but that's as close to automation that you're going to get, short of learning VB and writing a subroutine (macro) to do it for you.

    +
    0 Votes
    seanferd

    can you set up the formula to pull up the appropriate info so that you can copy it and paste it to a set of cells like you can do with formatting? Forgive me if I'm way below your experience level here.

    Say that Betty Layward Primary is text in a cell Sheet1 A1, then: =(Sheet1!A1) as a formula in any cell will give you "Betty Layward Primary". I'm not sure what you'd need to adjust in the rest of your original function, but any operations between relative cells within the worksheet are accounted for by copy/paste function. For instance, D2-C2 can be copied, pasted to an entire column E, and the next line would automatically be D3-C3, and so on. You would only need to do this for a bit of the column, and Excel will continue to propagate this don the column as new entries are added.

    Again, I apologize if you are already way beyond this.