Forum Discussion
Extracting specific cells from multiple sheets to table
Hey,
I have a sheet, which is structured for humans and not automation and that has resulted in me having some headaches getting power query to reformat it correctly.
From each sheet I'd love to have ie. cell value H8:11, H14:H19 (and more) pasted into a new table with each sheet being it's own seperate row with the correct column names. I also have some conditional formatting in the form of a few if statements that I'd like to add after the fact (for example, I want to convert a postal code range to a specific region).
Is there any way to do this with Power Query? - I've done it with VBA where I loop through each file and copy the specific values, but I'd love it if I could possibly do it in Power Query for ease of automation in the future and further statistical work based on a single source layout.
Hope y'all can help me out - In advance thanks!
3 Replies
This should be doable. Can you provide a link to an example spreadsheet so that the community can provide a specific response?
Pat
Hi,
Yes of course - Thank you very much.
I couldn't get Power Query to do anything but delete specific columns, but I must be missing something basic about how to use it.Example of my if statements:
If H11 = "x"
Put N/A in the rest of the fields
If 0<H10<3000
ThisRegion
ElseIf 3000<H10<4000This other region
Hope it makes sense.
I may have misunderstood what you are trying to accomplish. You are trying to use Power Query to populate this spreadsheet? Why not just use a formula to populate those cells?
Pat
