Cross sheet data gathering
Hello again. We have reached a need for cross sheet data gathering finally and I'm struggling.
We are bringing on outside vendors that we will be billing clients as time rather than hard costs. Each of these vendors has variable rates they will be charging so each has their own sheet(s) which has a form for entering which client, Service Line, Category, time worked, rate code, formulas for performing calculations, etc. Some of this information then needs to be copied into the specific client master time sheet(s).
I have created an automation to copy a row when added from the vendor sheet to the client sheet, based on the Client column value. Works very well in testing except that it also created more columns in the client master time sheet. Since each vendor has unique rate code and other columns necessary, this will create a whole bunch of unwanted columns in each client's master time sheet.
I'm struggling with INDEX/MATCH to even get anything to generate, get a viable formula without errors. I need the values from 10(?) cells to get copied from the vendor's time sheets over to the client's master time sheet.
Each client master time sheet needs to be able to receive data from however many vendor time sheets we need to link to. Additionally, each client master time sheet needs to be able to accept nearly identical data from at least two (2) of it's own forms (already in place and working beautifully).
Am I asking the impossible?
Help Article Resources
Check out the Formula Handbook template!