How do I copy cells and remove blank?

**Bifi85** · 01-16-2018, 10:27 AM

I made a very simple picture for perfect explaining: https://i.imgur.com/wutI6sY.png

I have 3 sheets:

sheet1: Here are my recipes (for example "Alpha" and "Beta")
sheet2: A big chart called ("Recipe (all ingredients)") with all ingredients listed.
sheet3: A chart that always uses the values from sheet2 but without the blank.

What I want to do:

I go to sheet1 and click on the button below a recipe (for example "Alpha").
Now the values from "Alpha" should copy themselves to sheet2 but enter correctly, because there are all ingredients listed and Alpha uses only a few ingredients.
Now sheet2 has a lot of 0 values, because we don't need all ingredients now, but all are listed. Some got values from recipe "Alpha" and the rest has "0%".
Sheet3 checks always the list/chart "Recipe (all ingredients)" from sheet2 and only displays the "cells" aka "ingredients" with value.

The picture explains it a lot better than I can do it with my words: https://i.imgur.com/wutI6sY.png

**modytrane** · 01-16-2018, 11:29 AM

attach excel file with data and expected result. please don't expect others to create it for you. you'll get help if you do that.

**Bifi85** · 01-16-2018, 12:05 PM

It is all in one Excel-data file. I don't know what you mean. Do you have a link to a tutorial what I have to do? I can make Buttons and I know how to copy normal cells, but not how to put them in the spot I want them and how not to copy blanks.

**JeteMc** · 01-18-2018, 11:59 AM

What modytrane is requesting is that you upload a sample workbook showing what you want, not just a picture. We can't experiment with pictures and would therefore have to type up sample data to work with. The results will need to be manually placed in the sample so that we can attempt to automate those results with either formulas or code. To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

**Bifi85** · 01-19-2018, 06:03 AM

Here is a sample with explaining on every side: https://files.fm/u/8b4qrzeh

**JeteMc** · 01-19-2018, 01:03 PM

I know that you are looking for a VBA solution, however since this has not received many responses here is an example of how this might be done using formulas.
1) On Tabelle 1 the recipes are put into records which makes it easier to access the data. Column F, which was not in the original data, is populated using: =IF(ISTEXT(G2),G2,F1)
2) On Tabelle 2 data validation is applied to cell E1 which takes the place of the buttons on Tabelle 1 and which references the list in column N.
3) The array entered formula* which populates column A is:

Formula:

Please Login or Register  to view this content.

4) On Tabelle 3 the array entered formula* that populates columns A and B is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
In the future please upload files directly to this site. (directions in post #4)
Let us know if you have any questions.

**Bifi85** · 01-20-2018, 06:24 AM

Thank you very much. But I have 1 problem:

I want to work in Tabelle2 "Recipe (all ingredients)" that is where I change the % in the cells. But when i write in it for example 20 % for B the whole thing doesn't work anymore, because I overwrote the code.

Can you explain me why it is =IF(ISTEXT(G2),G2,F1) and not =IF(ISTEXT(G2),G2,F2)? I don't understand that over the corner thing.

**JeteMc** · 01-20-2018, 11:22 AM

I can see that when you overwrite cell A4 on Tabelle2 it changes the other values, however without knowing what you would expect the other values to be I can't offer much in the way of help. If you change A4 to 20% what do you expect to see in cells B3, B4 and B5? An explanation as to why you would expect to see those values would also be helpful.
The formula is set up so that if there is text in the adjacent cell, in column G, it will copy that text, otherwise it will copy the text from the cell above, in column F. I hope that makes sense.
I don't understand to what "over the corner" is referring?

**Bifi85** · 01-20-2018, 12:40 PM

Originally Posted by JeteMc

I can see that when you overwrite cell A4 on Tabelle2 it changes the other values, however without knowing what you would expect the other values to be I can't offer much in the way of help. If you change A4 to 20% what do you expect to see in cells B3, B4 and B5? An explanation as to why you would expect to see those values would also be helpful.
The formula is set up so that if there is text in the adjacent cell, in column G, it will copy that text, otherwise it will copy the text from the cell above, in column F. I hope that makes sense.
I don't understand to what "over the corner" is referring?

Everything you did is very good. Thank you.

I use baker's percentage all ingredients are compared to 100% flour.

Maybe the "Data Validation" aka the list where i switch between "alpha" and "beta" should always copy the code into the cells, so they work every time when I want to go back to the original recipe.

The only thing beside THAT is, that I use some ingredients sometimes in more detailed form. For example when I need 50% water but the preparation says half cold water the other half warm water. So I don't need the 50% water in Tabelle3 but 25% warm water and 25% cold water. But I can mark the cell my myself, when I know how.

The file should be now clearer. I put it in:

**JeteMc** · 01-22-2018, 05:39 PM

As to the "switch between "alpha" and "beta"...[so that they]... go back to the original recipe". That will probably take VBA or possibly a template, I have asked some of the contributors from the VBA to take a look.
As to the more detailed form, I feel the easiest (perhaps only) way to accomplish that is to add the details to the "All Ingredients" list, so that for example instead of water you would have hot water and cold water. I know if will make the list longer, however there are 1,048,576, and possibly more in the 365 version, rows.
Let us know if you have any questions.

**bakerman2** · 01-23-2018, 03:48 AM

See if you can live with this.
Changing datavalidation triggers code.

**Bifi85** · 01-24-2018, 07:14 AM

Originally Posted by bakerman2

See if you can live with this.
Changing datavalidation triggers code.

Thank you but the problem here is, that sheet 3 never take the values when I add or change some values in sheet 2. - sheet 3 should change the values "live" when I change them in sheet 2. For example 0% Egg -> 4% Egg.

Sheet 1 has always constant values no matter what, it is like a database.

**bakerman2** · 01-24-2018, 07:30 AM

In that case you would need 2 seperate codes.
1 in sheet1 to send recipy to sheet2 and another one in sheet2 to transfer adapted recipy to sheet3.

**Bifi85** · 01-24-2018, 09:49 AM

Yeah, that would be great.

**bakerman2** · 01-24-2018, 10:00 AM

Changing validation changes values on Tabelle2. Code redirects to Tabelle2.
After making changes (or not) pushing button transfers values to Tabelle3. Code redirects to Tabelle3.

**Bifi85** · 01-29-2018, 03:50 PM

I don't get it right.

In the picture is what I want to do and the file is below.

**JeteMc** · 01-29-2018, 06:20 PM

Perhaps use the following array entered formula* to populate table 4 as in:

Formula:

Please Login or Register  to view this content.

1) Select cell H21,
2) Paste the formula into the formula bar,
3) Simultaneously press the Ctrl, Shift and Enter keys,
4) Double click the fill handle to copy down to H31
5) While the range H21:H31 is still selected drag the fill handle of over to I31.
Let us know if you have any questions.

**Bifi85** · 01-30-2018, 04:13 AM

Originally Posted by JeteMc

Perhaps use the following array entered formula* to populate table 4 as in:

Formula:

Please Login or Register  to view this content.

1) Select cell H21,
2) Paste the formula into the formula bar,
3) Simultaneously press the Ctrl, Shift and Enter keys,
4) Double click the fill handle to copy down to H31
5) While the range H21:H31 is still selected drag the fill handle of over to I31.
Let us know if you have any questions.

It does not work for me.
I can't copy the formula. Microsoft Excel ERROR "There's a problem with this formula."
Ctrl+Shift+Enter never did something for me in my Excel.

**JeteMc** · 01-30-2018, 09:38 AM

Here is a copy of the file with the formula applied as described.
Let us know if you have any questions.

**Bifi85** · 01-30-2018, 10:21 AM

It works thank you. What I have to do that this "easy mode" "=IF(ISTEXT(G7);G7;F6)" works?

How do I copy cells and remove blank?

LinkBack

Thread Tools

Rate This Thread

Display

How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Re: How do I copy cells and remove blank?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Help with Formula: Remove trailing commas or using IF to remove results from blank cells?

[SOLVED] Cocatenate Range of Cells in a column, remove blank cells, and stack text in a cell

[SOLVED] Remove Blank Cells from a Range when blank cells are actually a “” result from a IF Func.

Remove or hide time when copy is blank

remove blank rows and copy new range

How to remove blank cells from row

copy column of data, remove blank cells

Bookmarks

Bookmarks

Posting Permissions