Solved

Changing absolute cell references

Posted on 2016-12-01
3
33 Views
Last Modified: 2016-12-02
Dear Experts:

I got thousands of records where I have to concatenate two cells as follows

The Formula in D5 is as follows:
IF(C5="";"";C5&"_"&MOD($E$4;1;3))

Copying it down all the way does not any good since I need to change the absolute references as soon as the number in column E (green data records) change.

Any idea how I can get his done either by a formula or an Excel Macro?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

I have attached the sample file for your convenience.

formula_changing_absolut_cell_references
Changing_Absolut_Cell_References.xlsx
0
Comment
Question by:AndreasHermle
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 41909511
See if this formula works for you:

=IF(C5="","",IF(C4="",C5&"_"&MID(F4,1,3),C5&MID(D4,13,9)))

Use Special Paste - Formula

Flyster
0
 

Author Closing Comment

by:AndreasHermle
ID: 41909682
Hi Flyster,

this is great, I am so happy with it. It saves me many hours of tedious and stupid work :-)

Thank you very much for your professional help. I really highly appreciate it.

Regards, Andreas and have a nice day.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 41911056
Thank you Andreas. I'm glad I could assist!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now