Learn how to make a spreadsheet with automated answers

Excel Spreadsheets

One of the great advantages of Excel is the possibility of automation that it allows for its users. Still, I'm tired of seeing spreadsheets without a single formula or function. These are extremely simple things, but because of the lack of knowledge in Excel, are left aside.

Excel Course - Conditional Formatting

For example, I've seen clients with spreadsheets that instead of adding two cells with the SUM function or even a formula (= A1 + A2) left the answer space blank, so it went and added up. Imagine the work? No human should go through that job your computer can do for you.

Excel Spreadsheets

Smart Spreadsheet with Automated Responses

Therefore, having a Excel spreadsheet smart, fully automated, can make all the difference in analyzing the data and deciding what actions to take next. The results are presented immediately and without risk of errors making the work of the manager much more practical and efficient.

Automating an Excel spreadsheet for your use in a smart way is a very simple task and means saving time with manual calculations or other types of simple calculations, in addition to obtaining results with accuracy. This post will show you how easy and important it is to have an automated Excel spreadsheet to facilitate the work of the manager.

Automation of results with functions

A cell with a value in the worksheet will present automated results and should diverge whenever there are changes in the data entered for the measurement. Functions such as SOMASE and PROCV can contribute to the automation of the Excel worksheet.

Excel Course - Conditional Formatting

1 - Applying the SOMASE function:

This function is conditioned to some criteria, such as: interval (area destined to the data to be analyzed), criterion (a certain category to be added, examples related to the "Book" described in the worksheet) and be summed in terms of numerical values).

In this type of worksheet the entries are defined in inputs and outputs, in kind.

The data required for the spreadsheet are classified in column "D" and "E", Classification and Chart of Accounts, and "H" representing the dates of payment. While the columns "G" and "I" represent the amount paid and the accumulated balance. Thus, to know the sum of the values ​​considering the Chart of Accounts, the function SOMASE is applied.

excel financial database

In the example worksheet we have the "DRE" tab inside "Reports". The SOMASE formula is applied to display the results automatically. Below the main table, you can see a table with values ​​by item of the chart of accounts. To have the value received in "Book" for each month, the formula SOMASE is applied.

dre books somase

In order to have the final result with the sum of every month, the following formula can be used:

  • Example of January: "= SUMIFS (JAN! $ G $ 5: $ G $ 605; JAN! $ E $ 5: $ E $ 605; $ D19)":

JAN! $ G $ 5: $ G $ 605 - column of values ​​in the January tab
JAN! $ E $ 5: $ E $ 605 - column "Chart of Accounts" in the January tab
$ D19 - cell containing the word "Book"

In summary, the command passed to Excel is "sum all the values ​​in column G, when the corresponding in column E is Book". Every month they are in different tabs, but using the same formula. In this way, we can obtain the automatic result of all of them.

In this example we use our Sheet Cash Flow.

Excel Course - Conditional Formatting

2 - Using the PROCV function:

In the PROCV function, the entire worksheet with an integrated structure will be considered. This function aims at a virtual search.

We use as an example the application of the PROCV function in a stock control sheet of a company. In it, we add the product data in the rows and columns. The "Product Name" column will be the reference to locate the information you want in the other columns.

stock control procv

Navigating the spreadsheet, we will notice that within "Consolidated Results", there is the "Individual Analysis" tab. In this tab, the user chooses a product and the worksheet should seek data from it in the other tabs.

stock control formula procv individual analysis

Keeping the "Product Name" column always the reference for this function, we insert "Nail" and we format the formula to look for the other values.

Let's see the example of the formula used to fetch the Minimum Stock in the cell "D9", which would look like this: = PROCV ($ D $ 5; PG! $ D $ 7: $ E $ 1006; 2; FALSE)

Thus we have:

- Value_looked = $ D $ 5 (location on this tab where the product name "Nail" is located);

- Matrix_table = PG! $ D $ 7: $ E $ 1006 (columns with the name of the product and the minimum stock value, used to calculate the function in the tab PG -> Product Register);

Excel Course - Conditional Formatting

- Index_column_number = 2 (Note that it considers the matrix with columns D and E, bearing, and, where the minimum stock would be the second);

- Search_interval = FALSE (or "0" as the accepted value).

Note that by confirming the formula entry, the minimum stock will automatically appear in the designated cell.

Continue applying the formula to extend the PROCV function to every properly filled worksheet. So it will become more efficient by adding agility when it comes to getting some information.

3 - Automating responses from results

The results can still be demonstrated automated within a report. The use of the CONCATENAR function allows you to pull this data to insert inside a text and, thus, to present an automatic response.

In this example we will use a customer prospecting worksheet.

Learn how to make a spreadsheet with automated answers

See in the image below how you would be represented in a report text, the month that presented the best sales result, automatically.

Learn how to make a spreadsheet with automated answers

Learn how to make a spreadsheet with automated answers

To get an automated response like this, follow the step-by-step instructions.

Excel Course - Conditional Formatting

Choose the worksheet cell where you want the report to present the result. In this example we choose the cell "Q17". Now go to the "Formulas" tab and choose the type of formula "Text" and then "CONCATENATE". The following window will open.

Learn how to make a spreadsheet with automated answers

According to our sample worksheet, the data for the configuration of this formula would be populated as in the following image.

Learn how to make a spreadsheet with automated answers

Press the "ENTER" key to confirm and you will have automatic responses in the form of reports in your Excel worksheet.

Learn how to make a spreadsheet with automated answers

These functions are some examples of formulas that can automate the results. However, there are several other ways to make a spreadsheet intelligent and automatic from the use of the variety of formulas and functions that Excel makes available for calculations.

To have these spreadsheets and get even more information about automating results follow our blog.

Excel Course - Conditional FormattingAre you in doubt? Get in on LUZ Excel Forum and send your question!

Excel Spreadsheets


  1. Hi Nathália, yes it is possible, but it is necessary to understand the structure of the table that you have so you can generate graphs and indicators

  2. Hi, Melisa, everything good? Just use the SE function to compare the values ​​according to the criteria you set. As for the icons, you can use the conditional formula

  3. Hello, I would like to know if there is a possibility to set up a question system in Excel so that as they are being added, they are added automatically.
    In this system, they would have questions related to a municipality (for example: ANALFABETISMO: 1 adults, 2 adolescents ...) that when filled in give the answer in graph format.

  4. Hi Rafael


    I need to automate a table in excel, I have some values ​​in a column that I call "Specification", in another column I have others that I call "Alert level", in another column I am going to enter the "Results", I need that at the moment of enter the results generate me in the same cell that I entered the result, an icon to indicate if it is accepted, rejected or precaution and finally in another cell I need to indicate if it is accepted or rejected.

    Thank you very much for your collaboration.

  5. Hello Rafael, good afternoon, how are you? Well, I know your time is $. I'll try not to take it, but thank you if you can help me. I needed to have a column with x names per line, for example, mace, orange, avocado and banana (4 rows). In banana I would have, banana x, banana and, z banana. Force each one in the second column from 0 to 10. On banana I can have 8 strength, but in the specifications of it, take 0 to 10 of each item. In this, I wanted to mount next to these columns a spreadsheet with 6 for example columns that repeated according to weakness, for example, if in banana I am strong 8,5 and in Mace 5, I have 7 days of the week, would have to repeat more mace than banana during this week and thus, also, to classify the Bananax specification, yez, then:

    Column with the product and byproduct, your strengths and so, in another worksheet, set up 7 days with the incidence of repetition for weaknesses, you need more banana, rather than apple. Thank you, sorry if I was not very clear.

  6. Just use a simple reference. For example, if the name column is A, in the second worksheet you would put something like = plan1! A1 and drag down

  7. Hi Ercilio, you can have macro / VBA code unlocked and then, at the end, block a particular flap without problems

  8. Hi everyone, I'm facing a difficulty, because it happens that I intend to hide formulas from my worksheet but without protecting the sheets of the same because it contains definitions of macros (vba) and protecting the sheets will prevent the macros

    I wish I could help me



  9. I have two spreadsheets with the same data, but in different columns, I would like to know how I can automatically have the second spreadsheet based on the column title of the first spreadsheet without me copying and pasting. For example, if in the first worksheet there is a column named "name" with a series of data filled in, the second worksheet must automatically populate with the same data based on the column title. How can this be done?

  10. Rafael, good evening. Nice talking to you!

    Let's say I have a spreadsheet for a sales team and want to show all team members by giving the name of the manager. For example: Enter the name of the manager in a cell and a column next to it would show all the members of her team. If you changed the manager's name, you would show the members of another team. Could you simply exemplify how I could do this?

    Strong hug and thanks again!

  11. Hi Fortunato, if you want to add specific items from different months, you should use SOMASE or SOMASES

  12. Hello, I need a formula that adds up to the value of one item and adds the total of the month to another. Example: Item 01 month of January. item 02 month of January. I just want the 01 item month by month. I will have several dates with different items monthly. Thank you for your cooperation.

  13. Good afternoon, my problem is that I can use my 10 autotunks to get four long trips randomly from different hours throughout the week from Monday to Saturday, but not to repeat the hours of travel, that is if the first one already I play a 5 hours job that I do not play again in the week

  14. Hi Lino, you can explain where your doubt and difficulty is, so I can try to help.

  15. In general, when using PivotTables it is important to clean the data to have the correct values, with no results. In your case, perhaps it would make sense to place a future or past date just to have the correct operation

  16. Hi, I am trying to create an event-driven VBA code, but I do not know if there is a way to save it on a specific date (I think it should), but I recommend using other types of events, such as closing of the spreadsheet or the activation of a particular tab.

  17. Good night
    how to schedule save in excel automatically?
    I want to create a rule in excel that saves a file every day at a certain time.


  18. Hello Rafael, good afternoon, I have to make a dynamic table showing a grouping of dates per month and per year, however I have empty cells in the date space, which I can not fill with another data, since they are outstanding there are, however at the time of displaying the data can not be grouped since there are empty spaces, how can I solve this situation?

  19. Good afternoon!

    I can not solve the situation!

    Where can I send the file?

    Thank you.

  20. Hi Thiago, for this you can create a release tab to measure, by date, how much each company takes to deliver a sample and then make a tab with the companies name and an AVERAGE probably to account for the average time per company per month to have these samples back

  21. Rafael, I have several companies that give me samples, I need to monitor the time in days that the companies took me to deliver these samples, I would like to make a report to select the company and obtain a graph showing the average waiting time in each month of the year .

  22. Thanks for the contact. I will follow these guidelines and post.

    Thank you!

  23. Depending on the structure that is mounted on your spreadsheet, you simply add the values. If you need any conditions, you can use SOMASE or SOMASES

  24. Hi Yan, you can use a procv to create a list with all the values ​​and then the remove duplicates functionality to keep only the originals

  25. Hi Daiane, there are several different forms, one of the simplest is using the PROCV

  26. Hi Lino, you will have to create an auxiliary table with each name individually and use SOMASE (S) or CONT.SE (S) to consolidate these values. Then it is enough to use the INDEX or PROCV function to generate a PivotTable according to the name entered that will generate the individual graph

  27. Rafael, I have another question, I want to search between spreadsheets one word, Example spent on food, and on another table add up all the values ​​that correspond to food with food.

  28. Good afternoon! What function do I use for every time I have a name that returns me in another worksheet, example I will create 6 basic baskets in different tabs, in another tab I want to bring the relation of all the materials but treating the repetition. ABA basket 1: Beans - Rice / Tab basket 2 Beans - Macaroni / Tab basket 3 Macaroni - Rice / On the 4 tab comes all the materials dealing with Duplicates Feijao - Rice - Macarrao

  29. Hi Rafael, could you explain me a way that inside the worksheet I have how to fetch data inside the table.
    Thank you!

  30. Good afternoon, Rafael.
    I have the following dilemma:
    I have a spreadsheet with several variables and I use the common display filter. That is, in the same column have different names (drop-down list).
    I need to generate individual graph for each value, I need a formula that copies the contents of the cell according to what is written on it, thus generating a table which will generate the graphs.

    Thank you!

  31. Hi Eliane, I believe that if you have the table ready, you can use PROCV to find this value

  32. Hi Vinicios, it was clear yes. For this you need to use named ranges, for example, the interval pedro, paulo, clara, etc should be called joão. Then simply use = INDIRECT (A1) within the list data validation (if A1 is the cell where john is written)

  33. Good evening Rafael, I need a formula where when entering a letter in a cell, the corresponding value should be searched in the table of pitagoras

  34. Good afternoon Rafael, I'm looking for a function like procv, but let me in the result use a list of items to choose ex: if in column A appear joão: in column b I would like to have the option to choose from a list pedro, paulo, carla, etc, as well as done when using the drop-down list in data validation. but if in column A appear carlos would like to have the option of a second list, I do not know if it was clear.

  35. There's even Kathy, but it's pretty hard work. In that case, instead of filling each month, you would need a table to consolidate the values. Probably the best way would be to create auxiliary columns to account for month by month the amount of future installments.

  36. I have separate accounts payable control per month in different tabs. Many accounts are parceled out, and I have to post one by one within each month. Is there any way for excel to launch or other months automatically; For example, every day 20 wins, and 10 plots are. The description, value, and everything else is the same, would only have to change the due date.

  37. Hi Matheus, the problem with PROCV for these cases is when you have duplicate values. If by joining two fields the values ​​become unique, I recommend that you use CONCATENAR to create a column with unique identification and then, when placing the search fields, use the CONCATENAR to arrive at a unique search value as well

  38. Good afternoon, My case is a bit more specific.

    I have a table something like this:
    Ø mm weight price
    30 100 5 10,00
    50 100 10 20,00
    100 100 20 30,00
    so I would need 3 cells where I would put the value that would be from the "Ø" column and the amount I want in "mm" and return me with the result of a calculation of the weight value / mm eg:
    50 120 6 12,00
    I searched for the 50 value of the first cell combined with the 120 value of the adjacent second and returned me in the other two cells following the calculated 6 value of the "weight" column with the 12,00 value of the "price" column also calculated.

  39. Hi Jean, in that case you could create a VBA code to just pick up the marked / replied items and paste it into another tab already ready for printing. It would be something like parsing the response cells and generating a copy and paste code to another location.

  40. You can use the LEFT and RIGHT functions to analyze the letter and the last digit in auxiliary columns and then create an SE conditional to evaluate if it is loaded or empty - then just count those that fit the rules you want

  41. Good morning, Rafael.
    I need your help, I need to know the following:

    where K - indicates a train type (loaded if the last digit is even, empty if odd eg K142 or K143)
    C - the same applies here.

    I want to know a formula to be able to get the quantity K loaded and empty as well as C loaded and empty.

  42. Good evening,

    I would like to create a worksheet where I would select the options that I would like and in the end generate the result with all that I pointed out, such as answering a questionnaire and after signing everything I answered the result were all the options that I marked.
    Could you help me?

  43. It depends on the type of summary you want, but probably with text manipulation functions - EXT.TEXT, LOCATE, LEFT, RIGHT

  44. You can create an auxiliary column to analyze the date AND also the status, so conditional formatting can use this column as a parameter

  45. Hi Jéssica, you will probably need to use VBA code to generate this kind of aumotatization

  46. Good morning, Rafael. Congratulations on your work.
    I would like to ask a question, I need to format a worksheet for an action plan, you should highlight the late dates if the task is not completed. Do you know how you can help me? I did a conditional formatting for the dates using the Today () function but in this way even the tasks that were considered completed (in the status column) their end date is highlighted as if it had not been.
    Can you help me?

    Thank you

  47. Hello, I would like to know how to automate worksheets that are exported from a tool, which are arranged by table, columns and rows with the exact numbering. Because the exported worksheet is separated into tags, they are the exact color for each tag.

    Thank you.

  48. Just make a table with the numbering in the first column and the texts in the second and then use the PROCV to, according to the value entered, search the corresponding text

  49. Hi Rafael, could you tell me how to do the following situation:
    I want to have two columns of information, where one has one value and the other a sentence, so
    1 My name is Jhonatan
    2 Brazil goal
    3 Argentina

    Then on another cell phone I put the number 1 and appear My name is Jhonatan
    or in another cell phone I type 2 and appear Brazil

    Can tell me? Thank you very much in advance

  50. Good morning, Rafael, congratulations to the site, I would like to ask for help, because I researched and found nowhere. I am developing a stock sheet, it has a tab with entries, which has quantity, code, and cost value and the other output tab has quantity, code and sales value, I would like to calculate the profit based on the last stock value understood. An example: I have 2 productx entries, 2 being units by $ 22, 2 units by $ 33 and 2 outputs by $ 40 and then 1 output by 41. In this I want you to fetch the stock of the first entry, and then look up the value of the second entry to calculate with the sales of $ 40 and $ 41, understood. Thank you

  51. You can create a formula for the pay date whenever it is blank, keep zero filled. If it is manual fill, you can create auxiliary columns to post the month and put an SE function for when it is empty return zero instead of 1

  52. Good Morning ! great site and relevant content.
    I would have gotten an idea, how can I develop a spreadsheet that contains names of people who get certain products, with quantity description of the product and daily dates and who manages me a monthly report.

  53. Good morning Rafael, whenever I have doubts in the formulation of research data here, now an issue has arisen ... Help me !! I am developing a financial worksheet and is drawn as follows for each line that corresponds to an invoice I open 12 columns each one corresponds to the month of the year plus 12 for payment date plus 12 for amount paid .. My goal is to add all the columns of the month and prospect the value that was paid or is open, for this use = SOMARPRODUCT with month function to filter only the determined months, however if the cell is empty the month function returns 1, then I have the wrong sum in January. If you can help me, thank you very much !!!

  54. Hi Rafael, good afternoon
    I'm trying to create a formula for stock control of my clothing store, I made the table where I added sums and multiplications. However, I can not get the formula where it finds each cell with size G for example and tell me how many pieces there are.
    Because work with clothes type a pants has p sizes to the gg and 6 different colors.
    How can I have this control?
    my order table is

    I thank

  55. Good evening, I would like to congratulate your website, literally gave me a light to carry out an academic work, thank you Rafael!

  56. Hi Malfatti, I believe you will have to create the button on each tab. It is also possible to create macros and VBA code attached to Excel events, such as closing the worksheet, opening the worksheet, etc.

  57. Hi Rafael,
    I would like to create a field as a text box and could type a column B information and it would return a column G information on the same line. Is it hard to do this? I believe that I would use procv and SE, but I do not know how to program it in a text box, to hide the unseen data from the other people I see.

  58. Hello Rafael Avila!
    I would like to know how to create a Save button on multiple worksheets, each of which represents one month of the year. Eg, when I'm using the January worksheet, I always open it that month. So, if I create a button to save on it, when I start to open the one from the month of February and so on, every month, do I have to necessarily create a button on each of them? Or is there some other feature that simplifies this?

  59. Hi Larissa, to lock, just change the protected cell formatting to protected and then lock the spreadsheets (REVIEW tab). About having values ​​being populated automatically, will depend on the structure you use, or you will have to use formulas or macros

  60. Hi Dreouns, It is possible to put the formula in the comment, but it will only have use as text. The formula itself will not work

  61. Hi Glaucia, I did not quite understand. However, in order to automate filters and forms of visualization it is necessary for you to create macros or VBA codes so that the visualizations change according to commands that you give

  62. You can use the INDIRECT function by typing the name of the tab in question

  63. Hi David, in these cases I like to use an auxiliary column concatenating numeric values ​​and some other identifier that makes this new value a unique value

  64. It is possible, yes, André, you can create a table that has the name of the states and the percentage attached to each one and then, where you need to add the percentage, use PROCV to search the percentage value according to the chosen state

  65. Excellent tutorial, Congratulations! with the function if, for each of the Brazilian states, inserted in a drop-down list, add a value to certain cells? example by choosing RJ from the drop-down list .... the prices of the products in the table, would be summed + 1% choosing MG would add + 3% and so on ... is it possible? I am having a lot of difficulties to set up this table. I will be very grateful if you can guide me

  66. Hi everyone, I need ideas to automate a spreadsheet. In this case I would like to bring BROWSE AND PRODUCT data from another worksheet to control sent mail. However, to such a spreadsheet that includes branch and product data (standard for every company) there is more than one product with the same numbering.

    The intention is to automate to speed up at the time of invoicing, because I spend a lot of time having to verify branch and product at a time.

    Those who can help me will be grateful.

  67. Hi Rafael ... I would like you to help me ... I created 31 spreadsheets for each day of the month represented my input and outputs of sales and expenses ... I would like that when I click on the cash flow the day 15 / 03 / 2018 for example it would fetch the 15 / 03 / 2018 worksheet. How do I tie these dates individually? Help me please….

  68. Good morning,
    I need help urgently. I'm developing a spreadsheet for a client.
    I have an 2 spreadsheet, from vendors with PROCV formulas (removing information from the 1 worksheet with data from all vendors). How do I leave access for direct click when I have an e-mail site in the 2 worksheet cell. Example: 1 Worksheet = Vendor Data in Text. 2 worksheet = PROCV formulas to find only the vendor I need, BUT IN THE FIELD OF SITES AND E-MAIL'S, the formulas are included and I would like to be able to click on the sites / e-mail's and this will lead me straight to the desired one.

  69. Good afternoon!
    I need help urgenteeeee !!!
    I'm doing an automated spreadsheet for performance tracking, with links leading to folders, but I'd like for example:
    When I chose the department of personal department, in the tab of the positions only appear the positions related to this sector and that when I did a training register the students and in the tab where I analyze individually each student already pull the notes of them individually referring to 1º test and the second test of the database is that through a consolidation of data, type la in the data consolidation already would be registered the name of each one and when I chose one already would appear the notes that were registered in the database. I do not know if you understood very well but basically that's it.

  70. Good morning Rafael:
    work in goggle spreadsheets. I would like to display a note or comment containing a formula to explain X
    Is it possible to make notes or comments with formulas?

  71. Good morning, Rafael.

    I'm having a hard time finishing a goal sheet, in which I need to lock some rows / columns so that no one changes their punctuation values ​​and in another column I need to not only lock the punctuation but also add the punctuation automatically. score and would only add up the new ones.

    Thanks for the help


  72. Hi Leo, just use a simple formula, assuming the weight is in cell A1 and the amount in units in cell A2 in cell A3 you can put = A1 / A2, which will give you the amount in kg to generate 1 unit. In this case, the answer is 0,807 kg

    now in the B1 and B2 cells you can leave fields to fill weight and units respectively and create the formulas in cells C1 and C2
    in C1 you can calculate the amount of units generated by the weight of B1 then = B1 / A3
    in C2 you can calculate the weight needed to produce x units of B2 then = B2 * A3

  73. Hello Rafael, good morning!
    I need guidance for:
    I have a rule of three in which I calculate the yield for a given quantity of product.
    I need to get to the required ingredients (volumes) by putting the yield. In the situation I already have I have a basic recipe, ie: Ingredients (total weight in kg 21) and its yield (26 un).
    I want to put units and return the necessary weight, as well as put the weight and return the yield.
    I appreciate your help.

  74. Hi Josi, this will depend a lot on the structure you use in the spreadsheet. One possibility is to use columns for the count of inputs and outputs of the product in the inventory and one of total that is showing the quantity that has

  75. Hello Rafael, I need to create a spreadsheet where on one tab I put the sales orders that came out on the day and on another tab he finds the product sold and withdraws from the stock. Can you help me?

  76. Hi Lucas, can not you increase the size of the second worksheet to receive these 500 lines?

  77. As I transfer data from one worksheet to another, the first worksheet with 500 lines of information and the one I want to fill out with the information has 200. However, both have validation, and the information I want to get is not in order, that is, if I were to search one by one it would take a long time, can you help me?

  78. Greetings, Rafael.
    I am trying to perform an action on a spreadsheet that I use at school where I work, but I could not understand if it is possible to read this article and would like to know if you can help me:
    I have a spreadsheet with school hours per class / day (it goes one way):
    6 YEAR 7 YEAR
    1ª aula CIRLEI (Mat) SÔNIA (His)
    2ª fair 2ª aula PAULO HENRIQUE (Cie) CIRLEI (Mat)
    3ª aula PAULO HENRIQUE (Cie) ELISANGELA (Literature)
    And in another worksheet in the same folder I would like to "pull" teacher's classes, as in the example below:

    Hours Mon Tue
    01º 8º YEAR M 8º YEAR M
    02º 6º YEAR M 8º YEAR M

    How could I do that?
    Thank you for your help.

  79. Hi Ariana, this can be a very simple spreadsheet. Simply put a numbering from 1 to 159 in column A, put the values ​​in column B and in C mark as Pay or Not Paid and follow the amounts paid.

  80. Good Morning!

    I would like to know if you have some kind of spreadsheet model that calculates the delays, absences and extras of hourly teachers. Because all that I encounter on the internet only has for employees with daily workload.

  81. good morning

    I need to create a spreadsheet for a client, in fact it owes us a value of 32.744,50 and I have already calculated the interest for it is liquidating with us, it can give 250,00 every Monday, totaling 159 plots, ie 159 weeks, I would like to do a spreadsheet with this data to control ours to receive, has some way or solution, I'm lost, I do not handle almost anything excel, thank you

  82. Hi Edilson, how are you? You can use an SE within SE with PROCVs. You would use a cell to say whether to analyze the name, the RG, or the CPF. And the SE within SE will use that value. It would be something like = SE (A1 = "Name"; PROCV (etc); SE (A1 = "CPF"; PROCV (etc); PROCV (etc))) - this for every field you want to place

  83. Hi Thiago, I'm not sure exactly what formula you should use, but most likely you'll have to standardize file names to create standard opening links for them. This can be done via VBA code

  84. Hi Allan, I suggest you post your question in our forum too - forum.luz.vc

  85. Hello Rafael Avila!
    Satisfaction in greeting you.
    First of all I would like to congratulate you for the excellent work you have been doing in this magnificent work tool (excel) and sharing with us. I know you are a very busy person, but I would be very grateful if you would give me a few minutes of your precious time to help me with a spreadsheet command I created.
    I've created a worksheet where I want to list (four hundred) names of employees on a tab with personal and professional information about them. On another tab I want to look for these employees and show their registered information. Remembering that this search can be done either by the name, RG or CPF of the person and below show the search result. How can I make this setting? Is it possible to have a picture of the employee also appear in the search result ?!
    Any questions I can send you the worksheet for a better analysis, okay!
    Thank you in advance for your attention.



    A1 - A2 - A3 - A4 - A5 - A6 (OBS IN EACH CELL IS A NUMBER)
    3 - 6 - 10 - 17 - 34 - 37

    How many times have you left
    47 37 + 1 = 37 (47 DOES NOT LEAVE TO TAKE OVER 1)


  87. hello,
    Congratulations on the job.
    I would like a formula that opens a hyperlink according to the current date,

    eg if the date is the same as today (09 / 02 / 2018) open the worksheet for February

  88. Good evening, I need a formula that gives me several different results from selected sample cells
    I selected B4 C4 D4 E4 F4 G4 UNTIL B2014 to G2014.

    The contents of the cells are just the colors I wanted the results do not matter ONLY the colors with the result of M4 example down only those 4 colors with all possible outcomes, even if it only has one color within the cell but always in the order BLUE COLOR CELL, BLUE COLOR CELL, BLUE COLOR CELL, BLUE COLOR CELL.
    color not showing results 0 and only one color appearing result 6 - 0 - 0 - 0 or 0 - 6 - 0 - 0 or 0 - 0 - 6 - 0 or 0 - 0 - 0 - 6. I will not need the option 0 - 0 - 0 - 0 but it's nice to have it also to indicate that something was not filled with any color. Why doing it one by one is always annoying when you have more than 2.000.

    TOTUM M4

    2 - 1 - 3 - 0 6

    0 - 1 - 2 - 3 6

    1 - 3 - 0 - 2 6

    2 - 2 - 1 - 1 6

    2 - 3 - 0 - 1 6

    2 - 2 - 0 - 2 6

    2 - 0 - 0 - 4 6

    if you can not understand here is a link of a forum that I am where I put images but until today nobody knows to take my doubts.


  89. Good afternoon, Rafael.
    I need to create a spreadsheet, with formulas that when entering a code (number), it returns me the name of the product related to that code,
    type in cell "120" to return "pumpkin",
    I do not know if I could get through to my situation.

  90. Hi Rodrigo, you only have to use PROCV. In this case, it is necessary that you have a table with the names and the classification of these names in client or any other nomenclature that you want

  91. Hi Isabelly, in this case you can use the function SE - = SE (E1 = "NA", "X"; E1)

  92. Hi Julio, I would use one more column or row, auxiliary, to use numbers according to the values ​​of the columns. This way you can make counts conditioned to the values ​​in these extra columns.

  93. Good afternoon, how are you ? I need great help !! I need a formula where I can add the empty columns that are in sequence and at the same time adding the ones that are in range can help me ??

  94. Hello good afternoon! I need a help haha
    I want to condition the results of a cell according to the answer of the previous cell can you tell me how?
    For example: I'm referring to all the cells in column E and F, If in the cell of E der "NA" I want to automatically F give "X" .. I was working with drop down list, but I realized it would be easier if it filled alone.

  95. Good Afternoon, I would like a light on a formula, in which I type a Name: Rodrigo and Get a Result: Client.

  96. Hi Renato, I usually prefer to settle here. Faster and more practical. In that case you need to have a reference table that says what value is what. For example column A writes 200, in B writes light degree.

    Then just use a PROCV by searching the reference value of the patient's grade, in the matrix reference table created, with 2 index columns and TRUE at the end, since you are looking for approximate values

  97. Hi Mariana, in this case it is necessary to use the decimal value of the percentages. 10% = 0,1 and 15% = 0,15. So his formula would look like this:
    = 1000 + 0,1 / 0,15
    A care here is with the order of operations, in which case 0,1 / 0,15 would be the first operation done. If you want it to be the sum, you need to use parentheses

  98. Hello good afternoon,
    I need help, I would like to know which formula I can use to add and divide values ​​but with percentage. I tried several shapes but the value does not beat.
    1000 + 10% / 15%.

    I thank you.

  99. Rafael Avila okay I'm with a small problem I'm using a table that says the degree of the patients' illness that I take care of most I can not make a formula that can use the example reference values ​​between 100 to 200 light degree 200 to 250 moderate degree type that Could you look at my table to help me with my email? renathorodrigues@hotmail.com I'll send you an attachment

  100. Hi John, you can use CONT.SE to find out whether the count is equal to or greater than 1 - so you'll know if the part described is being used on the other tab. And you can also use PROCV to fetch the rating value according to a selected part.

  101. Good morning, I have a question. I made a database with several parts names for example and I classified it as car and motorcycle ... I need something that when I have to compare data from another worksheet, it verifies that it already has that part and marks like that being car or motorcycle according to my table of data. Can you do that?

  102. Hi Rafael, I do not remember having made a specific text like this, but usually using the conditional SE by making a comparison of the review date with the TODAY function already helps. Then just use the conditional formatting to change the color of the cell according to the text that appears in it.

  103. Hi Juciney, it all depends on the structure you have created. If you have only one cell to put the values ​​into, you may need to write a macro so that the value is subtracted without necessarily using a formula, and every time you input a data, it decreases from the total.

  104. Good afternoon, Rafael, I would like to know how to create a list of automatic daily payments, that is, I have an Excel table of payment to suppliers and what I wanted was for invoices that had payment dates today or older automatically copied to another Excel sheet and thus create a list and know what was in arrears up to today without having to see a vast list with everything mixed (paid and not paid).

    Since now, thank you very much.

  105. Good afternoon, Rafael, I would like to know if you have some text explaining how to do a spreadsheet in cycle, so that when I type the date of the second revision it informs me in color if this is in the time to do a new revision or not , I saw the spreadsheet in a youtube video. Thank you very much

  106. I wanted a formula to zero the included numbering, for example: I have a stock worksheet, when I will write down the number 1 in the other column my total decreases one qtd, but then on the other day that I will give output in the same product I have to type in 2 (1 + 1), I would like that when the first low number is added, it will reset itself, but without giving up the output in question

  107. You can use INDIRECT to do the calculations, as this last day's tab will always have the number of the day. You can use the TODAY function with the DAY function to find this value and link the INDIRECT function

  108. Hi Rafael

    I need to attach a tab that does averages calculations, however it needs to always pick up the values ​​that are made the averages of the last tab created. Every day we create a new tab with the date of that day. Hence there is always one last constant flap that takes the values ​​and calculates the averages. How do I link this spreadsheet to the last tab created daily?

    Thank you.

  109. Hi John, if you have done auxiliary tables calculating these values, you would only need to use the CONCATENATE function to join those pieces of text and indicators together.






  111. I liked all the tips and comments however I need another type of summary. If anyone can help I leave my email: portoescalada.joao@hotmail.com
    my spreadsheet has: date - nameplate - name - load - value
    on the same date we have more than one load for the same board or many different boards.
    I need to summarize for example:
    day 1 - Z plate had 3 loads in a total of 10 ton
    day 1 - plate D had 1 load - total 10 ton
    day 1 - plate A had 7 loads - total 60 ton
    day 2 - plate A - 1 load - 4 ton
    day 2 - X plate - 2 loads - 3 ton
    etc ...
    If you can help me, thank you. John

  112. Hi Márcia, just use formulas with the sum sign (+) or multiplication (*) referencing the cells where the values ​​of the measures are.


  114. Hello Rafael, I also have a problem and would like some help:
    I need the join of 2 tables
    In a table called Cash Out - By Credit date, we have detailed amounts of payments and other important information.
    In another table called Cash Out (Single Box), I have somatized values ​​with other fields.
    I need to join these 2 tables into a single one, I have in common the Customer Code (which is unique) and the student's name.
    It would be necessary to place the toral value of the parcels as soon as each value is
    If you can get me that doubt, I will be extremely grateful.

  115. Rafael, good afternoon.
    Congratulations on the blog, very good and useful information.
    I would like your help in a spreadsheet I am updating.
    Ex: 1 worksheet 2 worksheet 3 worksheet (output) ... I would like the balance sheet to fetch all the items that are given as input by entering it by its code that is registered in the worksheet "balance" and the input value subtract with the values ​​from the output sheet. Entry sheet will be typed by sequential date and so the balance amount will already subtract from the output sheet if you have this product / item typed tbm .... it would be possible?
    Thank you very much

  116. It's possible Janaina, just use the conditional SE to establish a rule.

  117. Hi Lucas, in this case you can use the SE function of this form = SE (B1 = "x"; 150; 0)

  118. Hi Paulo, I believe that if you use an SE conditional to get the values ​​of the effective date of the endorsement when it is filled and, if it is empty, take the same initial validity X

  119. Good afternoon Rafael
    I have a spreadsheet that, from the completion of the response 'X', will appear in another tab the given value by that name 'X'.

    It's possible?

  120. Good afternoon.

    I need to make a form that, from the completion of the response 'X', will appear in the column next to a certain value.

    Ex: Column A1 (Question) = Do you want to create an excel formula?
    Column B1 (Response) = x (if it only fills in the case of an affirmative answer)
    Column C1 (Value) = 150 (appears, in the case of the affirmative answer of the previous item, the value of the service).

    Can you help? Thank you!

  121. Good morning, Rafael!
    I need to do the following:
    In an insurance worksheet, the customer has Insurer XXXX's automobile insurance policy with Sale Date 1, Initial Term X and Final Term Y.
    If this same client makes an endorsement of the same insurance policy of the Insurer XXXX, Sale Date 2, the Initial Term will be the date of the endorsement, but the Final Term will be Y, as it is the same insurance this date does not change.
    Note: The end date for this same endorsed product will always be the same regardless of how many endorsements occur.
    Can you help me build this formula?

  122. Hi Daiane, the error may be in the quotation marks that you pasted here, I do not know if Excel is understanding them with the normal quotation marks. If it is, it does not make much sense if the C-related SE does not work. Regarding the B return error, you can use the SEERRO at the beginning of the function to ignore errors.

  123. Good Morning! Rafael, I'm breaking my head with a formula that does not work for days. I need a simple calculation that returns me the ZERO value if the cell of the calculated column is ZERO, so that this data does not enter the graph. I am using the following formula: SE (C6> = 1; TODAY () - B6; ""), in column B has the protocol date of the note, in column C has quantity referring to the note item and in column D the formula which should be empty if the item quantity is zero, but when the quantity is zero in column C the formula continues to calculate the days that the item is on the client and when column B is empty returns error. Can you help me? I hope I was clear in my explanation of the problem.

  124. How good it was to help Sergio! Hugs and success with the spreadsheet

  125. Sergio, what's up? I imagine that you can reach this result using the SOMASES function, which is a sum conditioned to some criteria that you can establish, for example, the type of cut or time of entry. If I can send the worksheet, I can see if that's the case or if I have any other tips. My email is rafael@luz.vc

  126. Hello! If anyone can help me, I thank you right away .. I have a spreadsheet in which I say entry of sugar cane in the plant, for example in column A beginning with the date, in B I throw the weight sa load, 50 ton for example, and at the start of the schedule, and at the beginning of each era (Machining) or Entire (Manual Cutting). Anyway, every day this worksheet closes with an average of 50 lines lines of launches, that is, 50 loads entered the plant. I now need a formula that I can draw a summary of the quant. of cane that entered the day, summarizing by the schedule and the type of cut. For example, we have 3 work shifts, the first is from 7h to 15h, so I would like a formula that already pulls from that worksheet the amount that came from cane in that period, from 7h to 15h, and separating by type of cut, Q and I. Could it be possible to do this with a normal formula? without involving dynamic tables, macros. and etc?? If anyone can help me, thank you! I do not understand much of excel

  127. Hi Suelen, for this you need to create an auxiliary table that has in the rows the plates and in the columns the months. Then just use a SOMASES making a sum conditioned to these established criteria

  128. Hello, I need a formula that separates by plate and month, bringing the billing of each one. I have the mother worksheet with the data of all year, with the total loading of the company and I want to see the results of each one. Thank you very much in advance

  129. Hi Luiz, in this case you are referring to tabs from the same worksheet or different files (workbooks)?

    To do this interaction you need to create references in the reports, searching for the values ​​added in the customer registry

  130. Good morning, Rafael, how are you?
    I have a client registry, but I need to create a worksheet that interacts with the others in the file, for example: a worksheet is destined for the weekly visitation report, another weekly schedule sheet. How can I take advantage of this customer database to interact with these two worksheets? Hugs

  131. Hi Jessica, you just need to create a formula. Assuming the value 1000 is in cell A1 and the value 10% in cell B1. In cell C1 you can put = A1 * B1 to have the value of this calculation. In this case I would give 100. But if you change the value from B1 to 20%, C1 will switch to 200 and so on. Then just use C1 as the reference for other formulas.

  132. Hello
    I want to put in excel the percentage value over the total value and when change the percentage, the value change automatically.
    For example: Suppose I have the value of $ 1.000,00 and want only 10% of that value for the next account, but as soon as I wanted to change the percentage to 20%, the value would appear without me needing to calculate anything else.
    Does anyone know how to do this? Please

  133. Hi Viviam, I believe you have to use the equal sign on every cell in the flap. It would be possible to do a procedure of this via VBA code, but I think it would give more work, since when using =, you can use the fill handle to facilitate the use of the formulas

  134. Hi good afternoon! I have a financial worksheet that accompanies the project deliverables in one of the tabs, I would like to copy this tab to another worksheet (where I will centralize all the deliverable tabs of all the projects, I called this portfolio sheet) automatic (if I change anything in the financial result worksheet, automatically update in the portfolio). I can do this in cells (using =) but I wanted to do it for an entire flap. It's possible?

  135. Good evening dear, I need help with a debit worksheet because I would like to put it to do the automatic sum through the status "paid" and "not paid"

  136. Good morning, your site has already helped me several times, since already my thanks.
    I am again with a problem, my boss asked me to do a database of all the material we have nanossa manufactures, this was easy. So in this tab the material says whether it is accepted or not ...

    The problem is that any material that says "yes" (which is accepted) it that which is automatically transferred to aba2, is not only the name but how does it measures the weight etc.

    If you can help me, thank you.
    Thank you

  137. Hi Daniel, it would only be possible with a macro probably. When creating a new tab in Excel, unfortunately it does not allow this type of automation

  138. Hello, I have a question, I think it's simple. I need to create an automatic sequential formula between "tabs". The tabs are all the same, but I need the new tab to always get the reference from the last date of the previous tab. However, when I copy the sheet from the previous tab and paste it at the end of the tabs, the formula remains that of the other tab.

    For example.
    In the 2 tab, the formula pulls the last date of the 1 tab, and on top of it, I sequence in dates.
    When I create the 3 tab, I want it to do the same thing automatically, which already takes the last date of the 2 tab, sequence in the dates, and so on when creating other tabs.

    However, when I create the 3 tab, I have to manually change the formula so that it takes the reference from the last date of the 2 tab, since it comes with the reference of the 1 tab, taking the last date of the 1 tab, instead of the 2 tab .

    Is it possible to automate this?

  139. I know it has a doubt time, the ideal in this situation is to use programming in VBA, could use the "select case".

  140. Hi Cláudio, the problem is that you are trying to make a text with the CONCATENAR function and, in these cases, Excel does not understand the formatting used. In this case, what I advise is that you add the formatting with more CONCATENATE functions. For example:

    = & "We invoice: R $" & A1 & ", 00")

    assuming the value is in A1

  141. Hi Otávio, I did not quite understand your doubt. In general I would use a PROCV to search the field according to the other data found.

  142. Good afternoon, how are you?

    I have the following doubt:

    I have a car spreadsheet with the following fields: ID, name, address, car plate, type of car, Used (Y / N), Purchased (Y / N) and city.

    My boss asked me to do another spreadsheet where I do a search for all the cars not bought with a justification. So I did this through the Advanced Search system where I created another worksheet called Car Selection with the Car Plate, Purchased (s / N) fields and a Search button (Macro was created through the Advanced Filter). MAs I want to put a Justiicativa field in the Cars selection worksheet and how should I make it fixed? Because every time I update (Clicking on the button), the field Justify permamence intact and mixing the data.

    Thank you!

  143. Hello Rafael, I'm having difficulty putting custom format inside the automatic replies, I want to put the format of currencies with decimal places the following format R $ #. ## 0,00, only I tried to do but I could not use the text function, if yes how should it be the syntax of the formula follows image.

  144. Hi Pedro, I did not quite understand the logic that you will use for this, but I imagine that a PROCV solves your case for a specific value. If you do not know the values ​​a priori, you would need to think about how to create a condition that analyzes the list, for example an SE

  145. Hi Leo, just use the conditional function SE

    Assuming that the value of 0 to 100 is in the A1 cell, just use

    = SE (A1 <51; "Good"; "Bad")

  146. I need to create a spreadsheet if it gives like this if the sum of that of 0 to 50 it appears a text in another cell type Good and of 51 the 70 appears in the same cell bad type so it has like

  147. Good evening. I admire Excel, I am intermediate, intermediate. In fact, I've never been able to afford an advanced course so that I can excel in every aspect of it, and I always forget the little I know.
    My question is: At work, where we perform services such as - removal of debris, weeding, pounding, pruning of trees, and other service, some are forwarded memos to secretariat-specific city hall, with 50% return only. We have a spreadsheet according to the data below:

    In this way, when it includes the date of request, it remains as PENDING, when it includes the date of service, it remains as ATTENDED. However, I need a way of when the attendance is only from Capina where a memo is sent and when the response is received, if it is done, the field is: MEMORANDO DONE, OR MEMORANDUM.

    Another thing: I have to do a weekly report and another monthly, where I expose to the manager the amount of debris, weeding, pruning, slugging, and other ATTENDED, and PENDING. Then in the amount of truck moving that is in another separate file, I make graphs and other data for the report.

    I need to make these worksheets talk to each other, so that when typing in the service request entry, the data can generate one or more automatic reports to be owned weekly, and when to do so end of the month. Otherwise I could put together all the files that generate data such as the attendance, the movement of trucks that gives me the amount of travel and tons of the week or month in a file only.

    Just wanting to do this, to make it easier for me to send my reports, and for my colleagues to insert data and generate reports without my presence, that is, for the knowledge to reach everyone.

    If you need to send more sample files to explain better, please send me an email, marcosilvacar@yahoo.com.br

    If there is a way to give a complete Excel course, tell me how I can pay and how to do it, okay.
    Hugs to all.

  148. Hi Maikon,

    without using macros or VBA programming really hampers the logic of creating a formula with the requests. What I thought about here, but I do not know if it solved by having repeated order numbers was to create a conditional formula where only the value would appear if it were less than or equal to the quantity of orders.

  149. https://uploads.disquscdn.com/images/e4878dd1ad8c6105b8348aa196188a554f9c3661514f7c6147c9dad5666fa822.jpg Good morning people. I'm having a problem scheduling a function in an excel worksheet.

    I am sending a base worksheet and wanted to see if anyone of you can find a solution, because I could not. I wanted to make a list of orders where when I say that an order was made for 5 units of a product by the same customer, the worksheet already automatically fills the order number of the listing. But I need it without Vba programming or macros, because I'm going to play this spreadsheet on Onidrive. I am sending the example in image. I hope someone can, because I've already broken my mind about it.

    Thank you

  150. Good morning, Rafael.
    Thanks a lot I did some searches with the procv function in other spreadsheets and I really had no problems, in this until I tried and I did not succeed. I will insist by taking advantage of your guidance.

  151. Hi Marco, as far as I understand, a PROCV would solve. Just create a table where, in the first column you would put the values, w34, y34, aa34, and so on and in the next column (the right of the first) put the values ​​x34, z34, ab34, and so on. Then just use PROCV to find the value of BB33.

  152. hello
    I would like help to solve the attached formula. I need to have 10 conditions if and only excel allows 8.
    I use excel 2013.
    My thanks.

  153. Good Morning!

    I have a spreadsheet as below, which will be used throughout the year:
    Then, after weeks 02 to 52.

    I need in another worksheet, to return the last value entered for each category (protests: value / quantity, lawsuits: value / units ...) so that when I open it is updated with the last value taken ...
    I'm trying to get the formulas off and on, but I can not do it.

    One detail is that I have 452 companies that want to do this monitoring, so when you open the worksheet, I need q to come the last completed data of each company

  154. Good afternoon, thank you for the tips. I'm having a hard time. I am updating my spreadsheet I use at the machine shop. I'm an Excel lover. I have a card where I put the breakdown of the pieces. and I would like a macro to insert more rows if you need to discriminate more pieces. I did the Macro, but when I drag the work order register worksheet below, then fill in the next 002,003 services ..., the Macro only obeys the Order of Service No. 1 determined "OS." 001 (red arrow) and is adding lines in 001. I made another attempt with Prov, as we can see on the red arrow, but it did not work. The line I would like to insert is on the penultimate line as indicated by the blue arrow. But I would like each plug end to have its own button for inserting lines. I'll try to send a print. Thanks if you can help me.


  155. Hi Areta, I imagine you can do this with a macro that parses a cell with the day function.

    You would do the following. I would place two cells, one with the TODAY function and the other with the date of today cast as values. Then you would do a macro that would only function conditioned the TODAY function to be larger than the other date. Being larger, puts the systems in the to-do list, if not, maintains the current status

  156. Hi, I have a problem: my boss asked me the following task:

    1-In the initial tab, you have a list below the survey button that shows the systems that have not yet been surveyed.
    The employee clicks on the VISTORIA button and enters the inspection tab of the survey.
    2-The employee chooses one of the systems that have not yet been surveyed and records the survey
    3-The system that has been inspected leaves the pending list of the initial tab

    My problem is: how do all systems get back to the to-do list every day at midnight?

  157. Another possibility is to use conditional formatting to warn of duplicate values

  158. Yes you have Mikael, just create this column and in the sum formula, use the conditional sum function SOMASE or SOMASES if you have more than one criterion

  159. Hi Pedro, in order to do this you need to use the external data retrieval feature, it is in the DATA tab. With it you can get data from the site of interest (the format is not always the friendliest). From this data you can create formulas using the cells in question without problems. About the daily update, you can do the setup in the field of obtaining external data for it to update periodically.

    Once the data is being pulled correctly the formulas should work optimally.

  160. Hi Pedro, in order to do this you need to use the external data retrieval feature, it is in the DATA tab. With it you can get data from the site of interest (the format is not always the friendliest). From this data you can create formulas using the cells in question without problems. About the daily update, you can do the setup in the field of obtaining external data for it to update periodically.

    Once the data is being pulled correctly the formulas should work optimally.

  161. Wave

    I need to set up a spreadsheet with daily data extracted from a website.
    these daily data should be pulled from a website, and added to the worksheet, adding up to those of the other monthly general days. how can I optimize this worksheet.

  162. I already have a very simple table where I put only the information I should in each month, and I will manually remove the items that have already been paid so that the sum at the end is changed so that I know how much I still have to pay ... that is me add the values ​​and retreat the values ​​to know what I still owe .. the only thing that is done automatically is the sum .. just this .. I would like to know if you have how I apply some formula to my table so that I create a field where I would like to inform you of "Payment" and "Not Paid" for example, and a condition if the word is "Not Paid", for example it adds that line, and if it is "Paid" the sum will ignore the value of that line, and tbm a place that some td q is with the word "Paid" so that I know exactly what I already paid and what I have not paid yet .. I believe that it is not something very complex this ... but I do not know to do although I already know some formulas for work with Excel, or maybe I already have used but do not remember me .. I'm looking forward to contact and appreciate if someone can help me .. Att Mikael Correa

  163. can do so:

    in the data tab in a date cell, leave the formula that searches the dates in the charts if there is one, the formula is:


    - notice that the formula checks if this is empty and if it is not empty it takes the date of the chart, check that there is a priority, then when you use this feature you can not leave dates in the spreadsheets, because the formula will prioritize, improve and much

    h13 = graphic data1
    h14 = graphic data2
    h15 = graphic data3

    Another elegant option would be the use of macro, enable developer mode, in the developer tab click record macro (leave an easy shortcut to execute the macro), click on the date cell of the 1 graphic, copy the contents, go to the tab click on the date cell, paste the cell that you copied, click stop recording.

    now every time you run this macro excel will do the same procedure for you, just do not forget to put the date in grafico1 (the value that will be copied) before running the macro.

    NOTE: You will have to create a macro for each graph.

    Good luck

  164. in the spreadsheet make sure all data has header, select the header along with all data, in the tab of "home page" excel has the option "sort and filter", click there.

    each header will now have a small number, click on it and you can organize your spreadsheet to show only what you want and hide the other lines.

    va in the approval header, let selected only "approved" ready excel will only show the ones you want.

  165. if the worksheet is formatted as you described, we can use the formula as follows:

    = SE (RIGHT (X; 2) = "35"; "Repeat"; ")

    Change x for the cell you are using, use the excel cross to drag the formula to the other lines to speed up your work.

    You can also select the column where these data are, hit the shortcut "ctrl u" you will get access to the tool where you can tell the excel phrases and their replacement, very practical.


  166. use the formula if, follow the formula:
    being x the cell number you want to check.


    Espero ter ajudado.

  167. Good afternoon!
    I need to do a relapse control on an excel spreadsheet, in case to control if such a person has already failed a working instruction numbering. Example:

    Name It Status
    John it 35
    José it 10
    John it 25
    John it 35

    I needed the name John IT 35 to be marked "Repeated" in the Status column.

    Thank you very much in advance!

  168. Hello, good morning, I need to make a spreadsheet in which I refer to values, Ex; from 0 to 10 = R $ 2,35, from 11 to 50 = R $ 4,40, in this way if you type in a cell the value ex: 34 will appear the value of R $ 4,40

  169. Hi, Rafael, I need some help!
    When using the cont.se function, it returns me x values ​​based on any criteria.
    For example, to know the number of "approved", then the cont.se form returns me, 4.
    How do I know which ones were approved within my spreadsheet?
    Thank you very much in advance. Abcs!

  170. Good Morning. I need to make a spreadsheet with the following structure:
    Column A - Customer Code
    Column B - Date the customer was served -
    Column C - Data of equipment serviced
    * As each customer has more than one equipment, I will repeat the information in the lines below, ie 1 equipment on each line but with the same customer data. The only data that will vary will be the date of service. I would like to know how to fill in the date the customer was served on the rows that repeat the data from the same. I do not know if I could explain it well, but it would be this: In the first line of the 10 client I fill in the date 17 / 10 / 2016 and I want it in the other date columns of this client to repeat the date. Thanks

  171. hello, I really wanted to make an automated numbering worksheet using 33 # of 16 in 16, if it had a type key generate combinations and that gave me as a result all possible combinations.ex: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,18, etc. to 33.
    can you help me ... from now thank you ... remembering that I have no practice with spreadsheets ...

  172. Only with this information open is it a little complicated to help Douglas, but in general it is important that you create links and formulas that allow this crossing automatically

  173. Hello everyone, I have the following problem:
    I use 5 different spreadsheets to get one. After that, I have to generate some more spreadsheets. Of the 5 spreadsheets, I need to cross-reference their 4 information against 1 and then generate the spreadsheet. Then generate other worksheets corresponding to each category. Suggestions?

  174. Hi Adonis, I thought of some solutions here.
    1 - You can copy and paste all data from the data tab into the tabs of the graphs and make the change of those data directly there. These solutions are only useful if you do not have many variations on the data tab
    2 - You can create a conditional SE formula in the date cell of the data tab by taking only the cell that is populated in the graphics tab. That way you would create 3 cells (one on each tab) and leave them blank, when filled, that reference would be pulled by the data tab and you would see the data normally. The problem here is that this would only work for one chart at a time. After analyzing it would be necessary to delete the date cell to be able to have that same view somewhere else

  175. What I'm trying to do is this:
    I have 3 charts, in different "tabs", which show the evolution of 6 months of Revenue, Expenses and Profit of the company. All three graphs are linked to the same worksheet (data).
    In the "data" tab there is a cell with a date, which will indicate the start date of the analysis. That is, when I change this date will change the three charts.
    What I need is that I can change this date from any of the charts without having to get back on the data tab.

  176. I do not quite understand what you want, Adonis. You can create references in yes worksheets, you can also create formulas to analyze information and change the value according to what was done.

  177. Good afternoon!

    Is it possible for me to have a cell in several different worksheets that modify the same cell in a main worksheet, ie will the last changed value return in the main worksheet?

    Thank you.

  178. OI Elisangela, I've never worked a lot with photos / images in spreadsheets because I do not think it's the best place for this, but I imagine it might be possible to create a macro or VBA code to fetch the contents of a cell (which will be the image address on your computer) and insert the image from there. As it never did, I can not give you the way of stones, but it would be the procedures that I would try to do

    I also do not know if it will help you, but I found this video on YouTube - https://www.youtube.com/watch?v=TvOqsKg9mJc e

  179. Hello I need to insert photos automatically into a photographic report that is already pre created with a spreadsheet that runs in macro, however it goes in the average of 100 photos per worksheet !!! It's taking a lot of time to put them manually !! How do I do

  180. Thank you. I was able to develop the macro.
    However, I had another problem: When I type in Column i of the Hospital Worksheet, the date appears in any cell in the 4 Column of the Control Worksheet (when I developed it, I did not even think about it, but for a control of the sheets in my folder, appear in the cell of the row in which it refers to Hospital, that is, the function must run in a given cell and not in the entire Column).
    see only https://uploads.disquscdn.com/images/3c035629abfb3f72740f8da0722839f49e1c8dac6f5c0a176bfc0ab708fa795d.png https://uploads.disquscdn.com/images/fbc2e4c34c80c0eed6fd03c8cfb82310e5773aae8bbe422616f137bb0e2dd26a.png

    If you have any tips or step-by-step how-to, I'd be grateful.

  181. You can create a macro for this without problems. The procedure would look something like this:

    - use relative references
    - when pressing the macro button it should move to the 4 column (ie 5 times to the left)
    - insert the function TODAY
    - select the cell where the formula was added, copy and paste values

  182. Good Afternoon!
    I want a macro where the "today" function fires when I type in a given column of a worksheet and the date appears in another worksheet.
    When I type "NO" (or something else) in the 9 Column (or i) of the Hospital Worksheet, I want the date of the day that I typed to appear in the 4 Column (or D) of the Control Worksheet.


  183. Hi Guilherme, you can use conditional formatting to leave the values ​​blank if a given cell is not filled.

  184. Good afternoon, friends, I have a small problem, I created a spreadsheet with the values ​​of some products that I want to sell, and I would like to make another spreadsheet with a summary of my survey, and I would like it to appear as a budget and type, listed the items that I inserted a value in that worksheet that already exists, if I did not put value I did not want the card to appear in the budget. Is there any way to do this? I am attaching an image also to facilitate understanding. I am very grateful if you can help me. Hugs.

  185. Good evening I have a spreadsheet with number of processes, and I would like to automate the search of the results of these processes, with a click return in the cell next to the results of all the processes listed in the spreadsheet. How can I do this

  186. Hi Weidson, I do not know a way to auto fill no, but I believe you can at least categorize alphabetically using filters, so it's easier to find the desired name within the list

  187. Auto-fill is a native feature of Excel, and it's just too shabby to be honest with. I do not know simple ways to create this type of operation, at most you can use lists in data validations to facilitate the search of the desired item or the lightning fill to follow patterns, but not to fill a particular cell itself

  188. Hello guys. I have a spreadsheet with a list of clients, and I would like to know if I can create a self-filling cell through this list.
    Like, I start typing a name, and Excel is already showing me a relationship on top of what I'm typing.

    Why do I use data validation, but rolling the scroll bar takes a long time, because, as I am registering, the names are not alphabetized.
    Give me a LIGHT ....

  189. Hi folks, I needed my plan1 column A2 (excel) to try to auto-fill up when I start typing the product name according to the database that is in the 2 plan. How do you do that?

  190. Sorted out

    I do not know what I was missing, but I went through it step by step and it worked.

    The next enhancement is to divide a total value into "x" plots, showing the date of each ...
    and preferably by keeping the charge command for each.

  191. Good morning, I need to make a spreadsheet where it calculates future payment dates and returns me information from:

    - "In day" If the due date is greater than today;
    - "Winning" If the due date is the same as today;
    - "Late" If the due date is lower than today.

    However, there must be one more condition, which is whether or not the customer is paying, since, in cases where the payment date is lower than today, but the customer has already paid the title, we should consider "On-time" also.

    The first three conditions I've already been able to reproduce using the following formula that appears in the image.

    I just can not add the "Paid" condition? SE (= "YES"; "In-day") SE (= No; ...) in this case return the conditions previously mentioned.

    Follow the image for better understanding, just note that the "Paid?" Condition is displayed as a drop-down list.

    I will be very grateful if you can help.

  192. Good morning, I need to make a spreadsheet where it calculates future payment dates and returns me information from:

    - "In day" If the due date is greater than today;
    - "Winning" If the due date is the same as today;
    - "Late" If the due date is lower than today.

    However, there must be one more condition, which is whether or not the customer is paying, since, in cases where the payment date is lower than today, but the customer has already paid the title, we should consider "On-time" also.

    The first three conditions I've already been able to reproduce using the following formula:

    SE (K2 = L7; "Winning"; SE (L7 <K2; "Late"; "Up-to-date"))

    I just can not add the "Paid" condition? SE (= "YES"; "In-day") SE (= No; ...) in this case return the conditions previously mentioned.

    Follow the image for better understanding, it is worth pointing out that the condition "Paid?" is displayed as a drop-down list.

    I will be very grateful if you can help.

  193. Good morning, I need to make a spreadsheet where it calculates future payment dates and returns me information from:

    - "In day" If the due date is greater than today;
    - "Winning" If the due date is the same as today;
    - "Late" If the due date is lower than today.

    However, there must be one more condition, which is whether or not the customer is paying, since, in cases where the payment date is lower than today, but the customer has already paid the title, we should consider "On-time" also.

    The first three conditions I've already been able to reproduce using the following formula:

    (= SE (K2 = L7; "Winning"; SE (L7 <K2; "Delayed";

    I just can not add the "Paid" condition? SE (= "YES"; "In-day") SE (= No; ...) in this case return the conditions previously mentioned.

    Follow the image for better understanding, it is worth pointing out that the condition "Paid?" is displayed as a drop-down list.

    I will be very grateful if you can help.

  194. Good morning, I need to make a spreadsheet where it calculates future payment dates and returns me information from:

    - "In day" If the due date is greater than today;
    - "Winning" If the due date is the same as today;
    - "Late" If the due date is lower than today.

    However, there must be one more condition, which is whether or not the customer is paying, since, in cases where the payment date is lower than today, but the customer has already paid the title, we should consider "On-time" also.

    The first three conditions I've already been able to reproduce using the following formula:

    = SE (K2 = L7; "Winning"; SE (L7 <K2; "Delayed"; "Up-to-date"))

    I just can not add the "Paid" condition? SE (= "YES"; "In-day") SE (= No; ...) in this case return the conditions previously mentioned.

    Follow the image for better understanding, it is worth pointing out that the condition "Paid?" is displayed as a drop-down list.

    I will be very grateful if you can help.

  195. Hi Rafael, I do not know if I understood very well what you want, but I suggest you put your question (with more information) in our Forum - forum.luz.vc

    That way, there may be more people helping you okay?

  196. I need to make a dynamic chart with percentage of variation between months. Does anyone know if it is possible?

  197. Guys. Anyone know if it is possible to make a dynamic graph with percentage of variation between months?

  198. Hi Welissom, is everything good?

    what are you thinking? In placing a link indicating to the posts of our blog? Can you explain it better?

  199. Hi Gabriel,

    in general I think you just need to create a column with STATUS and create a conditional SE to give you answers about the deadline of the notes according to the comparison of the expiration date and today's date (using the TODAY function).

    If the difference is less than zero it means that it has already won, if it is up to 7 days is to win and if it is longer than that, it has a long deadline (the days I've spoken here are examples, you can modify it for what you do most sense for your reality)

  200. Good afternoon Rafael, I wrote the post and it disappeared, hehe but we go the. I am organizing the company accounts and I have a table with cash flow and another with the description of the entry notes (purchases). In the shopping spreadsheet has the date entered, due date, note number, product description, value of each product and total value. I would like you to inform the expiration date, pull the value, name, note number, to the cash flow worksheet. Could you inform me? Thank you very much in advance

  201. Good afternoon Rafael, I wrote the post and it disappeared, hehe but we go the. I am organizing the company accounts and I have a table with cash flow and another with the description of the entry notes (purchases). In the shopping spreadsheet, it has the date entry, due date, note number, product description, value of each product and total value. I would like you to inform the expiration date, pull the value, name, note number, to the cash flow worksheet. Could you inform me? Thank you very much in advance

  202. Good morning Rafael, I am organized the accounts of the company and I have in a tab the cash flow of the company with payment date, company name, note number and value and in the other tab I have a worksheet where I describe the notes (date of entry , due date, value of each product, product description, total value of the note). Would you know how I can describe the notes pulling the winning values ​​(along with name, number, date, value) to the cash flow?

  203. Hi Alexander, how are you? I do not know if I understand very well, but for the value to accumulate, you would need to create a sum and have a list / table where you could put all new values ​​in sequence, so the sum would pick up all the added items and you would not delete the old value

  204. Good morning ... I need some help ...

    I'm creating a spreadsheet to control KM. and formatted this way: in the D3 cell, there is a formula that takes the largest KM of cell H3 ......... = MAXIMUM (IF ($ M $ 8: $ M $ 5007 = F8; $ N $ 8: $ N $ 5007) ) ...... .. (knowing that the plates can be replayed several times ... 300 plates are registered in the cell B ...... FUNCTIONALITY ...... when I type in the cells G (plate) and in the H (KM) and remembering that the plates can Repeat, the larger KM goes to cell D ... .what I need in the cell D the value is accumulated and does not erase every time I clear the cells G and H ...... could you help me? ... thank you very much

  205. Hello Rafel, I need a function to fetch the maximum values ​​for example:

    If the result is <R $ 99,99 the value to be displayed in the cell is $ 4,00.

  206. Hi Anthony, you have to do yes, but it all depends on how the worksheet is structured. If your database is a table with only inputs, you will probably have to create a macro that every time you fill out the form and execute it, it flips back in the data tab and changes the old values ​​by the new ones understood?

  207. Hi Rafael, how are you?

    I have a payroll control sheet for insurance policies.
    Where in the first moment I register her in a database with all the information.
    now I have a second worksheet, a form, where I type the policy number and it shows me everything I want to know.
    my problem happens now that I have a lot of data in the database, and I wanted to enter the policy number on the form, and when a customer pays the second or third installment, I unsubscribe from the form sheet and simultaneously fill out the database and vice versa?

    Can you do that? Or am I traveling asking too much?

    Thanks in advance for your attention and collaboration, I loved the site. Thank you!

  208. Hi Diego, I thought of you adding an auxiliary column (assuming the numbers are in column A and you put that column in B) with the following function:

    = SE (A2 = A1 + 1; "Correct"; "Sequence failure")

    This formula would be inserted in the second line and it was only dragged to the end, so if the sequence had an error, the fault message would appear. For your spreadsheet to become even more visual you could put a conditional formatting on that column to highlight the red-failing values.

  209. Good afternoon! I need to configure excel to identify missing numbering. Let's say I have 400 notes to check the numbering of the starting note is 250 and the end is 650 they are numbered in the worksheet so 251, 252, 253 etc, assuming there is a missing note as in the example, 452, 453, 455, 456, note that the 454 note was missing, how do I automatically unmask the absence of the sequence?

  210. Yes, you can do this with an SE formula, of type = SE (A1> date entered, "" normal result) or conditional formatting, "hiding" the values. To return the value in a query, you need to use search functions like PROCV

    If you can ask your questions on our forum is better, we have other participants who can help too (forum.luz.vc)

  211. Interesting, but I know you have to put the formula to not show the cell "x" if the date is greater than the one entered? and how to return that result to the form so that it is viewed in a simple query? the challenge is launched .. because I'm breaking my head ... give me the email I send you what I have so far ...

  212. Well, thinking of simple solutions, let the fill be just who you offer and, if there is an agreement, it would have a person responsible for taking the offer already resolved

  213. Raqfael .. thanks for replying colleague .... The initial idea is that this fill was via form (tb from Google) but how to show back the query only from who offers ....

  214. Hello good afternoon.

    First of all, great work you do here. Super important tips. Congratulations.

    I have a doubt .. is it possible to create a smart worksheet where after, for example, make a series of releases, generate a window with the summary of everything?

    Type, I throw several data from an NF in a line .. like date, cnpj, values, cfop, cst ... and at the end of last release excel open a window to make a quick conference.

    If so, would it be through macro?

    Thank you very much in advance.

  215. You do not have to send the spreadsheet, not Jorge, because I have no time to analyze further.

    In this case you will need to use a PROCV to find the corresponding result in the plan to do the calculation

  216. Hi Rafael, I have a plan with the data being a table with values ​​...

    of the Walls
    conditions Very Good Good Fair Poor

    Stone masonry 0,013 0,014 0,015 0,017

    And so has 31 items .... as I have tried to explain ... this data is entered in the first plan q has the data name .... and then it has to give me the result of the numbers in another plan to do the calculation ....

    If you want, I can send you the spreadsheet by e-mail so that I can better understand my difficulty.

  217. Hi Jorge,

    I believe you need to create an extra column with the value, you can use an SE function inside SE of type = SE (A1 = "Very Good"; nota5; SE (A1 = "Good"; Nota4, and so on

    That way, you will have the value of each of the notes. Then if you want, you can create an auxiliary table with the total 5, 4, etc. notes using a CONT.SE or SOMASE depending on the interest

  218. Sounds like a simple job.

    Create the spreadsheet in Google with a tab for each month and a table structure that has the person's name, date and time "offered." So people can go by placing their availability and, whoever has an interest, already puts the name on their worksheet and the exchange is scheduled.

    If you have difficulty or have thought otherwise, just tell me

  219. Good afternoon, guys ... I need a help ...... I made a spreadsheet and I'm not able to hit the formula to do the calculations ... .If anyone can help me, I'll send the worksheet ...

    I need it to calculate 2 drop-down lists and show me the result ... being one is the selection of the data, and the other is the classification (Very Good, GOOD, REGULAR and BAD) and in it are the numbers that I need ... how do I mount this formula.

    But type ... in one plan I enter the data, the other will give me the result and in the other is the data from the drop down ... ..

  220. Hi everyone, I need to make a worksheet of preference type forms of google that returns with the values ​​as follows: It is a scale of work where workers query possible change of schedules .. ex:
    "John" needs to change 01 / 02 / 2016 with 13 time: 00 to 05 / 02 / 2016 as 18: 00 (schedule available by "maria" that has an appointment)
    .. It happens that I realize people who want the schedule of the outras but as are many people do not get savebdno, the intention is to form a database to view the dates offered. .. give me a light to and ....

  221. My dear blog light, good morning. Please give me a light! rs ... I have the need to monitor dates constantly and I do it through a simple spreadsheet with conditional formatting. However, often being focused on other issues I let pass the dates or get too close to maturity. I wonder if there is a way to create reminders, such as Outlook, that appear on the desktop. I tried to create through VBA, but I did not get significant results. Thank you very much in advance. A hug.

  222. I want to learn how to make smart spreadsheets. someone help me please

  223. Hi Alê, you can use the conditionals SE and OU with the function MAIOR and SUM, would be something like this (assuming the notes are in column A and are respectively A1 to A7


    I think that will work perfectly

  224. Good Morning!
    Would you like a help in an excel formula?

    I have 7 notes in a bulletin format and need a summation of 6 only, follow example:

    Note 1 - 9
    Note 2 - 7,5
    Av.1 - 4
    Av. 2 - 3
    Av.3 - 4
    2ª ch. - 10
    AQ1 - 4,5
    Total: 34,5

    I need to enter the 1 note and 2 note, if the 2th call is greater than one of them in the sum, discard the minor and sum with the 2th call, as it appears in the total.

    If you help me, I thank you immensely.


  225. Good afternoon, I had a problem in my questionnaire, in the end I need to tabulate the type values: if from 9 to 15 points, it has to show "definitely morning" result if it gives from 16 to 20 the result is "moderately morning" are 5 answers possible tried to do with the formula if, but it did not, can you tell me the error?

    =SE(B49<=15;"Definitivamente matutino";SE(16<B49<=20;"Moderadamente matutino";SE(21<B49<=26;"Intermediário";SE(27<B49<=31;"Moderadamente vespertino";SE(32<B49<=38;"Definitivamente vespertino")))))

  226. Rafael, thank you very much, I was able to make the spreadsheet with your tips. I'll see the course you spoke about ????????????????

  227. Thank you so much for responding so quickly, Rafael, I will try like the one you gave me and the story went well. I think I'll read your entire blog and see if I learn to mess with this excel at once

  228. Hi Isa, how are you?

    is the conditional function SE. See a little more about her here in this post - https://blog.luz.vc/excel/funcao-se-excel/

    If you continue with doubts, just tell me, but your function would look more or less like this.


    If you have many arguments it may be best to use an auxiliary table with values ​​and scores and use a PROCV or PROCH, hugs

  229. Good morning, what is the formula used to punctuate answers of an issue? Answer: A to assign 1 point, if B assign 2, if C assign 3 ....

  230. Hi Jader, I believe you can do this using the SE function with OU with PROCV. It would look something like this:

    = SE (OR (PROCV1; PROCV2); PROCV; ")

  231. Good afternoon! I need a formula that looks for a value typed in two columns, if this value is in one of the two, return with the rest of the data. Eg: I type a code and it searches it in two columns, internal cod and cod provider, if it contains in one of them it brings the rest of the information, like description and and price. I tried PROCV, but it only searches in a column, always the first one. Is there a formula? Thanks for listening.

Comments are closed.