Populate multiples rows with data, then loop back and get next next row of input.

Hi all.
I’m using Excel 2007
I have a list on "Sheet3" that I need to draw data from.
On sheet “sewer” I have a validation list in column B.
I need to use the multiple values available in the validation list to place the appropriate rows from “sheet3” into rows in “sewer” starting at Q1. Comparing “sewer” column B with “sheet3” column F. In the real file the validation list expands out to over 300 items using a dynamic list validation.
The idea being I can then use the results in a pivot table.
The result I am chasing is on sheet “Explain”
On “Explain” sheet columns B, C, D, E, F are from “sheet3”. G is from column C “sewer”, and H is E times G on this sheet.
The excel file is an edited version of my real file, as the “sheet3” file is over 2500 rows long drawing the costs from a separate price list using multiple VLOOK ups over the row, then it calculates out the cells using =SUM(OFFSET(MATCH))), so I have left only values in the supplied file. I all so have “sheet3” used multiple times with different names.
I am very new to excel, only knowing basic =SUM() functions before now. I have tried using the multiple consolidation ranges feature in pivot tables, but I am laid out wrong. Have tried VLOOKUP, INDEX and MATCH, complex arrays, all I did was confused myself.
From what I can see it needs to be done with VBA and a macro that would clear the work are before inserting the data so it could be up dated, but I have no idea what to do in there. Can anybody please help me?


examplesewer.xlsm

Hi

Ok try this

1) Explain!E2: Change the heading to Quantity
2) Sewer!B2: enter Markup
3) Sewer!B2: Format Cells, Custom, ;;; This will have the effect of putting in a valid heading for an advanced filter, but you won't be able to see it.
4) Right click on the Explain tab, view code, and paste in the code below

Please Login or Register  to view this content.

Not when you select the explain sheet, it will be updated based on your selections in sewer.

HTH

rylo

Rylo,

Thanks for the above

I have sent you a PM

Hi

Next cut

Please Login or Register  to view this content.

rylo

Apologies Jerry

Rylo,

I did some more testing this afternoon.

If I use the validation list to replace the content from “sewer” column B, it adds the new content to the sheet “Explain”, but also leaves the replaced content in “Explain” with #N/A in columns G and H.

Any idea’s ?

I would like to thank you again

Bob

Rylo,

Stand down good sir, might be me at my end

Let me check before you start looking at it

Bob

Ok, Apologies Rylo I did not have the validation list setup properly and that created its own errors.

I have attached the working model of the original supplied, validation and all.

The only way I seem to be able to “break it” now is if I remove a row from “sewer” then any rows below it are not seen on “explain”. This also means if I remove ALL the rows from “sewer” I still have all the rows in “Explain” but I have #N/A in columns G and H.

Also, if I double up on an entry in “sewer”, “Explain” does not pick it up. Could it see the second instance and either add it to the first instance or just add it to the running list as a new line?

Pushing my luck,
Could both “sewer” and “Explain” tables reside on the same sheet as the each other? With “explain” data starting at AA2. The reason being, that I have multiple sheets with the “sewer” style data on it. This way I could place a pivot table on the same page. Using a macro I would step out of the sheet, back in again to refresh the data, and then refresh the pivot table all in the one action, keeping all the relative data in the one sheet.

You have no idea how much I appreciate this.

Thanks,
Bob


examplesewer (1).xlsm

Rylo,

I have adjusted the above macro to have the results on the same sheet as the supplied data ("sewer" and "explain" are on the same sheet) and is working correctly, but I still have the error checking problems.

The same as in the post above,

“The only way I seem to be able to “break it” now is if I remove a row from “sewer” then any rows below it are not seen on “explain”. This also means if I remove ALL the rows from “sewer” I still have all the rows in “Explain” but I have #N/A in columns G and H.

Also, if I double up on an entry in “sewer”, “Explain” does not pick it up. Could it see the second instance and either add it to the first instance or just add it to the running list as a new line?”

The code is what I now have working.

Are you able to help ??

Please Login or Register  to view this content.