+ Reply to Thread
Results 1 to 10 of 10

Inserting formulas and html code based on specific cell range criteria

  1. #1
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Inserting formulas and html code based on specific cell range criteria

    Hi, I am looking to find a way to automatically fill cell ranges based on criteria from 2 specific columns.

    In the attached spreadsheet column range Y-AC and AV-BA i have pre-filled formulas and html snippets that are relevant to the criteria in column K

    Is it possible to fill rows 30-40 with the same corresponding formulas and html snippets based on criteria in Col K and Col BB

    also i would like to then fill down the formulas in Col AD and Col AE based on 'New row' in Col BB.

    Automating these columns would relieve me of a monotonous daily task that is often over 2000 rows of data

    Any help would be greatly appreciated

    Regards
    John
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Inserting formulas and html code based on specific cell range criteria

    Try the attached.
    Attached Files Attached Files
    Last edited by AB33; 04-07-2015 at 11:33 AM.

  3. #3
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Inserting formulas and html code based on specific cell range criteria

    Thx for your response and your solution works great for the columns with formulas.

    The problem I have now is Columns AY and AZ need to have Html snippets relative to criteria in Col K

    e.g.

    K AY AZ
    R1 <script type="text/javascript">// <![CDATA[
    // Popup window code
    function newPopup(url) {
    popupWindow = window.open(
    url,'popUpWindow','height=700,width=1000,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=yes,menubar=no,location=no,directories=no,status=yes')
    }
    // ]]></script>
    <p><span style="font-size: medium;">This unit is an approved Grade 1 Refurb which is just about the best refurbished gets.</span></p><br />
    <p><strong><span style="font-size: medium;">What to Expect</span></strong><br />✔ No scratches, dents or dead pixels in display/TFT<br />✔ A device in great physical condition<br />✔ Could have minor signs of normal use (keyboard, palmrest buttons)</p>
    <p><br /><span style="font-size: medium;"><strong>What <span style="text-decoration: underline;">NOT</span> to Expect</strong></span><br />✔ Deep scratches or dents in the body<br />✔ Broken-off parts or paint damage<br />✔ Color difference due to asset tags or stickers<br />&nbsp;</p>
    <p><strong>Benefits of purchasing A1 Refurb Units:</strong>&nbsp;</p>
    <p>✔ Recent hardware models available<br />✔ Fully customisable to your needs<br />✔ Genuine operating system (Microsoft Authorised Refurbisher)<br />✔ Warranty extendable up to 5 years<br />✔ Saves you up to 60% on costs<br />✔ Reduces your carbon footprint<br />✔ Over 20 years of refurbishing experience<br />✔ Contributes to your Corporate Social Responsibility</p>
    <p>&nbsp;</p>
    <p><strong>All items go through an extensive 18 point quality check:</strong></p>
    <p>✔ Cleaned inside<br />✔ Cleaned outside<br />✔ All hard disk data wiped<br />✔ Asset tags removed<br />✔ BIOS tags removed<br />✔ BIOS battery checked/replaced<br />✔ Feet checked/replaced<br />✔ Covers checked/replaced<br />✔ Cosmetic check<br />✔ Power (I/O) check<br />✔ Correct specifications<br />✔ Working optical drive<br />✔ Working hard disk<br />✔ Software installed<br />✔ Working keyboard/touchpad/etc.<br />✔ Laptop battery checked<br />✔ Display checked for damage<br />✔ Carefully boxed</p>
    <p>&nbsp;</p>
    <p style="text-align: center;">&nbsp;Click on the "5 Star Google Rated" symbol below to see what previous customers have to say about their experience with Ex Demo Hut.</p>
    <p style="padding-left: 195px;"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/satisfaction-guaranteed.png" alt="Satisfaction Guaranteed" /><a href="JavaScript:newPopup('https://www.google.co.uk/shopping/seller✔gl=UK&amp;hl=en&amp;q=9502017');"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/5%20Star%20Google.png" alt="Ex Demo Hut Reviews" /></a></p>
    <p><span style="font-size: medium;">This unit has a one year warranty from date of purchase, extendable up to 5 years if required.</span></p>
    R2 <script type="text/javascript">// <![CDATA[
    // Popup window code
    function newPopup(url) {
    popupWindow = window.open(
    url,'popUpWindow','height=700,width=1000,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=yes,menubar=no,location=no,directories=no,status=yes')
    }
    // ]]></script>
    <p><span style="font-size: medium;">This unit is an approved Grade 1 Refurb which is just about the best refurbished gets.</span></p><br />
    <p><strong><span style="font-size: medium;">What to Expect</span></strong><br />✔ No scratches, dents or dead pixels in display/TFT<br />✔ A device in great physical condition<br />✔ Could have minor signs of normal use (keyboard, palmrest buttons)</p>
    <p><br /><span style="font-size: medium;"><strong>What <span style="text-decoration: underline;">NOT</span> to Expect</strong></span><br />✔ Deep scratches or dents in the body<br />✔ Broken-off parts or paint damage<br />✔ Color difference due to asset tags or stickers<br />&nbsp;</p>
    <p><strong>Benefits of purchasing A1 Refurb Units:</strong>&nbsp;</p>
    <p>✔ Recent hardware models available<br />✔ Fully customisable to your needs<br />✔ Genuine operating system (Microsoft Authorised Refurbisher)<br />✔ Warranty extendable up to 5 years<br />✔ Saves you up to 60% on costs<br />✔ Reduces your carbon footprint<br />✔ Over 20 years of refurbishing experience<br />✔ Contributes to your Corporate Social Responsibility</p>
    <p>&nbsp;</p>
    <p><strong>All items go through an extensive 18 point quality check:</strong></p>
    <p>✔ Cleaned inside<br />✔ Cleaned outside<br />✔ All hard disk data wiped<br />✔ Asset tags removed<br />✔ BIOS tags removed<br />✔ BIOS battery checked/replaced<br />✔ Feet checked/replaced<br />✔ Covers checked/replaced<br />✔ Cosmetic check<br />✔ Power (I/O) check<br />✔ Correct specifications<br />✔ Working optical drive<br />✔ Working hard disk<br />✔ Software installed<br />✔ Working keyboard/touchpad/etc.<br />✔ Laptop battery checked<br />✔ Display checked for damage<br />✔ Carefully boxed</p>
    <p>&nbsp;</p>
    <p style="text-align: center;">&nbsp;Click on the "5 Star Google Rated" symbol below to see what previous customers have to say about their experience with Ex Demo Hut.</p>
    <p style="padding-left: 195px;"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/satisfaction-guaranteed.png" alt="Satisfaction Guaranteed" /><a href="JavaScript:newPopup('https://www.google.co.uk/shopping/seller✔gl=UK&amp;hl=en&amp;q=9502017');"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/5%20Star%20Google.png" alt="Ex Demo Hut Reviews" /></a></p>
    <p><span style="font-size: medium;">This unit has a one year warranty from date of purchase, extendable up to 5 years if required.</span></p>
    R3 <script type="text/javascript">// <![CDATA[
    // Popup window code
    function newPopup(url) {
    popupWindow = window.open(
    url,'popUpWindow','height=700,width=1000,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=yes,menubar=no,location=no,directories=no,status=yes')
    }
    // ]]></script>
    <p><span style="font-size: medium;">This unit is an approved Grade 1 Refurb which is just about the best refurbished gets.</span></p><br />
    <p><strong><span style="font-size: medium;">What to Expect</span></strong><br />✔ No scratches, dents or dead pixels in display/TFT<br />✔ A device in great physical condition<br />✔ Could have minor signs of normal use (keyboard, palmrest buttons)</p>
    <p><br /><span style="font-size: medium;"><strong>What <span style="text-decoration: underline;">NOT</span> to Expect</strong></span><br />✔ Deep scratches or dents in the body<br />✔ Broken-off parts or paint damage<br />✔ Color difference due to asset tags or stickers<br />&nbsp;</p>
    <p><strong>Benefits of purchasing A1 Refurb Units:</strong>&nbsp;</p>
    <p>✔ Recent hardware models available<br />✔ Fully customisable to your needs<br />✔ Genuine operating system (Microsoft Authorised Refurbisher)<br />✔ Warranty extendable up to 5 years<br />✔ Saves you up to 60% on costs<br />✔ Reduces your carbon footprint<br />✔ Over 20 years of refurbishing experience<br />✔ Contributes to your Corporate Social Responsibility</p>
    <p>&nbsp;</p>
    <p><strong>All items go through an extensive 18 point quality check:</strong></p>
    <p>✔ Cleaned inside<br />✔ Cleaned outside<br />✔ All hard disk data wiped<br />✔ Asset tags removed<br />✔ BIOS tags removed<br />✔ BIOS battery checked/replaced<br />✔ Feet checked/replaced<br />✔ Covers checked/replaced<br />✔ Cosmetic check<br />✔ Power (I/O) check<br />✔ Correct specifications<br />✔ Working optical drive<br />✔ Working hard disk<br />✔ Software installed<br />✔ Working keyboard/touchpad/etc.<br />✔ Laptop battery checked<br />✔ Display checked for damage<br />✔ Carefully boxed</p>
    <p>&nbsp;</p>
    <p style="text-align: center;">&nbsp;Click on the "5 Star Google Rated" symbol below to see what previous customers have to say about their experience with Ex Demo Hut.</p>
    <p style="padding-left: 195px;"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/satisfaction-guaranteed.png" alt="Satisfaction Guaranteed" /><a href="JavaScript:newPopup('https://www.google.co.uk/shopping/seller✔gl=UK&amp;hl=en&amp;q=9502017');"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/5%20Star%20Google.png" alt="Ex Demo Hut Reviews" /></a></p>
    <p><span style="font-size: medium;">This unit has a one year warranty from date of purchase, extendable up to 5 years if required.</span></p>
    Renew <script type="text/javascript">// <![CDATA[
    // Popup window code
    function newPopup(url) {
    popupWindow = window.open(
    url,'popUpWindow','height=700,width=1000,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=yes,menubar=no,location=no,directories=no,status=yes')
    }
    // ]]></script>
    <h4><strong>This is a HP Renew Product = HP Certified Quality</strong></h4>
    <p>HP Renew products are guaranteed to be exactly the same as a brand new product. All HP Renew products undergo a complete HP remanufacturing and testing process, fully restoring them to meet HP certified standards. HP sets strict quality standards for its entire technology portfolio, and ensures that remanufactured products offer the same performance and reliability as new products.</p>
    <p>These items are delivered in sealed HP packaging with all original protective film and accessories included.</p>
    <p>&nbsp;</p>
    <h4><strong>Full HP Warranty</strong></h4>
    <p>HP Renew products are covered by the exact same warranty that is offered with new retail HP products. These products are only given the full original warranty after passing HP's stringent quality tests, guaranteeing HP quality and reliability. Additional service and support options are also available to complement the original HP product warranty.</p>
    <p>&nbsp;</p>
    <h4><strong>Great Value for Money</strong></h4>
    <p>HP Renew products offer the same reliability and performance as new HP products, but for at least 10% less than the cost of the equivalent new product. If you have a limited IT budget, HP Renew is the perfect alternative &ndash; high quality solutions at a lower cost.</p></br>
    <p><img title="Why Buy HP Renew?" src="https://exdemohut.com/media/wysiwyg/1Hp_Renew_res_Custom_.jpg" alt="Why Buy HP Renew?" /></p>
    <p style="text-align: center;"><br /><span style="font-size: large;">Click on the Google Rating symbol below to read what our previous customers have to say about their experience with Ex Demo Hut!</span></p>
    <p>&nbsp;</p>
    <p style="padding-left: 195px;"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/satisfaction-guaranteed.png" alt="Satisfaction Guaranteed" /><a href="JavaScript:newPopup('https://www.google.co.uk/shopping/seller?gl=UK&amp;hl=en&amp;q=9502017');"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/5%20Star%20Google.png" alt="Ex Demo Hut Reviews" /></a></p>
    <p><span style="font-size: medium;">This unit has a full manufacturer's warranty.</span></p>
    As New <script type="text/javascript">
    // Popup window code
    function newPopup(url) {
    popupWindow = window.open(
    url,'popUpWindow','height=700,width=1000,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=yes,menubar=no,location=no,directories=no,status=yes')
    }
    </script>
    <p>This item has been on display in a store and used for demonstration purposes only. It may have some very light usage marks but has been thoroughly checked by our trained engineers for any defects and is literally "As New".</p>
    <p>The unit comes with all original packaging and accessories but the original box will have been opened and resealed. The unit itself however is in A1 condition.</p><br>
    <p>&nbsp;</p>
    <p style="padding-left: 195px;"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/satisfaction-guaranteed.png" alt="Satisfaction Guaranteed" /><a href="JavaScript:newPopup('https://www.google.co.uk/shopping/seller?gl=UK&amp;hl=en&amp;q=9502017');"><img style="float: left;" src="https://exdemohut.com/media/wysiwyg/5%20Star%20Google.png" alt="Ex Demo Hut Reviews" /></a></p>
    <p>This unit has a full one year manufacturer's warranty from date of purchase</p>


    So the information provided for AY and AZ is based on the criteria from Col K

    Are you able to help with this

    regards

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Inserting formulas and html code based on specific cell range criteria

    I am not sure if I understood your request.
    Right now, these 2 columns are copied down based on column k. The code copies relative formulas. If the last cell is static, it copies that cell down.
    What do you want to change?

  5. #5
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Inserting formulas and html code based on specific cell range criteria

    I tried to get what i needed by amending another bit of code i had but it doesn't work for me.

    Maybe you take a quick look and see what i've done wrong.

    See attached file

    Many thx for your assistance and the pulldown code for the formulas
    Attached Files Attached Files
    Last edited by JohnnyBoyxxx; 04-07-2015 at 03:32 PM.

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Inserting formulas and html code based on specific cell range criteria

    The value to the right of R1 is it a single cell value?

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Inserting formulas and html code based on specific cell range criteria

    Johnny,
    I am not sure you can display all in a single cell value as this might exceed the text limit of excel.
    This gives you a hint how to put all the lines in a single cell. I have stop half-way as there were too many lines to go through. You need to put all the lines in double quote, use continuation line. You also need to put any text with
    double quote with another double quote. See my example.

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Inserting formulas and html code based on specific cell range criteria

    Hi, this is the code i'm trying to get right to fill AY and AZ based on criteria from K along the pull down code you already provided for me.

    If i could get this to work for me I should be able to adapt it to suit for the rest of the criteria in K.

    Please Login or Register  to view this content.
    Many thx for your time AB33

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Inserting formulas and html code based on specific cell range criteria

    You probably need to remove the fill down from these columns, but this is the correct syntax

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    04-25-2011
    Location
    Donegal, Ireland
    MS-Off Ver
    Excel 2013
    Posts
    133

    Re: Inserting formulas and html code based on specific cell range criteria

    Many many thx, works great after the fill down was removed as you suggested.

+ 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. Replies: 6
    Last Post: 06-17-2014, 11:11 AM
  2. Code to clear contents of a cell based on specific criteria
    By srsev in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-21-2013, 01:38 PM
  3. [SOLVED] Several formulas with specific range criteria (AVERAGEIFS, STDEV(IF), COUNTIFS)
    By thaphthia in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 04-11-2013, 05:22 PM
  4. [SOLVED] VBA Code not inserting the right number in a specific cell
    By akwishestofish in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-13-2012, 05:07 PM
  5. VB Macro for variable cell merge into HTML code based on criteria.
    By NewYorkVanilla in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-13-2009, 08:54 AM

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