Solved

Changing absolute cell references

Posted on 2016-12-01
3
16 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you Andreas. I'm glad I could assist!!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 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

17 Experts available now in Live!

Get 1:1 Help Now