Software·43,881 discussions

Can I do the following in Excel

Locked

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

Topic

Clarifications

In reply to Can I do the following in Excel

Clarifications

No, there is no way to automate it.

In reply to Can I do the following in Excel

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.

A bit more complex than what I’ve done before, but

In reply to Can I do the following in Excel

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.

Darn it, and I reloaded to see if there was another post before I submitted

In reply to A bit more complex than what I’ve done before, but

Just follow ThumbsUp2’s advice.

LOL!!! We pushed the button at the same time…. :D

In reply to Darn it, and I reloaded to see if there was another post before I submitted

.

[Author]

Replies