Excel Formula

[NickoTheSicko]

Hello, im currently fixing some data sheets for a school i work at im using formula in Excel to vlookup, im having to go to each tab to vlookup the information, i thought here was a way to slide down on the table and it would automatically populate the table, any ideas on what formula to do to make it easier so i dont have to go to each tab all the time.

 

Cheers

[howartp]

Can you explain more what the problem is?

 

Could you anonymise data without scrubbing it out so we can understand the sheet better?

[TechMonkey]

If the VLOOKUP is on each tab then you will have to visit each tab and fill the formula down. Don't forget to use $ to anchor references, otherwise as you fill the formula down things like the range will change as you go down the column.

[NickoTheSicko]

Can you explain more what the problem is?

 

Could you anonymise data without scrubbing it out so we can understand the sheet better?

 

Apologies for the rubbish description of the issue, im not good at explaining things lol anyway, im trying to vlookup for Attendance, Gold Days and Silver Days. Is there a way to do this instead of doing it manually for every person? I would have to click in the first box, go to formula, then vlookup, then select the name and then where the attendance box is on there table then press enter. Im sorry if thats a worse explanation but i thought you could just drag the box down and it would automatically complete it for you.

[clareq]

You can drag down. If there are any cell references you don't want to change you need to "anchor" them by putting a $ in front of the figure you don't want to change - so if you want to reference B1 every time for example, use $B$1 in the formula. It might help if you showed us the formula you are working with in your screenshot - press CTRL+` (the key next to the 1) to show them.

[howartp]

Ok.

 

Yes, get the formula in the first students' box working.

 

So it'll be something like =vlookup(a3,a1:b15,0,false) depending where data coming from.

 

Then you need to change a1:b15 into $a$1:$b$15 before you drag-down.

 

That'll give you:

=vlookup(a3,$a$1:$b$15,0,false)

=vlookup(a4,$a$1:$b$15,0,false)

=vlookup(a5,$a$1:$b$15,0,false)

so it's looking up a3, a4, a5, ... in the same table a1:b15

 

Otherwise when you copy down you'll get:

=vlookup(a3,a1:b15,0,false)

=vlookup(a4,a2:b16,0,false)

=vlookup(a5,a3:b17,0,false)

ie the table it's looking up is moving which you don't want.

[clareq]

@howartp That's a much better explanation than mine!