Forum Discussion
New data sheet - same fill in form
I just answered the earlier version of the same question, with the same feedback--too vague!--that Rodrigo_ gave you. Thank you for posting a copy of the workbook. Now that I've seen it, I will restate what I said before about keeping a single data source for that form.
The form is NOT filled in via macro. Why this is an XLSM file, I don't know, but you do NOT need a macro. It uses the classic VLOOKUP function to retrieve the data from any given row in that table. You enter the row number up in cell A1 (as the shared example has it) and for each entry in the form, VLOOKUP looks at a specific column. Here's the formula for the date. All of the subsequent formulas in the "Raptor Program" tab are essentially the same, except for that last '2' in this one. It tells VLOOKUP to go to column 2 of row 3 (the 3 coming from the reference in cell $A$1)...
=VLOOKUP($A$1,Black_Hills_Raptor_Data,2,FALSE)
Here's a reference where you can learn how VLOOKUP works.
What you'd be wise to do, however, is to clean up the tab containing the "raw data." Unless what you've shown has been greatly modified, it contains far too many blank rows. Make it an Excel Table, fill in the columns as needed. You do not need a new sheet for 2024. (It's not surprising that when you created one the formula didn't retrieve the data.) It could be modified so it would work with annual sheets, but that would just be putting a bandaid on the design issue. Instead, learn how VLOOKUP works, and put it to work retrieving data for that form from that single data sheet from now until forever.
Each year a number of items (columns) need to be tallied for federal and state reports. Am I understanding you are saying to continue 2024 on the same sheet? (I know we can write a formula to tally a range of rows.) Thank you also for the link for VLOOKUP.
You are indeed correctly understanding me. I would suggest you make sure that the year pertaining to each row is stated clearly, if not, in fact, a complete date. How "granular" you need to be is a function of the various reports you are responsible for.
And yes, by continuing year after year in the same database you will find the Pivot Table functionality of Excel incredibly useful in putting together those annual tallies, comparing one year with the next, etc. In addition to that link just above, you'll find many YouTube videos that will help you understand the utility of the Pivot Table.
