Changeable references - how best to handle?

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
Doing a daily report, in which I import 2 sheets to be processed, we'll call ClassReport and GradeData.
Both have a varying number of rows, but have the same general setup each day. Say I want to pull in grade percentage for each student into Class Report, doing a vlookup referencing Grade Data.

Trying to automate this all into VBA, so please bear with me:

ClassReport:
vlookup-goes-here"""
DataEtc.Jenny (C2)Sam(D2)Amy(E2)Chris(F2)
......datadataetc....
..................

<tbody>
</tbody>

GradeData:
Assignment
Date
HouseName% to date
xAmy95
xChris82
xDani94
xJenny88
xMarc88
xSam78

<tbody>
</tbody>

(Sure there's only one kid with each name in the class - roll with it.)

Sometimes there's 15 rows in Grade Data, other times there's 20, etc.

So I put my vlookup in Class Report:
=vlookup(C2,'GradeDataSheet8-18'!B4:C9,2,False)

I usually just fill across, so I have to add Absolute references after I type that. (...$B$4:$C$9)

Two questions:
1) Since I'm trying to automate this into a macro, how would I establish the range in GradeDataSheet every day? If it was the same thing in the same sheet, I'd define the name as "GRADES" and sub that into my vlookup.

I already have figured out how to do a LastRow and LastColumn declaration on there, so I could use that, but how do I get my code to recognize that today's sheet is the 8-18 one?

2) If I am able to answer question #1, how can I use Absolute references with variables like LastColumn (an integer)?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Think I made it too complicated, but it won't let me edit it now. I think I'll try a smaller, bite-sized question and go from there...
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top