Excell Help

[MatthewR]

I'm currently working on a project and part of it is creating a new scoping tool to assist myself and in the future colleagues.

We have a set of data (base rates) which are all on their own sheet, we then have a 'front page' which we have set to pull data from the set rates when numbered.

What I am trying to achieve is that we can input on the front sheet the rooms e.g

Preliminary items
Living room
Bedroom 1
Bathroom

And when these have been input it auto populates new sheets copying the set rates across so that the front page (scope) will then display all the items set across the relevant rooms.

Is this possible? If so how?

 

[Oggy997]

You mean you want to lookup the values from 'base rates' sheet, and return them to 'scope'

To do that you need a value to lookup against, call it "Building" (and the building contains the rooms) - Consider using XLOOKUP, depending on your Excel version.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

=XLOOKUP(G3,Rates!B3:B7,Rates!C3:F7)

Scope

Rates

Alternatively, and more legacy, you can use VLOOKUP for the same

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])

=VLOOKUP(G3,Rates!B3:F7,2,FALSE)

If they're tabulated differently, change to a HLOOKUP (works the same, just the axis are reversed)

Or, to work with no indexed data, using INDEX/MATCH as a combo

=INDEX(Rates!C3:C7,MATCH(G3,Rates!B3:B7,0))

 

[Oggy997]

If you don't mean that, then upload a sample workbook of what you want.
I'm at the hospital from lunch, but can look before then.

 

[MatthewR]

If you don't mean that, then upload a sample workbook of what you want.
I'm at the hospital from lunch, but can look before then.

I'll go through what you've suggested so far and see if that's what we are after, I'm waiting for the book to be issued but will fire you a pm as soon as we are in the correct position for it to discuss further if that's ok?

 

[Oggy997]

Yeah, no probs.
Always happy to help with this thing, gets my brain back active ready for returning to work.

After reading your post back, I dont think it is what you want.