+ Reply to Thread
Results 1 to 7 of 7

how to create many absolute cells

  1. #1
    Registered User
    Join Date
    04-24-2020
    Location
    San Jose
    MS-Off Ver
    Office365
    Posts
    4

    Post how to create many absolute cells

    Hello

    I have a question how to create absolute cells in column C which has formula to compare compare A cell and B cell.
    For example, A1 is compared with B1. C1 has the formula i.e. C1 = IF(A1=B1, "yes","no"). Data continues from Row 1 to let say down to 1000.
    copying C1 to all the way down to C1000.

    But now the challenge is, i need to insert or delete cells in A column or B column randomly which changes the C reference cells.
    So making them absolute i.e. C1=IF($A$1=$B$1, "","") C2=IF($A$2=$B$2, "","") ....C1000=IF($A$1000=$B$1000,"","") is what I want to do.
    It can be done easily for a few cells, but if there are thousands of cells, how can I create ?

    Thank you
    Samky2005

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    808

    Re: how to create many absolute cells

    Hi,
    I don't know what your skills are in Excel, but I would do this via VBA, a macro:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Last edited by Eastw00d; 04-25-2020 at 02:56 AM. Reason: Adjusted Formula
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,689

    Re: how to create many absolute cells

    =IF($A$1=$B$1, "","") does not achieve much, does it?
    And why use absolute references when you will insert or delete cells anyway which will change the outcome?

  4. #4
    Registered User
    Join Date
    04-24-2020
    Location
    San Jose
    MS-Off Ver
    Office365
    Posts
    4

    Re: how to create many absolute cells

    Hi Erwin

    Thank you so much! That gives me exactly what I wanted to do.
    I just took a few youtube videos to undersatnd how to deal with VBA/Macro, then I see the effect right away.
    I will spend some time about VB/macro since I'm not familiar with it. The command reminds me of the script i used in SecureCRT.

    Thank you
    Sam

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,150

    Re: how to create many absolute cells

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  6. #6
    Registered User
    Join Date
    04-24-2020
    Location
    San Jose
    MS-Off Ver
    Office365
    Posts
    4

    Re: how to create many absolute cells

    Hi
    Oh Actually, what Erwin advised me created formula successfully. But you are right. once I insert/delete cells in A or B column, the absolute formula in C column also changed. Hmmm

    Thanks
    Sam

  7. #7
    Registered User
    Join Date
    04-24-2020
    Location
    San Jose
    MS-Off Ver
    Office365
    Posts
    4

    Re: how to create many absolute cells

    Hi AliGW

    Thank you for the reminder. Yes, I was responding to 2nd reply and about to do so.

    Thanks
    Sam

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to create absolute hyperlink
    By iadoin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2016, 12:33 PM
  2. [SOLVED] Can I create a range with absolute cell references and multiple sums?
    By Chimchimeny in forum Excel General
    Replies: 4
    Last Post: 03-12-2014, 08:47 AM
  3. Replies: 0
    Last Post: 04-08-2013, 05:08 AM
  4. [SOLVED] Relative and Absolute to create an average/year
    By loonie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2007, 12:41 PM
  5. How do I create charts having non absolute reference values?
    By rvboxel in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-19-2006, 08:20 AM
  6. How do I anchor a cell/create absolute ref. while clicking on it?
    By dsapan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2006, 02:00 PM
  7. Replies: 5
    Last Post: 04-26-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1