formulla

Occasional Contributor

Hello, I am Ismail from Iran. My job is accounting and I use the Excel 2016 program and we are in the business of buying agricultural products. We buy the agricultural products of at least 500 gardeners every year. Therefore, in the Excel program, each sheet is related to a person and the input product and the money that We will pay him. Because of this, I want the names of all the gardeners to be written on the first sheet and each of them has a code. It is possible to change it easier and more general from sheet 1 to 01 and sheet 2 to 02 and to sheet 500 to the number 500 and so that By clicking on the number 500 in the first sheet, it will go to the same gardener's page, and the second question shows the money paid for each gardener at the bottom of the same page, and the sum of that page in the first sheet in front of the same code.

8 Replies
اHELLO CAN ENYONE HELP?????????

@ARIA_FRUIT 

Please don't be so impatient! All participants here are users like you who voluntarily spend some of their free time to help others. That means you cannot demand an immediate reply.

See the small sample workbook that I have attached. It has a summary sheet and three sheets named 1, 2 and 3.

The summary sheet has formulas using the INDIRECT and HYPERLINK functions. The hyperlinks are in column B.

SORRY .But I don't know how to do this for 500 sheets. Because I like to work with Excel, but I'm a beginner in this field and it's a bit difficult for me to speak and translate English.

@ARIA_FRUIT 

Select A2:A3.

Then drag the fill handle down to A501. You will now have the numbers 1 to 500 without having to type them yourself.

Next, select B2:C2.

Then drag the fill handle down to row 501. This will copy the formulas in columns B and C.

I'm sorry, but unfortunately I couldn't implement your order. There is a possibility of more guidance or doing this work for you. If you do a small favor, do it or explain more, I will be grateful, my friend. Do something friendly, my brother, I will compensate you.

@ARIA_FRUIT 

 

Hello, Ismail from Iran. First, I want to emphasize what my friend @Hans Vogelaar has said: all of us here are volunteers who happen to enjoy working with Excel as well as enjoying helping people like you learn how to use it.

 

In that connection, it seems to me that you are tackling a VERY BIG project here as a beginner with Excel. There are many things still to be learned...do you have access to any books about Excel and its applications in business? That could be very helpful for you in a task such as this. As just one example, the way you describe your current approach raises questions in my mind. It might, for example, make sense NOT to have 500 sheets, one for each of the gardeners from whom you buy product each year, and that is especially true if you only repeatedly buy one or two products (very few) from any one gardener. You might well be better served with a single data table that included all of them and your ordering history on one sheet.

 

It's hard to be certain about that, but it's a common design error on the part of beginners to design a spreadsheet as if they were working with paper ledger sheets. On paper, it's clearer to the human mind, more manageable, to have separate sheets for each client. But doing that with a spreadsheet design interferes with the computer's ability to manage big databases.

 

For example, if you have access to Amazon (or some other book seller) you might benefit from this book.

mathetes_0-1659368371175.png

 

Is it possible for you to post images of what your sheets look like?

Hello dear father. You mentioned a very interesting point that was very interesting to me. In 20 to 30 days, dates will be harvested in the south of Iran, which we often export to Pakistan with trailers. During the season, we have about 150 gardeners who produce Zahedi, Shahani, or Khasovi dates (a very high-quality and delicious type of date at a base price of 1 US dollar) and... ..they sell themselves to us as a whole, that every gardener needs a sheet to deliver the invoice, and partial purchases are collected in one sheet. Export and other items also need about 50 sheets, which becomes 200 sheets, and after the date product, we directly enter the citrus market, which includes oranges, tangerines, sweet lemons, stone lemons, and other products. Do I need this number of numbers? or not
Hello again, one thing to remind you. I live in Iran and living in Iran, like driving the first series of Chevrolet, requires special skills. Because I have to bear the losses of a lawsuit that has no end. For my purchase, access to I don't have international banks, and there are three ways. First, if I put a restriction on it, it means that I will give up due to the inability to pay due to the embargo, which is the worst possible situation because I need it very much. Second, it will be sent to my address for free. sent that this is a high expectation and if it is sent, it will reach you. And thirdly, if it is other than these two things, if you offer it, I will gladly accept it.