Forum Discussion
Processing a list of data through a separate excel setup
I need to input cells C13, C14, C21, C22, D26, F26 and G26 into the MAIN tab in the attached sheet from an input sheet which looks like the capture image.
E2=C13, D2=C14, F2=C21, F2=C22, G2=D26, J2=F26 and K2=G26
Then I want to extract from the spreadsheet tab 'Cases' Q15 and V15 and insert them N2 and O2 respectively.
Then continue this on repeat from 2 to ~200.
curry2445 So it is as I feared, lol. If this is something you will do a lot in the future you may want to invest in tracing each formula back and creating the correct functions accordingly. That said, in the attached I wrote a macro to loop through all rows in an active sheet and do as you asked but be careful because I did NOT add any error checking and such (i.e. it will overwrite without prompting and such)
I tried to make the names of variable as easy for you to understand to tweak as needed. Instead of hardcoding the name of the sheet, I opted for using cell A1 to point to a cell in the other workbook on the main sheet and pulled the workbook and worksheet info that way.
I noted that you wanted to fill cells N2 and O2 even though it appears L2 and M2 are the "open" cells.
I also noted that after running it the values for your sample were "not good".
I hope this template will at least let you figure out how to complete it.
Best of luck.
Thanks for this. It's really appreciated.
I am trying to run it and I running into error. Also definetely at my ability as a user rather than your code.
I have updated cell A1 to have the location where the calculation spreadsheet is saved on my computer. I have saved both sheets in this folder.
When I run the macro it gets stuck at:
Set MPAmain = Application.Range(Mid(Asheet.Range("a1").Formula, 2)).Worksheet
Do I need to link the sheets? I am currently running the macro out of sheet 1.
My mistake in my earlier capture I missed off )2 but that what the location I wanted than formula - thanks.
Thanks again.
curry2445 I'm sorry I didn't explain it well.
I changed the macro and sheet a little so I think it makes more sense for you.
Now the macro sheet only has 2 cells; one for the address of the data sheet and one for the address of that formula worksheet. The macro assumes the data sheet is the only or at least the first sheet in the workbook. If not you can tweak the macro (or the workbook).
If this works I hope you will like and mark as best answer 🙂
Hi mtarler,
This is working. Thanks a lot you've saved me a lot of time.
I just have one final question, if you don't mind. I attempted it myself, but its completely wrong.
I want to add a column to my summary sheet with bar sizes in it and ask the macro to look up the bar size in the Cases tab B13:B18 then return the corresponding Mres V13:V18 in the original summary sheet O2...
See attached:Capture - my weak attempt.
Capture1 - the additional column
Capture2 - the information I'm talking about.
