Dynamic Lookup

fishewb

New Member
Joined
Jun 3, 2019
Messages
19
ABC
15/1/2019
2OK510
3TX690
4CA300
5NY100

<tbody>
</tbody>

I'm looking for help designing a formula that can lookup data and can be nimble enough to return new data if I change either the date in C1 or the display name in B2:B5.

The display name in column B is a shortened report only display name and is controlled by a separate table used as a basis table that links to all similar reports tables like the one above (one change in the bases tab table and all related cells in display tables will change).

The formula I had built was a SUMProduct that used the model name and looked up in that model. The formula words for editing C1 and finding a new date, but changing a name in B2:B5 won't work as the formula cant use those as a lookup as they aren't the correct value fields in them model, display names only. I'm looking for the ability to type in a new display name in column B or date in C1 or both, and somehow have formulas in C2:C5 update correctly

Thanks for any thoughts on this. Let me know what else I can clarify if this doesn't make sense.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Yes, it works perfectly.

Well one more variable I want to ask about.

Would this theoretically work if set up correctly if there is non contiguous data in a couple ranges? I wasn't able to replicate that, it was one reason why I was using SUMProduct as it could look across noncontinuous data.
 
Upvote 0
Well one more variable I want to ask about.

Would this theoretically work if set up correctly if there is non contiguous data in a couple ranges? I wasn't able to replicate that, it was one reason why I was using SUMProduct as it could look across noncontinuous data.

End Result (ER)-Data Model (DM)Basis Lookup Template (BLT)
ABABC…ABC…
15/1/201915/1/20196/1/201915/1/20196/1/2019
2OK5102Oklahoma ltd.5105002Oklahoma ltd.OK
3TX69033
4CA3004California Dust..3002004California Dust..CA
5NY1005NY - (ltd)100505NY - (ltd)

<tbody>
</tbody>

I removed data above to replicate the structure of what I'm trying to describe.
 
Upvote 0
End Result (ER)-Data Model (DM)Basis Lookup Template (BLT)
ABABC…ABC…
15/1/201915/1/20196/1/201915/1/20196/1/2019
2OK5102Oklahoma ltd.5105002Oklahoma ltd.OK
3TX69033
4CA3004California Dust..3002004California Dust..CA
5NY1005NY - (ltd)100505NY - (ltd)

<tbody>
</tbody>

I removed data above to replicate the structure of what I'm trying to describe.

Nevermind...I was not building the formula correctly. It works perfectly, amazing really! Thank you again so much.

I was not defining my starting point for the offset correctly, when I did that correctly boom! Everything works perfectly.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top