Cycle through different Rate Sheets

[Falko26]

Hey Team,

I'm trying to reference multiple different rate sheets for my estimating sheets. To start simple, all I want to do is have a table on my estimating spreadsheet that shows either the info from Administration 01 on rate sheet 1 or Engineering 01 on rate sheet 2 based on maybe the info from a drop down? Not sure if this would require some sort of power query or what.

Thanks!

Rate Sheet 1:

Rate Sheet 2:

 

[RobP]

Hi, possibly a better way would be to have a single "Rate Sheet", organised properly as data.
Then you could use a formula (FILTER) linked to all your rates, organised as below, which is hinged on a drop down box (Data Validation, List) pointed at just the first column of headings as such.

Then, each time you change the value in the drop down box, it produces the list of data below it.

No reason for you not to be able to duplicate it and point it to rate sheet 2 in another box somewhere if you want .. its just usually cleaner to put all your "data" ie. Rates in one place so that it can be referenced elsewhere.

Book1
	A	B	C
14	Administrative Services	Level I	48
15	Administrative Services	Level II	58
16	Administrative Services	Level III	68
17	Administrative Services	Level IV	78
18	Administrative Services	Level V	90
19	Drafter / Designer / Specialist	Level I	73
20	Drafter / Designer / Specialist	Level II	78
21	Drafter / Designer / Specialist	Level III	88
22	Drafter / Designer / Specialist	Level IV	98
23	Drafter / Designer / Specialist	Level V	108
24	Engineer	Level I	92
25	Engineer	Level II	120
26	Engineer	Level III	150
27	Engineer	Level IV	185
28	Engineer	Level V	250
29	Construction Management	Level I	93
30	Construction Management	Level II	103
31	Construction Management	Level III	113
32	Construction Management	Level IV	132
33	Construction Management	Level V	150
Sheet1

Book1
	A	B	C	D
1
2
3
4
5
6
7	Drafter / Designer / Specialist
8
9	Drafter / Designer / Specialist	Level I	73
10	Drafter / Designer / Specialist	Level II	78
11	Drafter / Designer / Specialist	Level III	88
12	Drafter / Designer / Specialist	Level IV	98
13	Drafter / Designer / Specialist	Level V	108
14
Sheet2

Cell Formulas
Range	Formula
A9:C13	A9	=FILTER(Sheet1!A14:C33,Sheet1!A14:A33 = A7)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A10	Any value
A7	List	=Sheet1!$A$14:$A$33

 

[Falko26]

Hey Thanks Rob!

This method works for what I was trying to do with referencing different rate sheets.

Now I need a way to create and update the information from the original sheets. Or is something like that going to be a manual process you think? Absolute best case scenario is all of the original excel rate sheets I have, convert to the singular table you depicted above in a combined power query. That way, if any changes are made we can hit update in the bid sheet and have the new rates or added positions.

Thanks!

 

[RobP]

Well, I'm glad I understood your first question, and provided a response for you in the right way. Thanks for the feedback.

In terms of your second entirely different question about updating data, which I'm not sure relates to the first necessarily, updating raw Data is a manual process generally speaking. Unless you have a way to pull it in automatically via a Macro, or DBase formulas from an external source (just as an example).

I don't know how many Rate Sheets you have, or who indeed generates them for you. Perhaps you do it yourself. If you get them from somewhere else, my approach would be to go ask them (or IT department) to lump them all together on one sheet in the fashion I suggested - that way, when it gets issued (as presumably someone is typing in rate values for each category on some other system), all your rates are updated to the latest version. Easy to copy and paste a sheet into your workbook over the old raw data.

If you are generating them yourself manually, then, yes, its a manual job to put them all into the format I suggested up front (ONCE). From then on, you can use a simple (CTrl-Shft-L) FILTER on top of the header in (row 1 lets say) to allow you to simply filter each heading you want to see whenever you need to do a rate change on the RAW data.

The thing about formulas acting on your RAW data, is that they will automatically update to give you the latest rate if thats what is in your RAW data. So Question 1 would still be a valid solution. PowerQuery is also another solution that "acts" on RAW data to get you the latest result, but I don't see it as necessary here.

Hope that helps - as I say - thats just my idea. Others here could have different solutions to your challenges.

Cheers
Rob

 

[HighAndWilder]

Hey Thanks Rob!

This method works for what I was trying to do with referencing different rate sheets.

Now I need a way to create and update the information from the original sheets. Or is something like that going to be a manual process you think? Absolute best case scenario is all of the original excel rate sheets I have, convert to the singular table you depicted above in a combined power query. That way, if any changes are made we can hit update in the bid sheet and have the new rates or added positions.

Thanks!

It should be quite easy to convert the data.

This just takes the data from only one sheet but it can easily be adapted to loop through many sheets as long as the sheet names have a naming convention or are in a list.

The converted data range can then be made into a table so that the filter formula is dynamic.

See this example.

Public Sub subConvertData()
Dim rng As Range
Dim rngSheet As Range
Dim lngRow As Long
Dim WsDestination As Worksheet
Dim WsSource As Worksheet
Dim strHeading As String

  ActiveWorkbook.Save
  
  Set WsSource = Worksheets("Source")
    
  Set WsDestination = Worksheets("Destination")
  
  WsDestination.Cells.ClearContents
  
  lngRow = 2
  
  Set rngSheet = WsSource.UsedRange.SpecialCells(xlCellTypeConstants)
  
  For Each rng In rngSheet.Areas
    If rng.Columns.Count > 1 Then
      With WsDestination
        With .Cells(lngRow, 2).Resize(rng.Rows.Count, rng.Columns.Count)
          .Value = rng.Value
          .Columns(1).Offset(0, -1).Value = strHeading
        End With
        lngRow = WsDestination.Cells(.Rows.Count, "A").End(xlUp).Row + 1
      End With
    Else
      strHeading = rng.Value
    End If
  Next rng

End Sub

Cycle through different Rate Sheets.xlsm
	A	B
1	Administrative Services
2	Level I	48
3	Level II	58
4	Level III	68
5	Level IV	78
6	Level V	90
7
8	Drafter / Designer / Specialist
9	Level I	73
10	Level II	78
11	Level III	88
12	Level IV	98
13	Level V	108
14
15	Engineer
16	Level I	92
17	Level II	120
18	Level III	150
19	Level IV	185
20	Level V	250
21
22	Construction Management
23	Level I	93
24	Level II	103
25	Level III	113
26	Level IV	132
27	Level V	150
Source

Cycle through different Rate Sheets.xlsm
	A	B	C	D
1
2	Administrative Services	Level I	48
3	Administrative Services	Level II	58
4	Administrative Services	Level III	68
5	Administrative Services	Level IV	78
6	Administrative Services	Level V	90
7	Drafter / Designer / Specialist	Level I	73
8	Drafter / Designer / Specialist	Level II	78
9	Drafter / Designer / Specialist	Level III	88
10	Drafter / Designer / Specialist	Level IV	98
11	Drafter / Designer / Specialist	Level V	108
12	Engineer	Level I	92
13	Engineer	Level II	120
14	Engineer	Level III	150
15	Engineer	Level IV	185
16	Engineer	Level V	250
17	Construction Management	Level I	93
18	Construction Management	Level II	103
19	Construction Management	Level III	113
20	Construction Management	Level IV	132
21	Construction Management	Level V	150
22
Destination