Solved

Changing absolute cell references

Posted on 2016-12-01
3
46 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

838 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