We help IT Professionals succeed at work.

Problem with a moving column in Excel

on
117 Views
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

View Solution Only

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

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)
Group Finance Manager
CERTIFIED EXPERT

Commented:
Just select the formula and use Find & Replace by clicking Ctrl+F
Software & 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

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.