Jump to content
workinglikeadog

Microsoft Excel help

Recommended Posts

Okay - have been googling and playing around (as really do want to learn how to enter formulas) and come up with the following - but it is saying it is either too short or something with symbols.  I am wondering if this is due to the suburb not matching the school exactly - suburb would be just Redlynch or Freshwater - but school will actually be Redlynch College or Freshwater Primary School.......

=VLOOKUP(I2,Sheet3!F2:F140,30,[FALSE])

I2 - suburb

sheet 3 F2:F140 - is the lookup list

30 - column number where to enter outcomes

FALSE as EXACT

 

 

Share this post


Link to post
Share on other sites

Skel,

Do you have a list of the suburbs that feed to particular schools? That's what vlookup will be looking for.

eg:

image.png.5062e0e7cbdfae3b49e024b52287f4bb.png

  • Like 1

Share this post


Link to post
Share on other sites

Yes - but not that specific (just suburbs and a list of schools) - not sure how to set up a list that would be that specific to be incorporated into a formula

That type of list be much better as various suburbs do feed into one school!

Edited by skel

Share this post


Link to post
Share on other sites

Is there not a designated catchment area for each school. Dept of School Education should be able to supply it. 

If it’s not a neat division, that is if one suburb feeds several schools, then you need more address information than just a suburb name. Indeed, it goes beyond a simple spreadsheet problem and moves into the world of GIS and mapping. 

Share this post


Link to post
Share on other sites

That what I am now thinking.  So if was to go back to my original idea, just aligning a generally suburb to school - will it work if I have Redlynch College and the suburb is just Redlynch.   Thanks for your patience _ am googling and You Tubing at the same time too.....

This is what I tried initially,  but is comes up with an error

=VLOOKUP(I2,Sheet3!F2:F140,30,[FALSE])

Being:

i2 - suburb manually entered

sheet 3 F2:F140 - is the lookup list

30 - column number where to enter outcomes

FALSE as want an 'exact match'

Share this post


Link to post
Share on other sites

Try this

=VLOOKUP(I2,Sheet3!F2:AI140,30,[FALSE])

 

For that formula to work the table on Sheet 3 would need to be sorted by suburb and the suburbs would need to be listed in column F and the relevant school listed in Column AI (i.e 30 Columns to the left of the suburb)

Share this post


Link to post
Share on other sites

No  - think I am missing something simple and just can't work it out.  Amazingly,  there is no one in our regional office who can help either....:mellow:

I did change one thing in that it would 21 columns from column F where the data goes.....(column AD)

Enjoying my first day back....lol.  This is how it is set up at the moment, so if Mossman was typed in as the suburb, Mossman SS would be the catchment school.  Are my list set up wrong?

Capture.PNG

2.PNG

Edited by skel

Share this post


Link to post
Share on other sites

The table with schools and suburbs has to be sorted alphabetically by suburb for vlookup to work 

Share this post


Link to post
Share on other sites
3 hours ago, Limited said:

The table with schools and suburbs has to be sorted alphabetically by suburb for vlookup to work 

Not so. Use the False flag within vlookup and it only returns a precise match. Regardless of table order. I can recollect the one application (in 20+ years of being a serious excel user)  where I needed to omit the false. 

Share this post


Link to post
Share on other sites
4 hours ago, skel said:

No  - think I am missing something simple and just can't work it out.  Amazingly,  there is no one in our regional office who can help either....:mellow:

I did change one thing in that it would 21 columns from column F where the data goes.....(column AD)

Enjoying my first day back....lol.  This is how it is set up at the moment, so if Mossman was typed in as the suburb, Mossman SS would be the catchment school.  Are my list set up wrong?

Capture.PNG

2.PNG

Skel I think you are over thinking this. I assume that any suburb can only feed only one school, but one school might be fed by one or more suburbs. Correct?  This is called a one to-many relationship, perfect for excel lookups. 

Then you need a very simple lookup reference table. I’d put it on its own tab within your spreadsheet. The lookup needs just two columns

A a list of every suburb, one line each (ie, they are unique)

B the school that is fed by that suburb.  The same school could appear in more than one line. 

If one suburb feeds more than one school this won’t work. This is a many-to-many relationship that won’t work using lookup. You would need to split the suburb up somehow (usually by street address) which is not a simple spreadsheet application. 

 

 

  • Like 1

Share this post


Link to post
Share on other sites
8 hours ago, skel said:

30 - column number where to enter outcomes

Your Vlookup would try to RETURN a value from column 30 in your lookup table, but your lookup table only has one column.

 

Edited by Tyno

Share this post


Link to post
Share on other sites

Thanks everyone!!! So I think I  understand that I need to have suburb and school in a separate worksheet - this then links to "suburb" when entered in on the main spreadsheet.....  For the purpose for this spreadsheet, simple suburb / school will work as feeder concept will be too complex (as many suburbs could go to one school).

So.....this still didn't compute (but think I am getting closer)   =VLOOKUP(I2,Sheet2!A1:B10,2,[FALSE])

(sheet 1) Column I = Suburb (as entered by the admin)  on the main tracking spreadsheet

(sheet 1) Column AD - where I want the school name to be populate

(sheet 2) Suburb / school - column A/B (as per picture below)

Do I have the first value wrong?

Ref table.PNG

Edited by skel

Share this post


Link to post
Share on other sites

I DID IT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

:o:o:o:o:o:o:o:o:o:o:o:o

 

Thanks everyone!  :-)  Feeling pretty chuffed with myself......I just needed to remove the titles suburb / school

  • Like 2
  • Thanks 1

Share this post


Link to post
Share on other sites

One more slight problem - the document size has now grown to 40MB.  I have googled and worked through the options suggested, like clearing all the remaining cells in each worksheet (which reduced it from 128MB).  There are only 5 worksheets, 3 with data (about 600 lines each, 30 columns) and the other two are data list only (very minimal).  Only one of the worksheets has working formulas and is only set up for 500 rows. Would the VLOOKUP formula have added that much?  There are two others formula in play - one for calculating age and start date for Prep. 

Share this post


Link to post
Share on other sites

Do you have any links to other files, or data generated by formulas, that you can now copy & paste back in as straight data?

Share this post


Link to post
Share on other sites

Checked all the others aspects above. Nothing as listed.

Have redone the clear all other cells and it has gone to 20MB - so getting a big better. :D

Edited by skel

Share this post


Link to post
Share on other sites
16 hours ago, Tyno said:

ctrl-c -> ctrl-v into new sheet.

 

Sometimes the obvious is the answer - thank you everyone for your help.  I now really do understand how to do VLOOKUP and ensuring documents are the right size. :D  And I know the service is also super delighted with update and snazzy version! It will make it so much easier to track incoming referrals and actually run report on the volume of request!

Share this post


Link to post
Share on other sites

Hello......my six monthly check in.....

I am trying to gather together data for two sunsets of schools, 8 in one group and 9 in the other.  The senior leadership want baseline data for each group. 

Some data is simply just adding and divding, as I can access raw number. Some data sets are in percentages for that school.   Simple if it was just a single school. But one school may have 100 students, another 50.  Is there a formula to work backwards to go from a percentage to a number.  Once I have the raw numbers I can work out totals and graphs.

So, 78% of 215, then 82% of 67 etc etc.

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×