New data sheet - same fill in form

[StumpedinBlackHills]

I have a workbook that has one sheet with a lot of data. Another sheet in the same workbook is a form that fills in data automatically using an assigned line number from the data sheet - I believe a macro was used to create this. I would like to create a new data sheet for this coming year but still be able to fill in the same form using the information in the new data sheet. How does the form know which data sheet to get the information from?

[#2]

Your query is so vague, what kind of form? from what data? be more elaborate.. Since we're talking about Macro, you MUST be more specific from every details you want to do such as; what sheet name, cells (rows/column), what they should do.. or you could do sharing your file thru uploading it here or via cloud/drive.

[#3]

@Rodrigo_ Thank you for your input. Attached is the file. The first sheet "Raptor Program" is the form that is filled out for each presentation. At the very top left is a cell with a number. This corresponds to the number in column A in the second sheet, "BHRC Data." Certain data from that row is filled in on the Program Sheet. The current BHRC Data sheet reflects a sample of 2022. We would like to create a new sheet in this same workbook for 2024. However when I have done this, The Raptor Program sheet is not picking up any row from 2024. I have attempted to see the macro, if any, or determine where I need to make the change either on the Raptor Program sheet or the BHRC Data sheet. Does this make better sense? Is what I am trying to do even possible or do I just have to make a new worksheet and start over for 2024? Again thanks.

[#4 · Accepted solution]

@StumpedinBlackHills 

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.

[#5]

Thank you. The one I posted was a sample that was started in the fall of 2022. There will probably be close to 100 programs (rows) filled in each year. 2023 has at least 80 rows and is currently a separate sheet/tab. 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.

[#6]

@StumpedinBlackHills 

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.

[#7]

Thank you again for all of your help, it is really appreciated.