Aug 01 2022 06:15 AM
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.
Aug 01 2022 07:06 AM
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.
Aug 01 2022 07:15 AM
Aug 01 2022 07:25 AM
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.
Aug 01 2022 08:12 AM
Aug 01 2022 08:42 AM
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.
Is it possible for you to post images of what your sheets look like?
Aug 02 2022 12:07 AM
Aug 02 2022 01:10 AM