We help IT Professionals succeed at work.

Problem with a moving column in Excel

117 Views
Last Modified: 2017-03-13
Hello,

in our firm every month we import in Excel some data from SAP. On the "Sheet 1" there is the table with imported data, on the "Sheet 2" there are some formulas in which are used values from "Sheet 1". Unfortunately, sometimes when we import data for the new month the columns are moving and thus the formulas become wrong. I decided to write a macro which will search the whole row 5 on Sheet 1 and find cell with text "Overall result" and then use this column in the formula as the last argument. (Formula is : =SUMIF (Sheet 1 F:F; E10; Sheet 1 Z:Z)/100). I need to replace Sheet 1 Z:Z with the right row. Can you please help me with the code? Or maybe if you see another way to solve this problem, please share :) Thank you in advance!!
Comment
Watch Question

can you provide me any remote connections so i can help you

Author

Commented:
Akshay Parsai - unfortunately, no, i just need the VB code :)
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Just select the formula and use Find & Replace by clicking Ctrl+F
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I will make a rather different proposal...given the fact you have such an expensive ERP (SAP) i reckon that you have IT department....so hopefully someone has the knowledge to make the connection to SAP with a tool like Ms Access and forget about columns misplacements and formulas not working...here is a nice blog post with step by step guide on how to do it

Author

Commented:
Thank you a lot for your help! I solved my problem using these functions. My working formula is:
=SUMIFS(INDEX(Sheet_1!$A:$Z;0;MATCH("Overall result"; Sheet_1!$A$8:$Z$8;0)); Sheet_1!$F:$F;E10)/100
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.