VLOOKUP and OFFSET ? Where to begin ??

[Logit]

.
I'm in need of a macro that will search for the Project Name located in Col J, compare it with
the Project Names in Col A, copy the values in Col B & Col C and update the appropriate
Month Column for that Project Name.

It will need to be a macro, so once the month changes the figures for that month won't in Cols
K:V

The tables located in Cols J:V may extend a number of rows down on the sheet and will have 12
blank rows between them. The layout is to allow the insertion of individual charts.

Honestly ... I'm lost. Have no idea where to begin or end ... other than VLOOKUP.

Anyone willing to assist? Thank you ahead of time !



(NOTE: Open image in New Tab for full size image)

Macro.jpg

[maniacb]

Along with columns B and C, do you not also want a column to account for the month for the selection? Once the month passes, how are you going to tie the information back? Or is that not needed.

[Logit]

.
The months are in the tables located in Cols J:V . The sheet layout should remain as presented. However, if adding the current month in the general area of columns B & C makes it easier
to create the macro ... that is agreeable.

Once the month passes, the number for the month just passed will "freeze". Updating past months will not be required.

Have attached a sample workbook with the layout.


Thank you for responding.

[Logit]

.
It is important to mention - and I forgot to ... my apologies - the numbers in Cols B & C are formula values.

[maniacb]

so the items we want to collect from the J:V tables is the one in the current month? or just the last data set in the table?

[Logit]

.
I think a complete discussion is in order for clarity.

An existing macro pastes the Project name in Col A. The macro also pastes formulas in Col B and Col C that copies data from the Project sheet (it's another sheet). When the data is changed on the Project sheet, the numbers in Col B & C change as well.

The same macro creates the table located in J2:V5 and enters the Project Name in J2. In this example A4 and J2 correspond to one another. The intention is that the table in J2:V5 will be referenced for the data that is in A4:C4.

K3:V3 has the months of this year.

The macro to be created needs to find the current month in K3:V3. Then in the next two rows below the current month, copy the numbers from B4 & C4. In this example, B4 gets copied to Q4. C4 gets copies to Q5.

---------------------

When the next Project sheet is created, the project name is added in Col A to the first empty cell. In this example that would be A5 ... and Project 1's numbers are copied to B5 & C5 via formulas that are entered in those cells via the macro.

The existing macro also creates a table that corresponds to Project 1 (Cell A5) and pastes the table 13 rows below the table above.

The macro to be created needs to identify the current month in Cols K:V for the Project 1 table and transfer the data from B5 & C5. In this example that data would be entered in Q20 & Q21.

---------------------

More Projects will be added to the sheet in the same manner as described above, along with the corresponding tables in Cols J:V. Each of those tables will be separated from each other by 13 rows.

Let me know if this has clarified the goal. Apologies for being long winded.

Thank you so much !

[maniacb]

I created a simple approach that may be enough for your needs. I just pulled the data from the table for the current month;

Please Login or Register  to view this content.

I could add some more coding for the system to look for the current month, but here I hardcoded it to the July table(offset values in the code). Let me know your thoughts.

[Logit]

.
Thank you for this.

I edited the macro slightly so the values in Cols B & C will "write" to Col Q.
The original macro had it reversed.

Please Login or Register  to view this content.

So ... if you could be so kind ... how does this macro get edited to auto-find the correct month column in K:V ?

Thank you again !

[Logit]

.
Here is my first attempt to incorporate auto-selecting the correct Month Column :

Please Login or Register  to view this content.

It is not functioning as desired.

Still pounding keys ...

[Logit]

.
Next attempt ... still no success :

Please Login or Register  to view this content.

[Logit]

.
* Bump *

Can anyone assist ?

Thank you.

[Logit]

.
Thought I would attempt a SELECT CASE statement based on the current month. What I've put together is not working as desired.

?????

Please Login or Register  to view this content.

[bakerman2]

How about this for starters.

Please Login or Register  to view this content.

[Logit]

.
Bakerman2


Thank you for your response. You have always been a very valuable asset providing assistance. Nice to "see" you again.

Regretfully the macro only deleted the numbers in Cols B & C. It did not transfer the required data to the appropriate
Table cells.

????

[bakerman2]

Replaced your code with mine. Just push the button.

[Logit]

.
I am so sorry friend. It is still deleting the data. Nothing more.

[Logit]

.
Bakerman2 :

Disregard. I slightly edited the macro and it works fabulously ! Thank you SO MUCH !!!

Please Login or Register  to view this content.

[bakerman2]

Ah OK, it was supposed to be the other way round.

My bad, didn't read the thread closely enough (there was sooo much to read )

Glad you got it sorted out.

Glad to help and thanks for rep+.

[maniacb]

Logit, I'm glad you got it working! bakerman2, i have learned a lot from your code. Thanks. I finally got my version working, with your guys help.

Please Login or Register  to view this content.

[Logit]

.
Thank you for your assistance and you are welcome for the opportunity to learn.

This was a real tough one for me. Seems like the answer was at the tip of my fingers but ....

Bakerman2 has always been an excellent resource for assistance. His knowledge and understanding of VBA far surpasses mine.
I am extremely grateful for all his assistance.

Like you ... I was able to take some of your code and Bakerman's code to finish my project.

Take care, stay healthy. Cheers !