Excel spreadsheet question

scott Taylor 0

I have multiple different sheets with computer dates on them. I’m trying to come up with a replacement pattern for these computers. I want to replace the oldest computers first, but I also want to spread out the replacement of the computers over different departments. I have these departments organized in different spreadsheets? Is there a way for me to have excel automatically poll, a list of computers from those differing a spreadsheet’s without me, having to go and manually pick, and organize all of these computers into a single table.

Emi Zhang-MSFT 23,906 Reputation points Microsoft Vendor

2023-07-18T01:56:52.9966667+00:00

Hi,

Could you provide a sample about the tables and requirement here? Please be a bit more precise to explain your requirement or you can upload a screenshot so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.
Barry Schwarz 2,511 Reputation points

2023-07-18T05:57:35.7166667+00:00

Placing the data in a single table, sorted by age or by age within departments or by department within age, is easy. The problem is deciding which computers to replace.

If department A has many computers and the three oldest are 8, 9, and 10 years old AND department B has some and the two oldest are 5 and 8 AND department C has few and the oldest is 7 THEN how do you decide which ones to replace?
scott Taylor 0 Reputation points

2023-07-18T13:30:32.7833333+00:00

What I am hoping to achieve is making a plan to replace all the department computers in a 5 year period. With each year being split up quarterly. I would like to start with the oldest computers first, but I want to make sure I am evening out the replacements over every department. Example…

department A has 5 computers the oldest being 7 years, department B has 3 computers the oldest being 9 and department C has 10 computers the oldest being 12. My goal is to try and evenly pick for the first year of the first quarter which computer to replace, of course I want to start with the oldest computer, but I want to make sure I am not putting to much stress on a department by replace all of their computers at once so I need to first replace the 12yearold computer in department C, but my goal is to have excel come up with a line that is randomly picking a computer, starting with the oldest computer first, then the second oldest from a different spreadsheet and so on. These departments are all in different spreadsheets, and I want to pull the information into a single spreadsheet, where I can then list it out to see the order I am replacing computers.
Barry Schwarz 2,511 Reputation points

2023-07-19T11:30:11.1466667+00:00

Concentrate on one task at a time.

Step one seems to be collecting the data. If all the spreadsheets are in the same workbook (Excel file), you can use the VBA worksheets collection to cycle through them. If you have multiple workbooks containing the worksheets, the VBA workbooks collection provides a similar capability. Within a workbook, you need some method of determining the range of cells containing the data you need. You can copy that data to a new workbook/worksheet and append additional data as you process each set.

Once you have the data collected, you can sort as often and in as many different ways as you need.

You don't want to overburden any department and you want the replacement project to last five years with activity each quarter. That is 20 activities so you can replace the oldest 5% in each department each quarter. Conditional formatting could identify the candidates for you.

Share via

Excel spreadsheet question