Returning Values from Multiple Worksheet Tab Bars

Hi All

Simple one, I would like a formula to return the Fork description, Truck Make, Truck Model & 1 of 1 1 of 2 from multiple worksheet tab bars to the master.

Thanks
Aide

This one is not nescecarrily so simple. I usually make a new sheet where I link all the other sheets, so that I can Vlookup in the new sheet that contains all data. It requires that all sheets are set up in the same way, and it tends to make the excel book slower.

You can do this by putting Dosan!A1 in the first cell in the new sheet, then copy as far as you need to copy. Then you write =Hyster-Yale!A1 in the next free cell and do the same etc.
To make sure you only get the amount of rows you need, you can use the indirect formula combined with counting rows in each sheet. I'll not go into explaining that now.

I'll follow this thread in the hope of learning a better way to do this.

Good luck

Hi Tbez

Thanks for the reply, would this work it l set up all the worksheets the same on the same workbook??

Thanks
Aide

Hi All

Just wondering if someone as another solution to this, as I can't get the formula which Tbez has offered to work.

I've attached the workbook to assist.

Thanks
Aide

Hi Aide,

I've been away from my computer this week-end, so I couldn't reply before now. I've used your workbook to show what I mean. (See attachement)

I have made two extra Sheets.
"All sheets together"
"Support table"

In the Support table, I inserted the sheet name and the column number for the Fork Description, Truck Make, Truck Model and 1of1 1of2 for each Truck make. I also had to add information about the formatting of the fork number, because the Kalmar Sheet has different formatting than the other two.

All sheets together does what I described in my first post. I added the fork number [formatted as a number] and the truck make from first 24 rows of each sheet as well as a number or text to use in the vlookup, depending on the type of formatting you use. This sheet can have a lot more cells from each sheet, but if it gets too big, it will slow down your workbook.
* This sheet is a potential for error, because you have to make sure you dont have more parts than you add rows in this sheet. In this case, I limited the sheet to 24 parts per sheet. Normally it is enough to make say 50 % too many rows than you need. So if you have 5000 parts per sheet, make 7500 parts possible. If you want to reduce the chance of error, and optimize the workbooks performance, you probably want to make this part of the sheet dynamic (or hope for someone to offer a better solution than mine - as I do)

In the Master Sheet I use the following formulas:
1. A Vlookup first on the Truck make. Search for the fork part number you insert in B2 in the "All sheets Together" to return the Truck Make.
2. A Vlookup in the fork description, using the fork part to search in the All sheets together for the formatting of the fork part, then using the Truck Make to search in the support table for the range and column number, so that The Vlookup searches for the fork part in the correct format in the correct sheet and the correct column.
3. The same in the Truck Model and The 1 of 1 1 of 2 cells.

I hope this helps you go on with your project Aide, your Excel Sheet is starting to look very nice!
Best regards Tbez

Hi,
I made some changes in your file:
- I put the columns (Fork Description, Truck Make, Truck Model and 1 of 1 1 of 2) in the same order in all tables
- I defined name "RngData" with formula:

Formula:

Please Login or Register  to view this content.

With these changes you can use a VLOOKUP formula

Formula:

Please Login or Register  to view this content.

in B4,

Formula:

Please Login or Register  to view this content.

in B7 and so on

See attached

Hi Both,

Thanks for your formulas they both have worked, however for some reason the original formula now does not work.

Sorry to be pain, please can someone have a quick look to see why.

Thanks
Aide

Hi,

You're welcome.
The original formula should still work, but in the example you posted, you removed some of the data, so there is never a fork number that has both cost and part info. Try inserting fork number 7809124, and it returns component cost, because it is in the component cost sheet.

Tbez

Thanks Tbez for your input again.

I'm going to copy and paste this into my master workbook, am assuming at will work with the extra worksheets?

Also how did you check the formatting, as I need to check on my master workbook because I think they maybe the same as the Kalmar wooksheet.

Thanks
Aide

Hi,

In order for it to work with the extra sheets, you have to adjust the two extra sheets I added

"All sheets together" - Add enough rows of each sheet, and add all sheets
"Support table" - Add the info for the extra sheets

I actually didn't check the formatting, I just saw that the vlookup didnt work, so I tried a different format and figured it out. But a good way to check the formatting is to right click on the status bar on the bottom of your Excel. This is where you can see the sum and average of the selected cells, select "numeric amount" (i'm not sure about the english name for it), and Excel will add a number to show the amount of cells that has a numeric value. In your case, you'll se the counter for numeric values will show when you select the Kalmar fork numbers.

Best regards Tbez

Originally Posted by TudyBTH

Hi,
I made some changes in your file:
- I put the columns (Fork Description, Truck Make, Truck Model and 1 of 1 1 of 2) in the same order in all tables
- I defined name "RngData" with formula:

Formula:

Please Login or Register  to view this content.

With these changes you can use a VLOOKUP formula

Formula:

Please Login or Register  to view this content.

in B4,

Formula:

Please Login or Register  to view this content.

in B7 and so on

See attached

Hi TudyBTH

Just a quick question about the formula you've attached to help me, My master workbook has more worksheets (See first post) than the sample workbook you've put the formulas in. How do I transfers the formulas into my master workbook so it looks and returns values from of worksheets. I've tried to copy and paste, however it doesn't work.

Thanks in advance.
Aide

Hi Aide,

The formulas of "Master" sheet are the same, they can be copied.
But the defined name "RngData" must be adapted to your file. This depends on the arrangement of the data in your file.
So, I suggest you attach the original file. No important data, you can delete all sensitive data. But their arrangement is very important in order to find a solution that works in your file.

Hi TudyBTH

Many thanks for the quick response. That's a good idea, I'll try to post the master workbook with all the data and worksheets, however on the Components cost - Data Ran are is over 23k rows and I could not upload it last time. Is there another way or do I need to reduce the data size.

Thanks
Aide

Ok,
Make a copy of master workbook and in this copy delete all pages except "Master" and Truck Make sheets. In Truck Make sheets delete all rows with data, keep just header rows. Save and attach them.

Hi TudyBTH

I've attached the workbook and removed all the data in the rows, I've added a new column (Date Last Purchased) in the master worksheet as I have some additional data which once this is sorted I need the value returning.

If required you can use the data from the pervious workbooks which I attached.

Thanks for your help.

Aide

Question:
It is your own project? You can make changes?

Originally Posted by TudyBTH

Question:
It is your own project? You can make changes?

Yes & Yes

Aide

Ok,
in this case you have two choices:
Solution 1.
Keep the current arrangement of dates but
- you will have a solution difficult to adapt to any future changes.
- you will have many restriction (change the sheets names, headers of tables)
- You will use complicated formulas, hard to change
Solution 2.
You explain me exactly how you will use this file and I help you to make a full recovery of this file.

Ok Thanks

I think solution 2 would be better. The way the file is been use is as follows,

Firstly all the worksheets are live and will be added to moving forward, however after this is all set-up, I'll only be manually inputted a few rows at a time once/twice per month.

The workbook is designed so anyone can input a fork part number in the master and return values from the other worksheets.

Please free feel to change the worksheets and formulas as required. I will be adding further worksheets in the future as well.

Again Thanks
Aide

Tell me how many rows will be in all sheets (Doosan, Hyster, .../approximate) and please fill two or three rows (manual) with dates in Master sheet. You can use the file attached in first post for that.
Also it would be useful to mark with a color all the cells where the user will enter data.

Hi,
Take a look at the attached file and let me know if it is closer to what you want.
(Sorry for my English. I played football during the English classes )

Hi

I think I can work with this set-up I did want to have the truck information on different worksheets however this is the closest with have got.

Please can you explain how it works and what is the pivot table is for?

With the formulas still work if I just copy and paste the extra data in to the Fork & Components Part worksheets.

Thanks
Aide

With Excel is very easy to separate data but it is hard (sometimes impossible without VBA) to collect data from different sheets.
It is therefore appropriate to record data of the same type in the same place. If you want to see data for a single truck can use PivotTable.

Yes, the formulas still work if add or change data in the Fork & Components Part worksheets.
Pay attention to Fork Part#. Now is numeric. If you think alphanumeric codes will appear here (text) will have to change the formulas.

Hi All

Many Thanks for all of your help with this......

Aide