Building a table automatically based on data from another sheet (vlookup?)

[HyperG]

Howdy and thank you for reading and helping.

I am working on a sales forecast sheet.

There are 2 tabs.
Tab A has a summary.
Tab B has the data of sales activity based on sales stage.

Workflow.

1. Salesperson puts information for each sales inquiry on Tab B. Every sales has its own rule and a unique identifier.
2. As the sale moves along they enter dates into cells that track the progress.
3. When they enter a date in the "WON" column would like to add a new row of that sales on Tab A.

Thank you!
Hg

Agree.

Apologies for not doing this in the first case.

Thank you,

[JapanDave]

It would be much easier to answer your question if you could post a dummy workbook?

Cheers

[HyperG]

Agree. Added to initial post.

Apologies for not doing this in the first case.

Thank you,

[jpr73]

Hi,
May be a solution by using a pivot table
I rebuild your sheet Sales Pipeline in Data Sheet : you can add data the table will automaticaly increase line
After adding new data don't forget to refresh the pivot table :
Click in the PT > Tools PT > Options > Data > Refresh > Refresh All
Hope this helps
Best regards

[JapanDave]

OK, Try this,

Click the update report on the report page.

Add some names in the sales pipeline sheets sheet and click the upate report again and see if it is what you want.

If you want it automatically update, just let me know and I will set it up for you.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl see the attached file.

[HyperG]

Hi,
May be a solution by using a pivot table
I rebuild your sheet Sales Pipeline in Data Sheet : you can add data the table will automaticaly increase line
After adding new data don't forget to refresh the pivot table :
Click in the PT > Tools PT > Options > Data > Refresh > Refresh All
Hope this helps
Best regards

@jpr73 -- Thanks -- I am not upto speed on the Pivot Table ways but seems this could do it with some massaging. Appreciate the help.

[HyperG]

@JapanDave -- Thank you! Very cool stuff.
@kvsrinivasamurthy -- Thank you! Awsome!

I wonder if the VBA has a value over the equation way between your two approaches. Both seem to give me what we are looking for!

In both ways can I have excel add a new row (insert) command or do I need to leave rows empty if I place something like a summary chart below the summary table?

Thanks again!
Hg

[JapanDave]

My way adds rows as the list grows. You don't need to do anything other than add data and click the update button, it will add rows to accordingly. And if you want this to happen automatically , that can be done also.

[JapanDave]

OK, Take a look at the new file.

This automatically update the report for you when you enter the final order date in column

To test, erase all the data on the report, add a new name to the pipeline sheet and enter the order date. One stipulation is that the order date will be the last piece of data enetered for a customer. I assume that is how things would work?

Cheers

Dave

[HyperG]

@JapanDave - Appreciate the help.

Both files only seem to work for the first 4 lines then stops.

You assumption is not correct. There is a chance that the order may change as a result of a last minute modification. If possible to do live linking that is better or we can have an update button.

The report tab is going to be on a 'locked' tab so the users will not be able to modify the settings.

I am looking for way to have it grow but as it grows I need to have a way to perform sums on all the data in a single column.

Thanks,
Hg

p.s. if prefer you can PM me and I an send you a more complete file to review. The file was just a sample the final has more columns of data that needs to be linked up from the "pipeline" to the "report"

[JapanDave]

OK, will you have 2 orders for one customer?
If you are locking the tab, I will need to amend the code to correctly overcome that.

[HyperG]

No not 2 orders but rather there is more data that will be entered requiring the live update.

Appreciate your help and support.

I need to go with another solution as I need to finish this today.

Thank you again!
hg

[HyperG]

A few months ago this thread helped me solve a problem here -- #6

However I have run into an issue / bug now that I cannot determine the cause.

I have attached the sheet for review but on the "Sales Pipeline" sheet Once any data is added in or below Row 206 Column E the "Sales Pipline" the "Won Summary" sheets stops working and all goes blank.

However I can add above Row 206 without any issues.

I am lost and have tried to evaluate what the cause maybe but have not had any success hope you can help.

Thank you!
HgSample BUG.xlsx